SQL Joins - Part 1 — SQL Tutorial

Understanding Table Relationships

In a relational database, data is spread across multiple tables that are connected through relationships. JOINs allow you to combine rows from two or more tables based on a related column between them.

The most common relationship is when one table has a foreign key that references the primary key of another table. For example, an orders table might have a user_id column that references the id column in the users table.

Primary Key: A unique identifier for each row in a table
Foreign Key: A column that references the primary key of another table
One-to-Many: One user can have many orders
Many-to-Many: Users and products connected through an orders table

Example

-- Let's create related tables for our examples
CREATE TABLE customers (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE
);

CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    customer_id INT,
    product VARCHAR(100),
    amount DECIMAL(10, 2),
    order_date DATE,
    FOREIGN KEY (customer_id) REFERENCES customers(id)
);

-- Insert sample data
INSERT INTO customers (name, email) VALUES
    ('Alice', 'alice@example.com'),
    ('Bob', 'bob@example.com'),
    ('Charlie', 'charlie@example.com'),
    ('Diana', 'diana@example.com');

INSERT INTO orders (customer_id, product, amount, order_date) VALUES
    (1, 'Laptop', 999.99, '2026-01-15'),
    (1, 'Mouse', 29.99, '2026-02-10'),
    (2, 'Keyboard', 79.99, '2026-01-20'),
    (NULL, 'USB Cable', 9.99, '2026-03-01');

Notes

The FOREIGN KEY constraint ensures referential integrity — you cannot insert an order with a customer_id that does not exist in the customers table.

INNER JOIN

An INNER JOIN returns only the rows that have matching values in both tables. If a customer has no orders, they will not appear in the result. If an order has no matching customer, it will also be excluded.

INNER JOIN is the most commonly used type of join. When you simply write JOIN without specifying a type, SQL defaults to INNER JOIN.

Example

-- INNER JOIN: Get orders with customer details
SELECT 
    customers.name,
    orders.product,
    orders.amount,
    orders.order_date
FROM customers
INNER JOIN orders ON customers.id = orders.customer_id;

-- Using table aliases for cleaner syntax
SELECT 
    c.name AS customer,
    o.product,
    o.amount
FROM customers c
INNER JOIN orders o ON c.id = o.customer_id;

-- Add a WHERE clause to filter joined results
SELECT c.name, o.product, o.amount
FROM customers c
INNER JOIN orders o ON c.id = o.customer_id
WHERE o.amount > 50;

Notes

Notice that Charlie and Diana do not appear in the results because they have no orders. The order for 'USB Cable' also does not appear because it has no matching customer (customer_id is NULL).

LEFT JOIN

A LEFT JOIN (or LEFT OUTER JOIN) returns all rows from the left table and the matching rows from the right table. If there is no match in the right table, the result will contain NULL values for the right table's columns.

LEFT JOIN is useful when you want to include all records from the main table even if they do not have corresponding entries in the related table.

Example

-- LEFT JOIN: All customers, even those without orders
SELECT 
    c.name AS customer,
    o.product,
    o.amount
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id;

-- Find customers who have NOT placed any orders
SELECT c.name
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
WHERE o.id IS NULL;

-- Count orders per customer (including those with 0 orders)
SELECT 
    c.name,
    COUNT(o.id) AS order_count
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
GROUP BY c.name;

Notes

The trick of LEFT JOIN + WHERE right_table.id IS NULL is a powerful pattern for finding records in one table that have no matching records in another.