SQL Joins Explained: INNER, OUTER, CROSS & SELF JOIN

In This Chapter

Introduction to Joins
INNER JOIN
OUTER JOIN (LEFT, RIGHT, FULL)
CROSS JOIN
SELF JOIN

We'll use these two tables throughout all five topics:

Setup

CREATE TABLE customers (
    id       INT PRIMARY KEY,
    name     VARCHAR(100),
    city     VARCHAR(50)
);
CREATE TABLE orders (
    id           INT PRIMARY KEY,
    customer_id  INT,
    product      VARCHAR(100),
    amount       DECIMAL(8,2)
);

INSERT INTO customers VALUES
    (1, 'Sara Ahmed',    'Dubai'),
    (2, 'James Wilson',  'London'),
    (3, 'Priya Sharma',  'Mumbai'),
    (4, 'David Chen',    'Singapore');  -- David has NO orders

INSERT INTO orders VALUES
    (101, 1,    'Wireless Mouse',  29.99),
    (102, 1,    'USB-C Hub',       39.99),
    (103, 2,    'SQL Textbook',    45.00),
    (104, 3,    'Desk Lamp',       34.99),
    (105, NULL, 'Mystery Item',   9.99);   -- No customer assigned

Notice the edge cases: David (id=4) has no orders. Order 105 has no customer (NULL). These intentional mismatches will help you understand exactly how each JOIN type behaves.

1 Introduction to Joins

Top

In a relational database, data is split across multiple tables to avoid duplication. Customer info lives in customers, order info in orders. A JOIN combines rows from two or more tables based on a related column between them.

Without JOINs, you'd have to query each table separately and merge the results in your application code. JOINs let the database do this for you — faster, simpler, and in a single query.

The Key Concept: The ON Clause

Every JOIN needs a condition that tells the database how the tables are related. This is the ON clause:

SQL

SELECT customers.name, orders.product, orders.amount
FROM customers
JOIN orders ON customers.id = orders.customer_id;

The ON customers.id = orders.customer_id part says: "Match each customer with their orders by connecting the customer's id to the order's customer_id."

Table Aliases: Keep It Clean

Typing full table names gets tedious. Use aliases:

SQL

-- c = customers, o = orders
SELECT c.name, o.product, o.amount
FROM customers c
JOIN orders o ON c.id = o.customer_id;

Quick Reference: Join Types

Join Type	Returns	Use When
INNER JOIN	Only matching rows from both tables	You only want records that exist in both tables
LEFT JOIN	All rows from left table + matches from right	You want all customers, even those with no orders
RIGHT JOIN	All rows from right table + matches from left	You want all orders, even unassigned ones
FULL OUTER JOIN	All rows from both tables	You want everything, matched or not
CROSS JOIN	Every row from A paired with every row from B	Generate all possible combinations
SELF JOIN	A table joined to itself	Hierarchies (manager/employee), comparisons

Key Takeaways

JOINs combine rows from multiple tables based on a related column
The ON clause defines how the tables are connected
Use table aliases (c, o) to keep queries readable
Different JOIN types control what happens with non-matching rows

2 INNER JOIN

Top

An INNER JOIN returns only rows that have a match in both tables. If a customer has no orders, they're excluded. If an order has no customer, it's excluded. It's the most common JOIN type.

SQL

SELECT c.name, c.city, o.product, o.amount
FROM customers c
INNER JOIN orders o ON c.id = o.customer_id;

Result

name          | city    | product         | amount
--------------|---------|-----------------|------
Sara Ahmed    | Dubai   | Wireless Mouse  | 29.99
Sara Ahmed    | Dubai   | USB-C Hub       | 39.99
James Wilson  | London  | SQL Textbook    | 45.00
Priya Sharma  | Mumbai  | Desk Lamp       | 34.99

Notice: David Chen (no orders) is not in the results. Order 105 (no customer) is not in the results. INNER JOIN only keeps matches.

INNER JOIN with WHERE and ORDER BY

SQL

-- Find orders over $35, sorted by amount
SELECT c.name, o.product, o.amount
FROM customers c
INNER JOIN orders o ON c.id = o.customer_id
WHERE o.amount > 35
ORDER BY o.amount DESC;

Result

name          | product       | amount
--------------|---------------|------
James Wilson  | SQL Textbook  | 45.00
Sara Ahmed    | USB-C Hub     | 39.99

INNER JOIN with GROUP BY

SQL

-- Total spending per customer
SELECT c.name,
       COUNT(*) AS order_count,
       SUM(o.amount) AS total_spent
