Understanding SQL JOIN Types: INNER, LEFT, RIGHT, FULL OUTER and Their Exclusive Variants

This guide presents seven SQL JOIN patterns, showing how each combines rows from two tables based on matching keys. Diagrams illustrate the logical result sets, and example SELECT statements demonstrate the exact syntax.

1. INNER JOIN (Inner Join)

Returns only rows with matching keys in both tables.

SELECT <select_list>
FROM Table_A A
INNER JOIN Table_B B
ON A.Key = B.Key

2. LEFT JOIN (Left Join)

Returns all rows from the left table and matching rows from the right table; non‑matching right rows appear as NULL.

SELECT <select_list>
FROM Table_A A
LEFT JOIN Table_B B
ON A.Key = B.Key

3. RIGHT JOIN (Right Join)

Returns all rows from the right table and matching rows from the left table; non‑matching left rows appear as NULL.

SELECT <select_list>
FROM Table_A A
RIGHT JOIN Table_B B
ON A.Key = B.Key

4. FULL OUTER JOIN (Outer Join)

Returns all rows when there is a match in either left or right table; unmatched rows contain NULLs for the opposite side.

SELECT <select_list>
FROM Table_A A
FULL OUTER JOIN Table_B B
ON A.Key = B.Key

5. LEFT JOIN EXCLUDING INNER JOIN (Left Anti‑Join)

Returns rows from the left table that have no matching key in the right table.

SELECT <select_list>
FROM Table_A A
LEFT JOIN Table_B B
ON A.Key = B.Key
WHERE B.Key IS NULL

6. RIGHT JOIN EXCLUDING INNER JOIN (Right Anti‑Join)

Returns rows from the right table that have no matching key in the left table.

SELECT <select_list>
FROM Table_A A
RIGHT JOIN Table_B B
ON A.Key = B.Key
WHERE A.Key IS NULL

7. FULL OUTER JOIN EXCLUDING INNER JOIN (Full Anti‑Join)

Returns rows that exist in either table but not in both.

SELECT <select_list>
FROM Table_A A
FULL OUTER JOIN Table_B B
ON A.Key = B.Key
WHERE A.Key IS NULL OR B.Key IS NULL

These examples, accompanied by visual diagrams, help developers choose the appropriate join for their query requirements.