SQL Joins Explained: INNER, LEFT, RIGHT, FULL, CROSS and SELF JOIN
What each join returns, with one pair of tables and the output of every query.
Coding problems with instant test-case feedback
Write Python, C++, C or JavaScript in your browser and see which hidden tests pass.
A join combines rows from two tables based on a related column. Almost every SQL round in placement tests includes a join question, usually phrased as "find X that has no Y". Seeing all the join types on the same data is the fastest way to remember them.
1. The sample tables#
employees(id, name, dept_id): (1, Asha, 10), (2, Ravi, 20), (3, Meera, NULL), (4, Karan, 10).
departments(id, name): (10, Engineering), (20, Sales), (30, Marketing).
Meera has no department, and Marketing has no employees. These two rows are what make the joins differ.
2. Join types and their results#
All queries below join employees e with departments d on e.dept_id = d.id.
| Join | Returns | Rows in our example |
|---|---|---|
| INNER JOIN | Only rows with a match in both tables | Asha–Engineering, Ravi–Sales, Karan–Engineering (3 rows) |
| LEFT JOIN | All employees, department or NULL | The 3 above + Meera–NULL (4 rows) |
| RIGHT JOIN | All departments, employee or NULL | The 3 above + NULL–Marketing (4 rows) |
| FULL OUTER JOIN | Everything from both sides | The 3 matches + Meera–NULL + NULL–Marketing (5 rows) |
| CROSS JOIN | Every employee paired with every department | 4 × 3 = 12 rows, no ON clause |
| 1 | SELECT e.name, d.name AS department |
| 2 | FROM employees e |
| 3 | LEFT JOIN departments d ON e.dept_id = d.id; |
| 4 | |
| 5 | -- name | department |
| 6 | -- Asha | Engineering |
| 7 | -- Ravi | Sales |
| 8 | -- Meera | NULL |
| 9 | -- Karan | Engineering |
Practise placement aptitude on the real test pattern
TCS NQT, Infosys, Wipro, Accenture and Cognizant style mocks with section-wise analysis.
3. Anti-join: rows with no match#
To find employees without a department, or departments without employees, use an outer join and keep the rows where the other side is NULL. This pattern is called an anti-join.
| 1 | -- Departments with no employees |
| 2 | SELECT d.name |
| 3 | FROM departments d |
| 4 | LEFT JOIN employees e ON e.dept_id = d.id |
| 5 | WHERE e.id IS NULL; -- Marketing |
| 6 | |
| 7 | -- Same result with NOT EXISTS (often clearer) |
| 8 | SELECT d.name |
| 9 | FROM departments d |
| 10 | WHERE NOT EXISTS (SELECT 1 FROM employees e WHERE e.dept_id = d.id); |
In a LEFT JOIN, a condition on the right table placed in WHERE removes the NULL rows and quietly turns it into an inner join. Put such conditions in the ON clause if you want to keep unmatched left rows.
4. Self join#
A self join joins a table to itself with two aliases. The classic case is an employees table with a manager_id column that points to another employee.
| 1 | SELECT e.name AS employee, m.name AS manager |
| 2 | FROM employees e |
| 3 | LEFT JOIN employees m ON e.manager_id = m.id; |
5. Two questions you will see in tests#
Second highest salary: SELECT MAX(salary) FROM employees WHERE salary < (SELECT MAX(salary) FROM employees). With window functions: use DENSE_RANK() OVER (ORDER BY salary DESC) and pick rank 2.
Department-wise head count including empty departments: SELECT d.name, COUNT(e.id) FROM departments d LEFT JOIN employees e ON e.dept_id = d.id GROUP BY d.name. Note COUNT(e.id), not COUNT(*), so empty departments show 0.
Test Your Concept Mastery
Verify your retention on: SQL Joins Explained: INNER, LEFT, RIGHT, FULL, CROSS and SELF JOIN
Table A has 5 rows and table B has 4 rows. How many rows does A CROSS JOIN B return?
Which join returns unmatched rows from both tables?
Ready for a full proctored assessment?
Take a free aptitude mock on a real company pattern, with a worked solution for every question.
