RIGHT JOIN
RIGHT JOIN keeps every row of the right table, the one after RIGHT JOIN:
sql
SELECT p.name, c.name
FROM products p
RIGHT JOIN categories c ON p.category_id = c.id;Posters has no products. It appears once, with NULL as the product name.
A RIGHT JOIN B returns the same rows as B LEFT JOIN A. Most people write the LEFT form and put the table whose rows they must keep first.
FULL JOIN
FULL JOIN (or FULL OUTER JOIN) keeps every row of both tables. A row without a partner, on either side, gets NULLs for the other table.
products p
| name | category_id |
|---|---|
| Kraken Plush | 1 |
| Mystery Box | NULL |
categories c
| id | name |
|---|---|
| 1 | Plush |
| 5 | Posters |
products p FULL JOIN categories c ON p.category_id = c.id
| p.name | c.name |
|---|---|
| Kraken Plush | Plush |
| Mystery Box | NULL |
| NULL | Posters |
Which join keeps what
| Join | Rows without a partner that it keeps |
|---|---|
JOIN (inner) | none |
LEFT JOIN | from the left table |
RIGHT JOIN | from the right table |
FULL JOIN | from both tables |