0What are Joins in SQL?
- Joins in SQL are used to combine rows between two or more tables.
- Joins are used in SQL when user need to extract data between two or more tables based on a related column.
- Joins returns the data between the tables based on the type of join used.
- There are different types of joins available in SQL
What are the types of joins?
- Inner Join or Simple Join
- Left Join
- Right Join
- Full Join
Let’s discuss Inner join or simple join in detail.
Inner Join
- Inner join is also called as Simple Join.
- Inner join gives matching data from both the tables based on a related column.
- Inner Join is used by mentioning INNER JOIN keyword in the query.
How to join?
- Use Keyword INNER JOIN
- Mention INNER JOIN after FROM in the query.
- Mentioning INNER keyword is not mandatory.
- Mention ON keyword before the join condition
Syntax:
SELECT column1, column2 from table1 INNER JOIN table2 ON table1.fieldname = table2.fieldname
LEFT Join
What is left join?
- Left join returns all the rows from the left table.
- Left join returns the rows even if there are no matching rows available in the right table.
- Left join returns null to the columns of the rows where there are no matches found from the right table.
- Left join returns all the values from the left table, plus matched values from the right table or NULL in case of no matching join condition.
How to use a LEFT join
- Use Keyword LEFT
- Mention LEFT JOIN after FROM in the query.
- Mentioning LEFT keyword is mandatory.
- Mention ON keyword before the join condition
Syntax:
SELECT column1, column2 From table1 LEFT JOIN table 2
ON table1.common_field = table2.common_field
RIGHT JOIN
What is Right, Join?
- Right, join returns all the rows from the right table.
- Right, join returns the rows even if there are no matching rows available in the left table.
- Right, join returns null to the columns of the rows where there are no matches found from the left table.
- Right join returns all the values from the right table, plus matched values from the left table or NULL in case of no matching join condition.
How to use a RIGHT join
- Use Keyword RIGHT
- Mention RIGHT JOIN after FROM in the query.
- Mentioning the RIGHT keyword is mandatory.
- Mention ON keyword before the join condition
Syntax:
SELECT column1, column2 From table1 RIGHT JOIN table 2
ON table1.common_field = table2.common_field
FULL JOIN
What is Full Join?
- Full join returns all the rows from the right and left the table.
- Full join returns the nulls if there is no matching in the left or right table.
- FULL JOIN can potentially return very large result-sets!
How to Join?
- Use Keyword FULL
- Mention FULL OUTER JOIN after FROM in the query.
- Mentioning FULL OUTER keyword is mandatory.
- Mention ON keyword before the join condition
Syntax:
SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2
ON table1.column_name = table2.column_name
WHERE condition;
