AI Hints
Homeโ€บSQL Tutorialโ€บMore Kinds of Join
SQL ยท Lesson 52 of 126

RIGHT, FULL, and CROSS Joins

The rest of the family.

โฑ 2 min readโ–ถ Video at 1:13:36๐ŸŽ“ Free
Watch this lesson in the SQL courseWatch on YouTube โ†—Full SQL course

You've met inner join, and left join. There are three more. The good news is, you'll rarely need them. But you should know what they do, so you can read other people's queries.

RIGHT, FULL, and CROSS Joins
From the video: RIGHT, FULL, and CROSS Joins
  • You know INNER and LEFT
  • Three more joins
  • Rarely needed, good to know

RIGHT JOIN

A right join keeps every row from the right table, instead of the left. So orders, right join customers, is the same as customers, left join orders. Run it. The same customers with no orders, found the other way round. Most people just swap the tables, and use a left join.

query.sqlsql
SELECT c.first_name, o.order_id
FROM orders AS o
RIGHT JOIN customers AS c ON c.customer_id = o.customer_id
WHERE o.order_id IS NULL
LIMIT 3;
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚ first_name โ”‚ order_id โ”‚
โ”œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ผโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ค
โ”‚ Daniel     โ”‚          โ”‚
โ”‚ Daniel     โ”‚          โ”‚
โ”‚ Nina       โ”‚          โ”‚
โ””โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ดโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”˜

FULL OUTER JOIN

A full outer join keeps everything from both tables. Matched rows are joined. Unmatched rows, from either side, are kept, with nulls. It's handy for comparing two lists, like this month's stock against last month's, to see what appeared, and what disappeared.

Matched rowsjoined together
Left-only rowskept, with NULLs
Right-only rowskept, with NULLs
Use itto compare two lists

CROSS JOIN

A cross join has no on at all. It pairs every row from one table, with every row from the other. Thirty books, and eight staff members. Run it. Two hundred and forty combinations. Be careful, a cross join of two big tables can produce millions of rows.

query.sqlsql
SELECT COUNT(*) AS combinations
FROM books
CROSS JOIN staff;
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚ combinations โ”‚
โ”œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ค
โ”‚ 240          โ”‚
โ””โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”˜

Summary

Right join is a left join, flipped. Full join keeps both sides. Cross join pairs everything with everything. Next, a trap that catches almost everyone.

Key takeaways
  • RIGHT = LEFT, flipped
  • FULL keeps both sides
  • CROSS pairs everything
Next up: The Double Counting Trap โ€” when joins multiply rows