C BY-NC-ND Vertabelo SA
SQL JOINs Cheat Sheet
NATURAL JOIN
If the tables h ave columns with the same name, you can use
NATURAL JOIN instead of JOIN.
The common column a ppears only once in th e result table.
Note: NATURAL JOIN is rarely used in real life .
SELECT *
FROM toy
NATURAL JOIN cat;
ca t_i d to y_id toy_name cat_name
1 5 ball Kitty
1 3 mouse Kitty
3 1 ball Sam
4 4 mouse Misty
LEFT JOIN
LEFT JOI N returns all row s from the left table with matching rows fr om the right table. R ows without a match are fil led
with NULLs. L EFT JOIN is al so called LEFT OUTER JOIN.
SELECT *
FROM toy
LEFT JOIN cat
ON toy.cat_id = cat.cat_id;
to y_id t oy_na me cat_id cat_id cat_name
5ball 1 1 Kitty
3mouse 1 1 Kitty
1ball 3 3 Sam
4mouse 4 4 Misty
2 spring NULL NULL NULL
whole left table
RIGHT JOIN
RIGHT JOIN retu rns all rows from the r ight table with match ing rows from the left ta ble. Rows without a matc h are
filled with NULL s. RIGHT JOIN is als o called RIGHT OUTER JOIN.
SELECT *
FROM toy
RIGHT J OIN cat
ON toy.cat_id = cat.cat_id;
to y_id t oy_na me cat_id cat_id cat_name
5ball 1 1 Kitty
3mouse 1 1 Kitty
NULL NULL N ULL 2 Hugo
1ball 3 3 Sam
4mouse 44 Misty
whole right table
FULL JOIN
FULL JOIN returns all rows f rom the left table and all rows from the r ight table. It fills t he non-matching ro ws with
NULLs . FULL JOIN is al so called FULL OUTER JOIN.
SELECT *
FROM toy
FULL JOIN cat
ON toy.cat_id = cat.cat_id;
to y_id t oy_na me cat_id cat_id cat_name
5ball 1 1 Kitty
3mouse 1 1 Kitty
NULL NULL N ULL 2 Hugo
1ball 3 3 Sam
4mouse 4 4 Misty
2 spring NULL NULL NULL
whole left table whole right table
CROSS JOIN
CROSS JOIN returns all possible combinations of rows from the left and r ight tables.
to y_id t oy_na me cat_id cat_id cat_name
1ball 3 1 Kitty
2spring NULL 1 Kitty
3mouse 1 1 Kitty
4 mouse 4 1 Kitty
5 ball 1 1 Kitty
1 ball 3 2 Hugo
2 spring NULL 2 Hugo
3 mouse 1 2 Hugo
4 mouse 4 2 Hugo
5 ball 1 2 Hugo
1 ball 3 3 Sam
··· ··· ··· ··· ···
SELECT *
FROM toy
CROSS JOIN ca t;
SELECT *
FROM t oy, c at;
Other syntax:
JOIN
JOIN retu rns all rows that matc h the ON condition. JOIN is also c alled INNER JOIN.
SELECT *
FROM toy
JOIN cat
ON toy.cat_id = cat.cat_id;
to y_id t oy_na me cat_id cat_id cat_name
5ball 1 1 Kitty
3mouse 1 1 Kitty
1ball 3 3 Sam
4mouse 4 4 Misty
There is als o another, older synta x, but it isn't recommended.
List joined t ables in the FROM clause, and place th e conditions in th e WHERE clause.
SELECT *
FROM t oy, c at
WHERE toy.cat_id = cat.cat_id;
JOIN combin es data from two t ables.
JOINING TABLES
JOIN typi cally combines rows with equal value s for the specified columns. Usually, one t able contains a primary key,
which is a column or co lumns that uniquely ide ntify rows in the t able (the ca t_i d column in t he cat table).
The other tab le has a column or columns tha t refer to the prima ry key columns in the fir st table (the c at _id column in
the toy table). Suc h columns are foreign keys. The JOIN cond ition is the equalit y between the p rimary key columns in
one table and co lumns referring to t hem in the other tabl e.
CAT
ca t_i d cat_name
1Kitt y
2Hugo
3Sam
4Mist y
TOY
to y_id t oy_na me c at _id
1ball 3
2spring NULL
3mouse 1
4mouse 4
5ball 1
JOIN CONDITIONS
The JOIN con dition doesn't have to b e an equality – it can b e any condition you want . JOIN doesn't in terpret the JOIN
condition, it only checks if the rows satisfy the given condition.
To refer to a column in the J OIN query, you have to u se the full column name: fi rst the table name , then a dot (.) and the
column name:
ON cat.cat_id = toy.cat_id
You can omit the tab le name and use just th e column name if the name of t he column is unique within a ll columns in the
joined tables.
