SQL JOINs

In a relational database, related data is usually stored in separate tables to avoid duplication. For example, instead of storing the department name for every employee, we store only the department_id in the employee table. Whenever we need the department name, we combine both tables using a JOIN. Throughout this chapter, we’ll use the following tables.

Departments

Department ID	Department Name
1	Engineering
2	HR
3	Sales
4	Finance

Employees

Employee ID	Employee Name	Salary	Department ID
101	Rahul	65000	1
102	Anitha	55000	2
103	Kiran	72000	1
104	Sneha	50000	3
105	Ajay	80000	NULL

Notice that Ajay is not assigned to any departments.

Why Do We Need JOINs?

In a normalized database, related information is stored in different tables. For example, the employee table stores only the department_id, not the department name. If we execute the following query:

SELECT *
FROM employees;

We get:

Employee ID	Employee Name	Salary	Department ID
101	Rahul	65000	1
102	Anitha	55000	2
103	Kiran	72000	1
104	Sneha	50000	3
105	Ajay	80000	NULL

Although we know the department IDs, we don’t know the department names. To retrieve the department name, we combine the employee and department tables using a JOIN.

JOIN Syntax

SELECT columns
FROM table1
JOIN table2
ON table1.common_column = table2.common_column;

Explanation

JOIN specifies the table to combine.
ON specifies the matching condition.
The matching column is usually a Primary Key in one table and a Foreign Key in another.

Practice

Which clause specifies the matching condition between two tables?

Solution

The ON clause specifies how two tables should be matched.Example:

ON employees.department_id = departments.department_id

INNER JOIN

An INNER JOIN returns only the rows that have matching values in both tables. If a record exists in one table but has no matching record in the other table, it is not included in the result.

Example 1

Display employee names along with their department names.

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

Result

Employee	Department
Rahul	Engineering
Anitha	HR
Kiran	Engineering
Sneha	Sales

Notice that Ajay is not included because there is no matching departments.

Example 2

Display employees earning more than ₹60,000 along with their department names.

SELECT
    e.employee_name,
    e.salary,
    d.department_name
FROM employees e
INNER JOIN departments d
ON e.department_id = d.department_id
WHERE e.salary > 60000;

Result

Employee	Salary	Department
Rahul	65000	Engineering
Kiran	72000	Engineering

Practice

Display employee names with their department names.

Solution

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

Practice

Display employees working in the HR departments.

Solution

SELECT
    e.employee_name,
    d.department_name
FROM employees e
INNER JOIN departments d
ON e.department_id = d.department_id
WHERE d.department_name = 'HR';

Practice

Display employee names, salaries, and department names for employees earning more than ₹70,000.

Solution

SELECT
    e.employee_name,
    e.salary,
    d.department_name
FROM employees e
INNER JOIN departments d
ON e.department_id = d.department_id
WHERE e.salary > 70000;

LEFT JOIN

A LEFT JOIN returns all rows from the left table and only the matching rows from the right table. If no matching row exists in the right table, SQL fills those columns with NULL.

Example 1

Display all employees along with their department names.

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

Result

Employee	Department
Rahul	Engineering
Anitha	HR
Kiran	Engineering
Sneha	Sales
Ajay	NULL

Notice that Ajay is included because LEFT JOIN returns every row from the employees table.

Example 2

Display employees along with department names and salaries.

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

Practice

Display all employees with their department names.

Solution

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

Practice

Find employees who are not assigned to any departments.

Solution

SELECT
    employee_name
FROM employees
WHERE department_id IS NULL;

Practice

How does a LEFT JOIN differ from an INNER JOIN?

Solution

INNER JOIN returns only matching rows.
LEFT JOIN returns all rows from the left table and matching rows from the right table.
If there is no match, the right table columns contain NULL.

SQL & Databases

Libraries

SQL JOINs

SQL JOINs

Departments

Employees

Why Do We Need JOINs?

JOIN Syntax

Explanation

Practice

INNER JOIN

Example 1

Result

Example 2

Result

Practice

Practice

Practice

LEFT JOIN

Example 1

Result

Example 2

Practice

Practice

Practice

​SQL JOINs

​Departments

​Employees

​Why Do We Need JOINs?

​JOIN Syntax

​Explanation

​Practice

​INNER JOIN

​Example 1

​Result

​Example 2

​Result

​Practice

​Practice

​Practice

​LEFT JOIN

​Example 1

​Result

​Example 2

​Practice

​Practice

​Practice

SQL JOINs

Departments

Employees

Why Do We Need JOINs?

JOIN Syntax

Explanation

Practice

INNER JOIN

Example 1

Result

Example 2

Result

Practice

Practice

Practice

LEFT JOIN

Example 1

Result

Example 2

Practice

Practice

Practice