Getting everything ready for you…

Loading courses, problems and your progress. This takes just a moment.

0s

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:

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

13. Performance Tips

14. JOIN Interview Questions

  1. What is the difference between INNER JOIN and LEFT JOIN?
  2. How does RIGHT JOIN work?
  3. What is a FULL OUTER JOIN?
  4. What is a CROSS JOIN?
  5. What is a SELF JOIN?
  6. Why can a join return duplicate rows?
  7. What is the difference between ON and WHERE?
  8. 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

  1. Display each employee with their department name.
  2. Show all employees, including those without departments.
  3. Show every department, including departments with no employees.
  4. Find employees who have no matching department.
  5. Count employees per department, including departments with zero employees.
  6. 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