In SQL, the OUTER JOIN syntax allows you to retrieve unmatched rows from two tables by including rows from one table that do not have corresponding rows in the other table(s). The main types of OUTER JOINs are LEFT JOIN, RIGHT JOIN, and FULL JOIN.
Here's how each of them works to retrieve unmatched rows:
1. LEFT JOIN
A LEFT JOIN returns all rows from the left table and the matched rows from the right table. If there is no match, NULLs are returned for columns from the right table.
Syntax:
SELECT a.*, b.*
FROM table_a a
LEFT JOIN table_b b ON a.id = b.foreign_id
WHERE b.foreign_id IS NULL;
In the example above, this query retrieves all rows from table_a that do not have a match in table_b.
2. RIGHT JOIN
A RIGHT JOIN returns all rows from the right table and the matched rows from the left table. If there is no match, NULLs are returned for columns from the left table.
Syntax:
SELECT a.*, b.*
FROM table_a a
RIGHT JOIN table_b b ON a.id = b.foreign_id
WHERE a.id IS NULL;
In this case, this query retrieves all rows from table_b that do not have a matching row in table_a.
3. FULL JOIN
A FULL JOIN returns all rows when there is a match in either the left or right table records. If there is no match, NULLs are returned for columns from the table that does not have a matching row.
Syntax:
SELECT a.*, b.*
FROM table_a a
FULL JOIN table_b b ON a.id = b.foreign_id
WHERE a.id IS NULL OR b.foreign_id IS NULL;
This query retrieves all rows from both table_a and table_b, ensuring that any rows that do not match either side are included.
Summary
- Use LEFT JOIN to find unmatched rows from the left table.
- Use RIGHT JOIN to find unmatched rows from the right table.
- Use FULL JOIN to find unmatched rows from both tables.
Points to Remember
- Always be specific about the condition for joining tables to avoid erroneous results.
- NULLs indicate that there was no match in the joined table.
- The performance of OUTER JOINs can vary; it’s important to optimize them based on your database and dataset size.