Guides › Database & SQL
SQL Joins Explained with Examples
Understand INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN, CROSS JOIN, and SELF JOIN using simple tables, visual examples, practical SQL queries, and interview questions.
SQL Joins Explained with Examples
Introduction
SQL joins combine rows from two or more tables using related columns. They help answer questions such as which students enrolled in courses, which customers placed orders, and which employees belong to departments.
1. Create Sample Tables
We will use two tables: employees and departments.
CREATE TABLE departments (
department_id INT PRIMARY KEY,
department_name VARCHAR(50)
);
CREATE TABLE employees (
employee_id INT PRIMARY KEY,
employee_name VARCHAR(100),
department_id INT
);
Sample data:
INSERT INTO departments VALUES
(10, 'IT'),
(20, 'HR'),
(30, 'Sales'),
(40, 'Marketing');
INSERT INTO employees VALUES
(1, 'Aman', 10),
(2, 'Priya', 20),
(3, 'Ravi', 10),
(4, 'Neha', NULL);
In this example, Neha has no department assigned. The Marketing department has no employee assigned.
2. What Is a JOIN?
A JOIN combines data from tables using a condition, commonly a primary key and a foreign key or another related column.
General syntax:
SELECT columns
FROM table_a
JOIN table_b
ON table_a.key_column = table_b.key_column;
Always check whether the join key is unique. Joining duplicate keys can multiply rows in the result.
3. INNER JOIN
INNER JOIN returns only rows with matching values in both tables.
SELECT
employees.employee_name,
departments.department_name
FROM employees
INNER JOIN departments
ON employees.department_id = departments.department_id;
Expected matches:
- Aman — IT
- Priya — HR
- Ravi — IT
Neha is excluded because she has no matching department. Marketing is excluded because no employee matches it.
Use INNER JOIN when you only need matching records.
4. LEFT JOIN
LEFT JOIN returns every row from the left table and matching rows from the right table. When there is no match, right-table columns are NULL.
SELECT
employees.employee_name,
departments.department_name
FROM employees
LEFT JOIN departments
ON employees.department_id = departments.department_id;
This includes Aman, Priya, Ravi, and Neha. Neha's department name is NULL.
Use LEFT JOIN when every row from your main table must remain in the result.
5. RIGHT JOIN
RIGHT JOIN returns every row from the right table and matching rows from the left table.
SELECT
employees.employee_name,
departments.department_name
FROM employees
RIGHT JOIN departments
ON employees.department_id = departments.department_id;
This includes every department, including Marketing, whose employee name is NULL.
RIGHT JOIN is supported by MySQL and PostgreSQL. You can often rewrite it as a LEFT JOIN by switching table order.
6. FULL OUTER JOIN
FULL OUTER JOIN returns matched rows, unmatched rows from the left table, and unmatched rows from the right table.
SELECT
employees.employee_name,
departments.department_name
FROM employees
FULL OUTER JOIN departments
ON employees.department_id = departments.department_id;
This includes Neha and the unmatched Marketing department.
PostgreSQL supports FULL OUTER JOIN directly. MySQL does not support it directly; a carefully constructed UNION of left- and right-side unmatched results can be used instead.
7. CROSS JOIN
CROSS JOIN returns every possible combination of rows from the two tables.
SELECT
employees.employee_name,
departments.department_name
FROM employees
CROSS JOIN departments;
If one table contains 4 rows and the other contains 4 rows, the result contains 16 combinations.
Use CROSS JOIN when every combination is intentionally needed, such as generating a set of product and size combinations.
8. SELF JOIN
A SELF JOIN joins a table to itself. It is useful for hierarchical data, such as employees and their managers.
SELECT
employee.employee_name AS employee,
manager.employee_name AS manager
FROM employees AS employee
LEFT JOIN employees AS manager
ON employee.manager_id = manager.employee_id;
For this example, manager_id would need to be added to the employees table. Aliases distinguish the two roles of the same table.
9. JOIN with Multiple Conditions
You can use more than one condition in the ON clause.
SELECT *
FROM orders AS o
JOIN order_items AS i
ON o.order_id = i.order_id
AND o.customer_id = i.customer_id;
Only use conditions that match your actual data model. Incorrect conditions may exclude valid rows or create misleading results.
10. JOIN with WHERE
WHERE filters the joined result.
SELECT e.employee_name, d.department_name
FROM employees AS e
LEFT JOIN departments AS d
ON e.department_id = d.department_id
WHERE e.employee_name LIKE 'A%';
Be careful when filtering right-table columns in WHERE after a LEFT JOIN. A condition such as WHERE d.department_name = 'IT' removes unmatched rows, making the result behave like an inner join for that condition.
11. JOIN with GROUP BY
Find the number of employees in each department:
SELECT
d.department_name,
COUNT(e.employee_id) AS employee_count
FROM departments AS d
LEFT JOIN employees AS e
ON d.department_id = e.department_id
GROUP BY d.department_id, d.department_name;
COUNT(e.employee_id) returns zero for a department with no matching employee. COUNT(*) would count the preserved department row instead.
12. Common JOIN Mistakes
- Forgetting the ON condition and accidentally producing a Cartesian product.
- Joining columns that do not represent the same relationship.
- Assuming a join always returns one row per entity.
- Ignoring duplicate keys.
- Using COUNT(*) when counting matches after an outer join.
- Filtering an outer-joined table in WHERE unintentionally.
- Selecting ambiguous column names without table aliases.
13. Performance Tips
- Index join columns when appropriate.
- Use EXPLAIN to inspect query plans.
- Select only the columns you need.
- Ensure data types and key definitions are appropriate.
- Test queries with realistic data volumes.
- Do not add indexes blindly; measure the effect on reads and writes.
14. JOIN Interview Questions
- What is the difference between INNER JOIN and LEFT JOIN?
- How does RIGHT JOIN work?
- What is a FULL OUTER JOIN?
- What is a CROSS JOIN?
- What is a SELF JOIN?
- Why can a join return duplicate rows?
- What is the difference between ON and WHERE?
- How do you find records that have no match in another table?
Example: Find employees without a matching department.
SELECT e.*
FROM employees AS e
LEFT JOIN departments AS d
ON e.department_id = d.department_id
WHERE d.department_id IS NULL;
15. Practice Exercises
- Display each employee with their department name.
- Show all employees, including those without departments.
- Show every department, including departments with no employees.
- Find employees who have no matching department.
- Count employees per department, including departments with zero employees.
- Create an orders and customers example and join the two tables.
Conclusion
Choose a join based on which rows must appear in your result. Practice with small tables first, inspect the output, and then test more complex queries with multiple joins and aggregate functions.
More guides
- How to Use Codify: A 7-Day Start Plan — New here? Pick a roadmap, make a study plan, start the DSA sheet, revise with notes, practise for placements and test yourself — one step a day, then a daily routine.
- Infosys Preparation Guide: Online Test, Pseudocode and Interview — Roles (SE, DSE, SP), what each section of the Infosys online test checks, how to crack pseudocode and reasoning, a 4-week plan and the interview.
- Wipro Elite NLTH Preparation: Aptitude, Essay and Coding — The Wipro fresher test round by round — quant, logical and verbal, the written essay, two coding problems — plus a 3-week plan and interview tips.
- Accenture Preparation Guide: Cognitive, Technical, Coding and Communication — Every Accenture round explained — cognitive, MS Office/networking/cloud technical MCQs, pseudocode, coding and the spoken-English test — with a 4-week plan.