How to Master SQL Joins - Visual Guide to SQL Joins with Examples

This visual guide to SQL joins should help you determine which one of the various SQL join types to use for the query you need to run.

The inner join is the default used when you don't specify the type of Join. When you do an inner join of two tables, it returns results which are matched in both tables.

SELECT * FROM photos
INNER JOIN galleries
ON photos.gallery = galleries.id;

Full outer join produces the set of all records in Table A and Table B, with matching records from both sides where available. If there is no match, the missing side will contain null.

SELECT * FROM photos
FULL OUTER JOIN galleries
ON photos.id = galleries.id

Left outer join produces a complete set of records from Table A, with the matching records (where available) in Table B. If there is no match, the right side will contain null.

SELECT * FROM photos
LEFT OUTER JOIN galleries
ON photos.gallery = galleries.id
WHERE galleries.id IS null

To produce the set of records only in Table A, but not in Table B, we perform the same left outer join, then exclude the records we don't want from the right side via a where clause.

SELECT * FROM photos
FULL OUTER JOIN galleries
ON photos.gallery = galleries.id
WHERE galleries.id IS null

To produce the set of records unique to Table A and Table B, we perform the same full outer join, then exclude the records we don't want from both sides via a where clause.

SELECT * FROM photos
FULL OUTER JOIN galleries
ON photos.id = galleries.id
WHERE photos.gallery IS null
OR galleries.id IS null