SQL Joins Explained

A join combines rows from two tables by matching a column they share, such as an employee’s dept_id and a department’s dept_id. This overview runs every type of join on the same two small tables, so you can see exactly which rows each one keeps and which it drops. Each join then has its own detailed lesson.

Why joins exist

A well-designed database stores each fact once. Department names live in departments; employees store only a dept_id. That avoids repeating “Engineering, Bengaluru” on every engineer’s row, but it means that to print an employee next to their department name you must join the two tables back together.

The two tables

To keep the comparison easy, this lesson uses a few columns of each table:

Notice the two “unmatched” rows that make the joins differ:

• Meera (100) has dept_id = NULL: she belongs to no department.
• Finance (5) has no employees.

Join types at a glance

INNER JOIN

SELECT e.emp_id, e.first_name, d.dept_nameFROM employees eINNER JOIN departments d ON d.dept_id = e.dept_idORDER BY e.emp_id;

Full lesson: INNER JOIN.

LEFT JOIN

SELECT e.emp_id, e.first_name, d.dept_nameFROM employees eLEFT JOIN departments d ON d.dept_id = e.dept_idORDER BY e.emp_id;

Full lesson: LEFT JOIN.

RIGHT JOIN

SELECT e.emp_id, e.first_name, d.dept_nameFROM employees eRIGHT JOIN departments d ON d.dept_id = e.dept_idORDER BY d.dept_id, e.emp_id;

Full lesson: RIGHT JOIN.

FULL OUTER JOIN

MySQL has no FULL OUTER JOIN keyword. Combine a left and a right join with UNION, which also removes the rows they share:

SELECT e.emp_id, e.first_name, d.dept_nameFROM employees eLEFT JOIN departments d ON d.dept_id = e.dept_idUNIONSELECT e.emp_id, e.first_name, d.dept_nameFROM employees eRIGHT JOIN departments d ON d.dept_id = e.dept_idORDER BY emp_id;

Full lesson: FULL OUTER JOIN.

CROSS JOIN

A cross join pairs every row of one table with every row of the other. With 3 product categories and 2 sizes you get 6 combinations:

SELECT c.category, s.sizeFROM (SELECT DISTINCT category FROM products) cCROSS JOIN (SELECT 'S' AS size UNION ALL SELECT 'L') sORDER BY c.category, s.size;

Full lesson: CROSS JOIN.

SELF JOIN

A table joined to itself, here to put each employee next to their manager:

SELECT e.first_name AS employee, m.first_name AS managerFROM employees eLEFT JOIN employees m ON m.emp_id = e.manager_idORDER BY e.emp_id;

Full lesson: SELF JOIN.

Joining more than two tables

Joins chain: each JOIN adds one more table to the rows built so far. Who bought what?

SELECT o.order_id, c.customer_name, p.product_name, oi.quantityFROM orders oJOIN customers   c  ON c.customer_id = o.customer_idJOIN order_items oi ON oi.order_id   = o.order_idJOIN products    p  ON p.product_id  = oi.product_idWHERE o.order_id IN (1001, 1006)ORDER BY o.order_id, p.product_name;

Which join should I use?

SQL joins FAQ