SQL JOINs: Combine Data from Multiple Tables in SQLite

Relational databases store related data in separate tables on purpose — it reduces duplication and keeps updates consistent. But when you need to answer questions that span multiple tables, you need JOINs. A JOIN combines rows from two (or more) tables based on a matching condition. This page teaches you every major join type, when to use each one, and the mistakes that trip up beginners.

Why JOINs Exist

Consider: your employee table stores department_id, but not the department name. To display employee names alongside their department names you must connect the two tables.

-- Without a JOIN: you see IDs, not names
SELECT employee_name, department_id FROM employee;

employee_name	department_id
Rahul Sharma	1
Anitha Rao	2
Gopal Krishna	NULL

-- With a JOIN: you see meaningful names
SELECT e.employee_name, d.department_name
FROM employee e
INNER JOIN department d ON e.department_id = d.department_id;

employee_name	department_name
Rahul Sharma	Engineering
Anitha Rao	Human Resources

Notice that Gopal Krishna disappears — they have no matching department. That is exactly INNER JOIN behavior, and understanding when that is correct (versus using LEFT JOIN) is the core skill of this page.

The Sample Tables

department

department_id	department_name
1	Engineering
2	Human Resources
3	Sales
4	Finance
5	Marketing

employee (abbreviated)

employee_id	employee_name	salary	department_id
101	Rahul Sharma	65000	1
102	Priya Reddy	72000	1
105	Anitha Rao	56000	2
107	Ajay Verma	50000	3
110	Gopal Krishna	53000	NULL

JOIN Syntax

SELECT columns
FROM   left_table  [alias]
JOIN   right_table [alias]
ON     left_table.column = right_table.column;

The ON clause specifies the matching condition — almost always a primary key on one side and a foreign key on the other. Use table aliases (short names like e and d) to keep your queries readable, especially when joining multiple tables.

INNER JOIN

An INNER JOIN returns only the rows that have a matching value in both tables. Rows with no match on either side are excluded.

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

Result: Every employee with a non-null department_id that exists in the department table. Gopal Krishna (NULL department) is excluded. Any department with no employees is also excluded.

INNER JOIN with WHERE and ORDER BY

-- Engineering employees earning more than ₹60,000
SELECT
    e.employee_name,
    e.salary,
    d.department_name
FROM employee e
INNER JOIN department d ON e.department_id = d.department_id
WHERE d.department_name = 'Engineering'
AND   e.salary > 60000
ORDER BY e.salary DESC;

Practice: HR employees

Write a query that returns the name and salary of all employees in the Human Resources department, sorted by salary descending.

Solution

SELECT
    e.employee_name,
    e.salary
FROM employee e
INNER JOIN department d ON e.department_id = d.department_id
WHERE d.department_name = 'Human Resources'
ORDER BY e.salary DESC;

LEFT JOIN

A LEFT JOIN returns all rows from the left (first) table, plus the matching rows from the right table. Where there is no match, the right table’s columns are filled with NULL.

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

Result: All 10 employees appear. Gopal Krishna’s department_name shows as NULL.

employee_name	salary	department_name
Rahul Sharma	65000	Engineering
…	…	…
Gopal Krishna	53000	NULL

Find Unassigned Employees

LEFT JOIN + IS NULL is the standard pattern for finding rows in the left table that have no match in the right table.

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

This returns only the employees who have not been assigned to any department — a common data-quality check.

Practice: Departments with no employees

Write a query that lists department names that currently have no employees. (Hint: join from department to employee and look for NULL on the employee side.)

Solution

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

RIGHT JOIN

A RIGHT JOIN returns all rows from the right (second) table, plus matching rows from the left. It is the mirror of LEFT JOIN.

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

Result: All departments appear — even Marketing and Finance if they have no employees — with NULL in the employee columns where there is no match.

SQLite does not natively support RIGHT JOIN. You can achieve the same result by swapping the table order and using a LEFT JOIN:

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

FULL OUTER JOIN

A FULL OUTER JOIN returns all rows from both tables. Rows with matches are merged; unmatched rows on either side appear with NULL for the other table’s columns.

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

SQLite does not support FULL OUTER JOIN directly. Emulate it by combining a LEFT JOIN and a RIGHT JOIN (rewritten as LEFT JOIN) with UNION ALL, then deduplicating:

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

UNION

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

Joining Three or More Tables

You chain multiple JOIN clauses. Each new JOIN adds one more table to the result.

-- employees + departments + a hypothetical projects table
SELECT
    e.employee_name,
    d.department_name,
    p.project_name
