JOIN in SQL is a clause used to combine rows from two or more tables based on a related column between them. Its main purpose is to obtain a single dataset with complete and comprehensive information. “Complete” here refers to the data structure resulting from the combination of columns from the related tables.
To meet various data retrieval needs, the JOIN command is divided into several types: OUTER JOIN, INNER JOIN, RIGHT JOIN, and LEFT JOIN.
Differences Between SQL JOIN Types
Below is an explanation of various JOIN clauses along with example syntax to help you understand their differences:
1. FULL OUTER JOIN
Outer join (or Full Outer Join) returns all records or rows, whether they have matching values or not, from both tables being joined. If there is no matching data between the two tables, the missing side is automatically filled with NULL values in the join result.
Syntax:
SQL
SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2
ON table1.column_name = table2.column_name
WHERE condition;
2. LEFT JOIN
Left Join is an operation that returns all records from the left table (the first table) of the two joined tables. All data from the left table is retained and displayed, even if the values in the left table do not match any data in the right table (the second table). Data in the right table that has no match is replaced with NULL.
Syntax:
SQL
SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name = table2.column_name;
3. RIGHT JOIN (Complement)
As the opposite of Left Join, the Right Join clause returns all records from the right table (the second table), along with matching data from the left table. If there is no matching data in the left table, the result from the left side will be NULL.
Syntax:
SQL
SELECT column_name(s)
FROM table1
RIGHT JOIN table2
ON table1.column_name = table2.column_name;
4. INNER JOIN (Complement)
Inner Join is the most frequently used clause. Unlike Outer Join, Inner Join works more strictly by returning only rows that have matching data (the intersection) in both joined tables.
Syntax:
SQL
SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;
