SQL Joins

Joins combine rows from two or more tables using a related column.

SQL

-- departments
+---------+-----------+
| dept_id | dept_name |
+---------+-----------+
| 10      | IT        |
| 20      | HR        |
| 30      | Finance   |
+---------+-----------+
-- staff
+----+-------+---------+
| id | name  | dept_id |
+----+-------+---------+
| 1  | Asha  | 10      |
| 2  | Ravi  | 20      |
| 3  | Karan | NULL    |
+----+-------+---------+

Join	Returns
INNER JOIN	Only matching rows from both tables
LEFT JOIN	All rows from the left table, plus matches (NULL if none)
RIGHT JOIN	All rows from the right table, plus matches
FULL OUTER JOIN	All rows from both tables
CROSS JOIN	Cartesian product
SELF JOIN	A table joined with itself

Shaded area = rows returned (A = left table, B = right table)

INNER JOIN

SQL

SELECT s.name, d.dept_name
FROM staff s
INNER JOIN departments d ON s.dept_id = d.dept_id;

+------+-----------+ | name | dept_name | +------+-----------+ | Asha | IT | | Ravi | HR | +------+-----------+

LEFT JOIN

SQL

SELECT s.name, d.dept_name
FROM staff s
LEFT JOIN departments d ON s.dept_id = d.dept_id;

+-------+-----------+ | name | dept_name | +-------+-----------+ | Asha | IT | | Ravi | HR | | Karan | NULL | +-------+-----------+

RIGHT JOIN

SQL

SELECT s.name, d.dept_name
FROM staff s
RIGHT JOIN departments d ON s.dept_id = d.dept_id;

+------+-----------+ | name | dept_name | +------+-----------+ | Asha | IT | | Ravi | HR | | NULL | Finance | +------+-----------+

Track your progress through the SQL course

← Prev Next →