SQL JOIN Tutorial

Example Tables

We will use the following tables named employees and departments for our examples:

    CREATE TABLE employees (
        employee_id INT PRIMARY KEY,
        employee_name VARCHAR(50),
        department_id INT
    );

    CREATE TABLE departments (
        department_id INT PRIMARY KEY,
        department_name VARCHAR(50)
    );

    INSERT INTO employees (employee_id, employee_name, department_id) VALUES
    (1, 'John Doe', 1),
    (2, 'Jane Smith', 2),
    (3, 'Emily Davis', NULL),
    (4, 'Michael Brown', 3);

    INSERT INTO departments (department_id, department_name) VALUES
    (1, 'HR'),
    (2, 'Finance'),
    (4, 'IT');
    

Employees Table

employee_id	employee_name	department_id
1	John Doe	1
2	Jane Smith	2
3	Emily Davis	NULL
4	Michael Brown	3

Departments Table

department_id	department_name
1	HR
2	Finance
4	IT

Using SQL JOIN

SQL JOIN is used to combine rows from two or more tables, based on a related column between them.

Example: Inner Join

To perform an inner join on the employees and departments tables, use the following query:

    SELECT employees.employee_name, departments.department_name
    FROM employees
    INNER JOIN departments
    ON employees.department_id = departments.department_id;
    

Result:

employee_name	John Doe
department_name	HR
employee_name	Jane Smith
department_name	Finance

Example: Left Join

To perform a left join on the employees and departments tables, use the following query:

    SELECT employees.employee_name, departments.department_name
    FROM employees
    LEFT JOIN departments
    ON employees.department_id = departments.department_id;
    

Result:

employee_name	John Doe
department_name	HR
employee_name	Jane Smith
department_name	Finance
employee_name	Emily Davis
department_name	NULL
employee_name	Michael Brown
department_name	NULL

Example: Right Join

To perform a right join on the employees and departments tables, use the following query:

    SELECT employees.employee_name, departments.department_name
    FROM employees
    RIGHT JOIN departments
    ON employees.department_id = departments.department_id;
    

Result:

employee_name	John Doe
department_name	HR
employee_name	Jane Smith
department_name	Finance
employee_name	NULL
department_name	IT