Logical joins

Types of Joins

Classic Joins

INNER JOIN returns only rows where there's a match in both tables:

SELECT customers.name, orders.order_date
FROM customers
INNER JOIN orders ON customers.customer_id = orders.customer_id;

-- Or

SELECT customers.name, orders.order_date
FROM customers
JOIN orders ON customers.customer_id = orders.customer_id;
-- Both INNER JOIN and JOIN syntax are functionally the same

The INNER JOIN and JOIN are functionally identical in SQL, because when you write JOIN without specifying the type, it defaults to an INNER JOIN.

Example

1. The Initial Tables

Here is our sample data. Pay close attention to the customer_id column, as that is the bridge connecting these two tables.

Table A: customers

• Note: Charlie (ID 3) has no matching order.

customer_id	name	city
1	Alice	New York
2	Bob	London
3	Charlie	Paris

Table B: orders

• Note: Bob has two orders. Order #104 belongs to ID 5, who doesn't exist in the customers table.

order_id	customer_id	order_date	amount
101	1	2023-01-10	$50.00
102	2	2023-01-12	$25.00
103	2	2023-01-15	$80.00
104	5	2023-02-01	$10.00

2. The Join Logic

The SQL engine looks for matches where customers.customer_id = orders.customer_id.

• Match: ID 1 matches (Alice Order 101).

• Match: ID 2 matches twice (Bob Order 102 AND Order 103).

• Excluded: ID 3 (Charlie) is found in customers but not in orders. He is dropped.

• Excluded: ID 5 is found in orders but not in customers. That row is dropped.

3. The Result Set

Because your query only selected customers.name and orders.order_date, the final output looks like this:

name	order_date
Alice	2023-01-10
Bob	2023-01-12
Bob	2023-01-15

---

LEFT JOIN (LEFT OUTER JOIN) returns all rows from the left table, plus matching rows from the right table (NULL for non-matches):

SELECT customers.name, orders.order_date
FROM customers
LEFT JOIN orders ON customers.customer_id = orders.customer_id;

Based on the example SQL code above:

The "Left" table is the first one listed (customers). A LEFT JOIN grabs every single row from the Left table. It then tries to find matches in the Right table (orders). If it finds no match, it still keeps the Left row but fills the Right columns with NULL.

The Result:

name	order_date	Note
Alice	2023-01-10	Matched normally.
Bob	2023-01-12	Matched normally.
Bob	2023-01-15	Matched normally.
Charlie	NULL	Charlie is kept (Left table), but he has no order, so the date is NULL.

• What happened: We kept Charlie (the customer with no orders), but the orphan order #104 (which is in the Right table) was ignor

---

RIGHT JOIN does the opposite - all rows from the right table:

SELECT customers.name, orders.order_date
FROM customers
RIGHT JOIN orders ON customers.customer_id = orders.customer_id;

Concept:

This is the mirror image of the Left Join. The RIGHT JOIN prioritizes the Right table (orders). It grabs every single order. It tries to find a matching customer name. If the order has a customer_id that doesn't exist in the customers table, it keeps the order and marks the name as NULL.

The Result:

name	order_date	Note
Alice	2023-01-10	Matched normally.
Bob	2023-01-12	Matched normally.
Bob	2023-01-15	Matched normally.
NULL	2023-02-01	Order #104 is kept (Right table), but the customer ID (5) doesn't exist.

• What happened: We discovered an "orphan" order. Charlie was completely dropped because he is only in the Left table and has no matching order in the Right table.

---

FULL OUTER JOIN returns all rows from both tables, with NULLs where there's no match:

SELECT customers.name, orders.order_date
FROM customers
FULL OUTER JOIN orders ON customers.customer_id = orders.customer_id;

Concept:

The FULL OUTER JOIN is the combination of both. It says: "Give me everything." It returns all matching records, plus all unmatched records from the Left table, plus all unmatched records from the Right table.

The Result:

name	order_date	Note
Alice	2023-01-10	Match found.
Bob	2023-01-12	Match found.
Bob	2023-01-15	Match found.
Charlie	NULL	From Left table (No order).
NULL	2023-02-01	From Right table (No customer).

