Joins and Relationships

Learn how to combine data from multiple tables using SQL joins.

Setup Sample Tables

CREATE TABLE departments (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL
);

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    department_id INTEGER,
    salary REAL,
    FOREIGN KEY (department_id) REFERENCES departments(id)
);

-- Insert sample data
INSERT INTO departments (id, name) VALUES
(1, 'Engineering'),
(2, 'Marketing'),
(3, 'Sales');

INSERT INTO employees (name, department_id, salary) VALUES
('Alice', 1, 75000),
('Bob', 1, 80000),
('Charlie', 2, 65000),
('Diana', 3, 70000),
('Eve', NULL, 50000);

INNER JOIN

Returns rows when there is at least one match in both tables.

-- Employees with their departments
SELECT 
    employees.name, 
    departments.name as department,
    salary
FROM employees
INNER JOIN departments ON employees.department_id = departments.id;

LEFT JOIN

Returns all rows from the left table, even if there are no matches in the right table.

-- All employees, even those without departments
SELECT 
    employees.name, 
    departments.name as department,
    salary
FROM employees
LEFT JOIN departments ON employees.department_id = departments.id;

RIGHT JOIN

Returns all rows from the right table, even if there are no matches in the left table.

-- All departments, even those without employees
SELECT 
    employees.name, 
    departments.name as department,
    salary
FROM employees
RIGHT JOIN departments ON employees.department_id = departments.id;

FULL OUTER JOIN

Returns rows when there is a match in either the left or right table.

-- All employees and all departments
SELECT 
    employees.name, 
    departments.name as department,
    salary
FROM employees
FULL OUTER JOIN departments ON employees.department_id = departments.id;

Self Join

A table can be joined to itself when you need to compare rows within the same table.

-- Find pairs of employees in the same department
SELECT 
    e1.name as employee1,
    e2.name as employee2,
    e1.department_id
FROM employees e1
JOIN employees e2 ON e1.department_id = e2.department_id
WHERE e1.id < e2.id;

Cross Join

Creates the Cartesian product of two tables.

-- All possible combinations of employees and departments
SELECT 
    employees.name,
    departments.name as department
FROM employees
CROSS JOIN departments;

Next Steps

Optimize your database performance with Indexing and Performance techniques.

Last updated on January 4, 2026

Advanced Queries Indexing and Performance