FROM customers c
INNER JOIN orders o ON c.id = o.customer_id
GROUP BY c.name
ORDER BY total_spent DESC;

Result

name          | order_count | total_spent
--------------|-------------|----------
Sara Ahmed    | 2           | 69.98
James Wilson  | 1           | 45.00
Priya Sharma  | 1           | 34.99

Shorthand: JOIN without a prefix defaults to INNER JOIN. Both JOIN orders ON ... and INNER JOIN orders ON ... do the same thing.

Key Takeaways

INNER JOIN returns only rows with matches in both tables
Unmatched rows from either side are excluded
JOIN = INNER JOIN (same thing)
Combine with WHERE, ORDER BY, GROUP BY just like a regular SELECT

3 OUTER JOIN (LEFT, RIGHT, FULL)

Top

OUTER JOINs keep rows even when there's no match in the other table. The unmatched columns are filled with NULL.

LEFT JOIN

Returns all rows from the left table (customers) and matched rows from the right table (orders). If a customer has no orders, the order columns show NULL.

SQL

SELECT c.name, c.city, o.product, o.amount
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id;

Result

name          | city      | product         | amount
--------------|-----------|-----------------|------
Sara Ahmed    | Dubai     | Wireless Mouse  | 29.99
Sara Ahmed    | Dubai     | USB-C Hub       | 39.99
James Wilson  | London    | SQL Textbook    | 45.00
Priya Sharma  | Mumbai    | Desk Lamp       | 34.99
David Chen    | Singapore | NULL            | NULL     <-- no orders

David Chen appears with NULL for product and amount because he has no orders. This is the key difference from INNER JOIN.

Finding Rows with No Match

LEFT JOIN + WHERE ... IS NULL is a powerful pattern for finding missing data:

SQL

-- Customers who have never placed an order
SELECT c.name, c.city
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
WHERE o.id IS NULL;

Result

name        | city
------------|----------
David Chen  | Singapore

Real-world use cases: Find users who never logged in, products never ordered, employees with no assigned department, students not enrolled in any course. The LEFT JOIN + IS NULL pattern is one of the most useful in SQL.

RIGHT JOIN

The mirror of LEFT JOIN — returns all rows from the right table (orders) and matched rows from the left (customers):

SQL

SELECT c.name, o.product, o.amount
FROM customers c
RIGHT JOIN orders o ON c.id = o.customer_id;

Result

name          | product         | amount
--------------|-----------------|------
Sara Ahmed    | Wireless Mouse  | 29.99
Sara Ahmed    | USB-C Hub       | 39.99
James Wilson  | SQL Textbook    | 45.00
Priya Sharma  | Desk Lamp       | 34.99
NULL          | Mystery Item    | 9.99    <-- no customer assigned

Now the orphan order (105, Mystery Item) appears with NULL for the customer name, but David Chen is excluded because we're keeping all orders, not all customers.

In practice: Most developers just use LEFT JOIN and put the "keep all" table on the left. A RIGHT JOIN can always be rewritten as a LEFT JOIN by swapping the table order. So A RIGHT JOIN B = B LEFT JOIN A.

FULL OUTER JOIN

Returns all rows from both tables, matching where possible and filling NULL where not:

SQL

-- Not supported in MySQL. Supported in PostgreSQL, SQL Server, Oracle.
SELECT c.name, o.product, o.amount
FROM customers c
FULL OUTER JOIN orders o ON c.id = o.customer_id;

Result

name          | product         | amount
--------------|-----------------|------
Sara Ahmed    | Wireless Mouse  | 29.99
Sara Ahmed    | USB-C Hub       | 39.99
James Wilson  | SQL Textbook    | 45.00
Priya Sharma  | Desk Lamp       | 34.99
David Chen    | NULL            | NULL     <-- customer with no orders
NULL          | Mystery Item    | 9.99     <-- order with no customer

MySQL doesn't support FULL OUTER JOIN. You can simulate it with a LEFT JOIN UNION RIGHT JOIN. PostgreSQL, SQL Server, and Oracle support it natively.

Key Takeaways

LEFT JOIN keeps all rows from the left table, fills NULL for non-matches on the right
RIGHT JOIN keeps all rows from the right table — but just use LEFT JOIN and swap tables
FULL OUTER JOIN keeps all rows from both sides (not supported in MySQL)
LEFT JOIN + WHERE right.id IS NULL finds rows with no match — extremely useful

4 CROSS JOIN

Top

A CROSS JOIN produces the Cartesian product — every row from the first table paired with every row from the second table. No ON clause needed (or allowed).