FROM employee e
INNER JOIN department d ON e.department_id = d.department_id
INNER JOIN project    p ON e.employee_id   = p.lead_employee_id
ORDER BY d.department_name, e.employee_name;

Work through joins one at a time when building complex queries: start with two tables, verify the result, then add the next join.

Self-JOIN

A self-join connects a table to itself. A common use case is a management hierarchy — where each employee row has a manager_id that references another employee_id in the same table.

ALTER TABLE employee ADD COLUMN manager_id INTEGER;

-- Find each employee and their manager's name
SELECT
    e.employee_name  AS employee,
    m.employee_name  AS manager
FROM employee e
LEFT JOIN employee m ON e.manager_id = m.employee_id
ORDER BY manager;

You must alias the table twice (here as e and m) so SQL can distinguish which “copy” of the table each reference refers to.

The Cartesian Product Trap

If you forget the ON clause, SQL returns the Cartesian product — every row in the left table paired with every row in the right table. For tables with 25 and 5 rows, that is 125 rows of meaningless combinations.

-- ❌ Missing ON clause — produces a Cartesian product
SELECT e.employee_name, d.department_name
FROM employee e
JOIN department d;  -- 25 × 5 = 125 rows of noise

-- ✅ Correct — include the ON clause
SELECT e.employee_name, d.department_name
FROM employee e
JOIN department d ON e.department_id = d.department_id;

Choosing the Right JOIN

INNER JOIN

Use when you only want rows that have a match on both sides. Excludes unmatched rows entirely.“Show me employees who have a department.”

LEFT JOIN

Use when you want every row from the left table, whether or not it has a match. Unmatched right columns become NULL.“Show me all employees, with their department if they have one.”

RIGHT JOIN

Use when you want every row from the right table. Equivalent to swapping the table order in a LEFT JOIN.“Show me all departments, with their employees if any exist.”

FULL OUTER JOIN

Use when you need every row from both sides regardless of matches. Useful for data reconciliation.“Show me all employees and all departments, matched where possible.”

Complete Practice: Departmental salary report

Write a query that returns: department_name, the count of employees in each department, and the average salary — but only for departments that have at least one employee. Sort by average salary descending.

Solution

SELECT
    d.department_name,
    COUNT(e.employee_id)  AS headcount,
    AVG(e.salary)         AS avg_salary
FROM department d
INNER JOIN employee e ON d.department_id = e.department_id
GROUP BY d.department_id, d.department_name
HAVING COUNT(e.employee_id) >= 1
ORDER BY avg_salary DESC;

Using INNER JOIN here naturally excludes departments with no employees. If you want to include zero-headcount departments, switch to LEFT JOIN and change HAVING COUNT(e.employee_id) >= 1 to >= 0.

Next: Advanced SQL

Go beyond the basics — write subqueries, CTEs, window functions, CASE expressions, and learn how indexes and views work.

Project Development

SQL & Databases

Data Analysis

SQL JOINs: Combine Data from Multiple Tables in SQLite

Why JOINs Exist

The Sample Tables

JOIN Syntax

INNER JOIN

INNER JOIN with WHERE and ORDER BY

Practice: HR employees

LEFT JOIN

Find Unassigned Employees

Practice: Departments with no employees

RIGHT JOIN

FULL OUTER JOIN

Joining Three or More Tables

Self-JOIN

The Cartesian Product Trap

Choosing the Right JOIN

INNER JOIN

LEFT JOIN

RIGHT JOIN

FULL OUTER JOIN

Complete Practice: Departmental salary report

Next: Advanced SQL

​Why JOINs Exist

​The Sample Tables

​JOIN Syntax

​INNER JOIN

​INNER JOIN with WHERE and ORDER BY

​Practice: HR employees

​LEFT JOIN

​Find Unassigned Employees

​Practice: Departments with no employees

​RIGHT JOIN

​FULL OUTER JOIN

​Joining Three or More Tables

​Self-JOIN

​The Cartesian Product Trap

​Choosing the Right JOIN

INNER JOIN

LEFT JOIN

RIGHT JOIN

FULL OUTER JOIN

​Complete Practice: Departmental salary report

Next: Advanced SQL

Why JOINs Exist

The Sample Tables

JOIN Syntax

INNER JOIN

INNER JOIN with WHERE and ORDER BY

Practice: HR employees

LEFT JOIN

Find Unassigned Employees

Practice: Departments with no employees

RIGHT JOIN

FULL OUTER JOIN

Joining Three or More Tables

Self-JOIN

The Cartesian Product Trap

Choosing the Right JOIN

Complete Practice: Departmental salary report