SQL Joins: Combining Data Across Relational Tables

Learn how INNER JOIN and LEFT JOIN work to combine related tables, with practical examples using the employee and department tables.

In a well-designed relational database, related data is deliberately spread across multiple tables to eliminate redundancy. The employee table stores a department_id rather than the full department name, because storing the name in every employee row would mean updating hundreds of rows every time a department was renamed. JOINs are the mechanism that bring those separate tables back together when you need to query them — letting you retrieve the employee’s name and the department’s name in a single result set. This chapter covers the two most important join types: INNER JOIN and LEFT JOIN.

Reference Tables

All examples in this chapter use these tables.

Department:

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

Employee:

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

Ajay has no department assigned (NULL). His row will behave differently depending on which join type you use — watch for this in the examples below.

Why Do We Need JOINs?

Without a JOIN, a query against the employee table returns only the numeric department_id, not the human-readable department name:

SELECT * FROM employee;

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

To see the department name alongside each employee, you JOIN the two tables on their shared key column.

JOIN Syntax

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

• JOIN names the second table to include.
• ON specifies the matching condition — usually a foreign key in one table equalling a primary key in the other.
• Table aliases (like e and d) are commonly used to keep queries concise.

Practice: Which clause specifies the matching condition?

The ON clause specifies how two tables should be matched.

ON employee.department_id = department.department_id

INNER JOIN

An INNER JOIN returns only the rows that have a matching value in both tables. If a row in either table has no match in the other, it is excluded from the result entirely.

Example 1: Employee names with department names

SELECT
    e.employee_name,
    d.department_name
FROM employee e
INNER JOIN department d
ON e.department_id = d.department_id;

Result:

Employee Name	Department Name
Rahul	Engineering
Anitha	HR
Kiran	Engineering
Sneha	Sales

Notice that Ajay is not included — his department_id is NULL, so there is no matching row in the Department table.

Example 2: High-earners with their department

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

Result:

Employee Name	Salary	Department Name
Rahul	65000	Engineering
Kiran	72000	Engineering

Practice: Employees in the HR department

Display all employees who work in HR.

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

Practice: Employees earning more than ₹70,000 with departments

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

LEFT JOIN

A LEFT JOIN returns all rows from the left table, plus any matching rows from the right table. Where no match exists in the right table, SQL fills the right table’s columns with NULL.

Example 1: All employees including unassigned ones

SELECT
    e.employee_name,
    d.department_name
FROM employee e
LEFT JOIN department d
ON e.department_id = d.department_id;

Result:

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

Ajay now appears with NULL for department_name, because LEFT JOIN guarantees every row from the left table (employee) is included.

Example 2: All employees with salary and department

SELECT
    e.employee_name,
    e.salary,
    d.department_name
FROM employee e
LEFT JOIN department d
ON e.department_id = d.department_id;

Finding employees with no department

Combining LEFT JOIN with an IS NULL filter lets you find records that have no match in the right table — a common real-world need.

SELECT
    e.employee_name
FROM employee e
LEFT JOIN department d
ON e.department_id = d.department_id
WHERE d.department_id IS NULL;

Alternatively, since department_id is directly on the employee table:

SELECT employee_name
FROM employee
WHERE department_id IS NULL;

Practice: Display all employees with department names

SELECT
    e.employee_name,
    d.department_name
FROM employee e
LEFT JOIN department d
ON e.department_id = d.department_id;

Practice: How does a LEFT JOIN differ from an INNER JOIN?

• INNER JOIN returns only rows where both tables have a matching key.
• LEFT JOIN returns all rows from the left table, plus matching rows from the right. Where there is no match, the right-table columns contain NULL.

Use an INNER JOIN when you only want complete, matched records. Use a LEFT JOIN when you want to keep all rows from the left table regardless of whether a match exists on the right.

INNER JOIN vs. LEFT JOIN — Side by Side

Feature	INNER JOIN	LEFT JOIN
Rows from left table	Only matching rows	All rows
Rows from right table	Only matching rows	Only matching rows
Unmatched left rows	Excluded	Included with NULL
Use when…	You only want complete pairs	You want all left-side records

A useful mental model: think of INNER JOIN as an intersection (only shared records) and LEFT JOIN as left table plus intersection (everything from the left, matched where possible).