With 4 customers and 5 orders, a CROSS JOIN produces 4 × 5 = 20 rows. This grows fast, so use it carefully.

SQL

-- A more practical example: generate all color/size combinations
CREATE TABLE colors (color VARCHAR(20));
CREATE TABLE sizes  (size  VARCHAR(5));

INSERT INTO colors VALUES ('Red'), ('Blue'), ('Black');
INSERT INTO sizes  VALUES ('S'), ('M'), ('L'), ('XL');

SELECT color, size
FROM colors
CROSS JOIN sizes
ORDER BY color, size;

Result (12 rows = 3 colors × 4 sizes)

color | size
------|-----
Black | L
Black | M
Black | S
Black | XL
Blue  | L
Blue  | M
Blue  | S
Blue  | XL
Red   | L
Red   | M
Red   | S
Red   | XL

Be careful with large tables. CROSS JOIN on two tables of 1,000 rows each produces 1,000,000 rows. On tables of 10,000 rows each, that's 100,000,000 rows. Only use it when you intentionally need all combinations — like generating product variants, calendar grids, or test data.

Key Takeaways

CROSS JOIN pairs every row from table A with every row from table B
No ON clause — it's not based on a relationship
Result size = rows in A × rows in B (grows fast!)
Useful for generating combinations (colors/sizes, dates/products, test data)

5 SELF JOIN

Top

A SELF JOIN is when a table is joined to itself. It's not a special syntax — it's a regular JOIN where both sides are the same table, using different aliases.

Classic Example: Employee / Manager Hierarchy

SQL

CREATE TABLE employees (
    id          INT PRIMARY KEY,
    name        VARCHAR(100),
    manager_id  INT   -- references employees.id
);

INSERT INTO employees VALUES
    (1, 'Alice CEO',      NULL),  -- no manager (top)
    (2, 'Bob VP',         1),     -- reports to Alice
    (3, 'Carol Manager',  2),     -- reports to Bob
    (4, 'Dave Developer', 3),     -- reports to Carol
    (5, 'Eve Designer',  3);     -- reports to Carol

SQL

-- Show each employee and their manager's name
SELECT
    e.name  AS employee,
    m.name  AS manager
FROM      employees e
LEFT JOIN employees m ON e.manager_id = m.id;

Result

employee       | manager
---------------|---------------
Alice CEO      | NULL            <-- top of hierarchy
Bob VP         | Alice CEO
Carol Manager  | Bob VP
Dave Developer | Carol Manager
Eve Designer   | Carol Manager

The trick is using two different aliases for the same table: e for the employee row, m for the manager row. The ON e.manager_id = m.id connects each employee to their manager's record.

Another Use Case: Finding Pairs

SQL

-- Find customers in the same city
SELECT
    a.name AS customer_1,
    b.name AS customer_2,
    a.city
FROM      customers a
JOIN      customers b ON a.city = b.city
WHERE     a.id < b.id;  -- avoid duplicates and self-pairs

The a.id < b.id trick: Without it, you'd get Sara-Sara (self-pair) and both Sara-James and James-Sara (duplicates). The < ensures each pair appears exactly once.

Key Takeaways

A SELF JOIN joins a table to itself using two different aliases
Classic use case: employee/manager hierarchies where manager_id references the same table's id
Use LEFT JOIN for self-joins when the top of the hierarchy has NULL
Use a.id < b.id to avoid duplicate pairs

What's next? You can now combine data from multiple tables. The final chapter of the Beginner course covers SQL Functions — aggregate functions (COUNT, SUM, AVG) and scalar functions (UCASE, ROUND, NOW) for transforming and analyzing your data.

All Chapters in SQL Beginner Course

Continue Learning

Previous ChapterDQL Commands Next Chapter SQL Functions

SQL Joins

In This Chapter

1 Introduction to Joins

The Key Concept: The ON Clause

Table Aliases: Keep It Clean

Quick Reference: Join Types

Key Takeaways

2 INNER JOIN

INNER JOIN with WHERE and ORDER BY

INNER JOIN with GROUP BY

Key Takeaways

3 OUTER JOIN (LEFT, RIGHT, FULL)

LEFT JOIN

Finding Rows with No Match

RIGHT JOIN

FULL OUTER JOIN

Key Takeaways

4 CROSS JOIN

Key Takeaways

5 SELF JOIN

Classic Example: Employee / Manager Hierarchy

Another Use Case: Finding Pairs

Key Takeaways

All Chapters in SQL Beginner Course

Continue Learning

Courses

Services

Support

Connect