• What happened: Nothing was filtered out. We see the active customers, the inactive customer (Charlie), and the data error (the orphan order).

---

Cross Join

A CROSS JOIN is a SQL operation that combines every row from the first table with every row from the second table, producing a Cartesian product of the two tables. Cross joins are useful when you need to generate all possible combinations of rows from two tables.

Key characteristics:

• No join condition required - Unlike other joins (INNER, LEFT, RIGHT), a CROSS JOIN doesn't use an ON or USING clause to match rows

• Result size - If the first table has M rows and the second table has N rows, the result will contain M × N rows

• All combinations - Every possible pairing between rows from both tables appears in the result set

Here's an example of a CROSS JOIN in standard SQL:

-- Sample tables
CREATE TABLE sizes (
    size_id INT PRIMARY KEY,
    size_name VARCHAR(10)
);

CREATE TABLE colors (
    color_id INT PRIMARY KEY,
    color_name VARCHAR(20)
);

-- Insert sample data
INSERT INTO sizes (size_id, size_name) VALUES
(1, 'Small'),
(2, 'Medium'),
(3, 'Large');

INSERT INTO colors (color_id, color_name) VALUES
(1, 'Red'),
(2, 'Blue'),
(3, 'Green');

-- CROSS JOIN example
-- Generate all possible product variations (sizes × colors)
SELECT 
    s.size_name,
    c.color_name
FROM sizes s
CROSS JOIN colors c;

Result:

size_name	color_name
Small	Red
Small	Blue
Small	Green
Medium	Red
Medium	Blue
Medium	Green
Large	Red
Large	Blue
Large	Green

What's happening: A CROSS JOIN produces a Cartesian product - every row from the first table is combined with every row from the second table. With 3 sizes and 3 colors, you get 3 × 3 = 9 combinations.

Common use cases:

• Generating all possible combinations of product variants (like the example above)

• Creating a complete calendar or time series by crossing dates with other dimensions

• Generating test data

• Creating reporting templates with all possible dimension combinations

When to avoid:

CROSS JOINs can produce extremely large result sets and should be used cautiously. Accidentally omitting a join condition in a multi-table query can result in an unintended CROSS JOIN, leading to performance issues and incorrect results.

Alternative syntax:

-- These are equivalent
SELECT * FROM sizes, colors;  -- Implicit cross join
SELECT * FROM sizes CROSS JOIN colors;  -- Explicit cross join

The explicit CROSS JOIN syntax is preferred for clarity.

---

Lateral Join

The LATERAL operator allows a nested query to reference attributes in other nested queries that precede it. You can think of lateral joins like a for loop that allows you to invoke another query for each tuple in a table.

Example: Calculate the number of students enrolled in each course and the average GPA. Sort by enrollment count in descending order..

Once we have gotten the course records, we can think of this query like below. For each course:

• Compute the number of enrolled students in this course

• Compute the average GPA of the enrolled students in this course

SELECT * FROM course AS c
LATERAL (SELECT COUNT(*) AS cnt FROM enrolled
WHERE enrolled.cid = c.cid) AS t1,
LATERAL (SELECT AVG(gpa) AS avg FROM student AS s
JOIN enrolled AS e ON s.sid = e.sid
WHERE e.cid = c.cid) AS t2;

Lateral join is more advanced and allows the right-side table expression to reference columns from the left side. It's like a correlated subquery but more powerful:

SELECT customers.name, top_orders.order_date, top_orders.total
FROM customers
LEFT JOIN LATERAL (
    SELECT order_date, total
    FROM orders
    WHERE orders.customer_id = customers.customer_id
    ORDER BY total DESC
    LIMIT 3
) AS top_orders ON true;

This example gets each customer along with their top 3 highest-value orders. The LATERAL keyword allows the subquery to reference customers.customer_id from the left side.

LATERAL joins are particularly useful for:

• Getting top N records per group

• Running functions that need input from the left table

• Complex aggregations that depend on left-side values

Note that LATERAL join support varies by database system - PostgreSQL has full support, while MySQL and SQL Server use different syntax (APPLY in SQL Server).

The key distinction is that regular joins work with static relationships, while LATERAL joins allow dynamic, row-by-row evaluation where the right side can change based on each row from the left side.

---