Master complex JOIN types and set operations in SQL to solve advanced data analysis problems and extract insights from multiple related datasets.
Master complex JOIN types and set operations in SQL to solve advanced data analysis problems and extract insights from multiple related datasets.
Before diving into advanced JOIN techniques, let's briefly review the basic JOIN types you're likely already
familiar with and understand their limitations in complex scenarios.
Basic JOIN Types:
INNER JOIN
: Returns only matching rows from both tables.
Limitation:
Excludes records that don't have matches, which may hide important data gaps.
: Returns all rows from the left table and matching rows from the right table.
Limitation:
Asymmetric; doesn't show rows from the right table with no match in the left table.
RIGHT JOIN
: Returns all rows from the right table and matching rows from the left table.
Limitation:
Also asymmetric; essentially the mirror image of a LEFT JOIN.
FULL OUTER JOIN
: Returns all rows from both tables, matching where possible.
Limitation:
While comprehensive, it can be difficult to work with when multiple tables are involved.
These basic JOIN operations work well for many scenarios, but they have limitations when dealing with:
Complex many-to-many relationships
Hierarchical or self-referential data
Cases where you need to join based on the result of a subquery
Situations where you need to correlate rows within the same table
Set-based operations like finding the intersection or difference between result sets
In this lesson, we'll explore advanced JOIN techniques and set operations that address these limitations,
allowing you to solve complex data analysis problems more elegantly.
Throughout this lesson, we'll use the following sample data tables to demonstrate advanced JOIN techniques:
1-- Sample Customers table
2CREATE TABLE customers (
3 customer_id INT PRIMARY KEY,
4 name VARCHAR(100),
5 email VARCHAR(100),
6 city VARCHAR(100),
7 signup_date DATE
8);
9
10INSERT INTO customers VALUES
11(1, 'John Smith', '[email protected]', 'New York', '2022-01-15'),
12(2, 'Mary Johnson', '[email protected]', 'Los Angeles', '2022-02-20'),
13(3, 'Robert Brown', '[email protected]', 'Chicago', '2022-03-10'),
14(4, 'Patricia Davis', '[email protected]', 'Houston', '2022-04-05'),
15(5, 'Michael Wilson', '[email protected]', 'Phoenix', '2022-05-12');
16
17-- Sample Orders table
18CREATE TABLE orders (
19 order_id INT PRIMARY KEY,
20 customer_id INT,
21 order_date DATE,
22 total_amount DECIMAL(10,2),
23 status VARCHAR(20),
24 FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
25);
26
27INSERT INTO orders VALUES
28(101, 1, '2022-02-10', 150.75, 'Completed'),
29(102, 2, '2022-03-15', 89.99, 'Completed'),
30(103, 1, '2022-04-20', 245.50, 'Completed'),
31(104, 3, '2022-05-05', 55.25, 'Completed'),
32(105, 2, '2022-06-15', 199.99, 'Completed'),
33(106, 1, '2022-07-01', 175.25, 'Pending'),
34(107, 4, '2022-07-18', 120.80, 'Completed'),
35(108, 5, '2022-08-05', 85.75, 'Pending');
36
37-- Sample Products table
38CREATE TABLE products (
39 product_id INT PRIMARY KEY,
40 name VARCHAR(100),
41 category VARCHAR(50),
42 price DECIMAL(10,2),
43 stock_quantity INT
44);
45
46INSERT INTO products VALUES
47(1, 'Laptop', 'Electronics', 899.99, 45),
48(2, 'Smartphone', 'Electronics', 699.99, 120),
49(3, 'Headphones', 'Electronics', 149.99, 80),
50(4, 'Coffee Maker', 'Appliances', 79.99, 30),
51(5, 'Desk Chair', 'Furniture', 199.99, 15),
52(6, 'Desk Lamp', 'Furniture', 45.99, 25),
53(7, 'Tablet', 'Electronics', 349.99, 60),
54(8, 'Monitor', 'Electronics', 249.99, 40);
55
56-- Sample Order Items table (for order line items)
57CREATE TABLE order_items (
58 item_id INT PRIMARY KEY,
59 order_id INT,
60 product_id INT,
61 quantity INT,
62 price_each DECIMAL(10,2),
63 FOREIGN KEY (order_id) REFERENCES orders(order_id),
64 FOREIGN KEY (product_id) REFERENCES products(product_id)
65);
66
67INSERT INTO order_items VALUES
68(1, 101, 1, 1, 899.99),
69(2, 101, 3, 1, 149.99),
70(3, 102, 2, 1, 699.99),
71(4, 103, 5, 1, 199.99),
72(5, 103, 6, 1, 45.99),
73(6, 104, 4, 1, 79.99),
74(7, 105, 7, 1, 349.99),
75(8, 106, 8, 1, 249.99),
76(9, 107, 3, 1, 149.99),
77(10, 108, 2, 1, 699.99);
78
79-- Sample Warehouses table
80CREATE TABLE warehouses (
81 warehouse_id INT PRIMARY KEY,
82 name VARCHAR(100),
83 city VARCHAR(100),
84 capacity INT
85);
86
87INSERT INTO warehouses VALUES
88(1, 'North Distribution Center', 'New York', 5000),
89(2, 'West Distribution Center', 'Los Angeles', 7000),
90(3, 'Central Distribution Center', 'Chicago', 4500),
91(4, 'South Distribution Center', 'Houston', 6000),
92(5, 'East Distribution Center', 'Atlanta', 5500);
93
94-- Sample Inventory table
95CREATE TABLE inventory (
96 inventory_id INT PRIMARY KEY,
97 warehouse_id INT,
98 product_id INT,
99 quantity INT,
100 last_updated DATE,
101 FOREIGN KEY (warehouse_id) REFERENCES warehouses(warehouse_id),
102 FOREIGN KEY (product_id) REFERENCES products(product_id)
103);
104
105INSERT INTO inventory VALUES
106(1, 1, 1, 10, '2023-01-15'),
107(2, 1, 2, 25, '2023-01-15'),
108(3, 2, 1, 15, '2023-01-20'),
109(4, 2, 3, 20, '2023-01-20'),
110(5, 3, 4, 30, '2023-01-25'),
111(6, 3, 5, 15, '2023-01-25'),
112(7, 4, 6, 25, '2023-02-01'),
113(8, 4, 7, 10, '2023-02-01'),
114(9, 5, 8, 20, '2023-02-05'),
115(10, 5, 2, 30, '2023-02-05');
116
117-- Sample Promotions table
118CREATE TABLE promotions (
119 promotion_id INT PRIMARY KEY,
120 name VARCHAR(100),
121 start_date DATE,
122 end_date DATE,
123 discount_percent DECIMAL(5,2)
124);
125
126INSERT INTO promotions VALUES
127(1, 'New Year Sale', '2023-01-01', '2023-01-15', 15.00),
128(2, 'Spring Special', '2023-03-01', '2023-03-15', 10.00),
129(3, 'Summer Clearance', '2023-06-01', '2023-06-30', 20.00),
130(4, 'Back to School', '2023-08-15', '2023-09-05', 12.50),
131(5, 'Holiday Season', '2023-12-01', '2023-12-31', 25.00);
132
133-- Sample Promotion Products table (many-to-many relationship)
134CREATE TABLE promotion_products (
135 promotion_id INT,
136 product_id INT,
137 PRIMARY KEY (promotion_id, product_id),
138 FOREIGN KEY (promotion_id) REFERENCES promotions(promotion_id),
139 FOREIGN KEY (product_id) REFERENCES products(product_id)
140);
141
142INSERT INTO promotion_products VALUES
143(1, 1), (1, 2), (1, 3), -- New Year Sale applies to these products
144(2, 4), (2, 6), -- Spring Special applies to these products
145(3, 1), (3, 2), (3, 7), -- Summer Clearance applies to these products
146(4, 1), (4, 7), (4, 8), -- Back to School applies to these products
147(5, 1), (5, 2), (5, 3), (5, 7), (5, 8); -- Holiday Season applies to these productsNow let's explore advanced JOIN types and techniques that extend beyond the basic JOIN operations.
These techniques are powerful tools for solving complex data problems.
A CROSS JOIN returns the Cartesian product of two tables—every row from the first table combined with every row
from the second table. While often avoided due to the large result sets it can produce, CROSS JOINs have
legitimate use cases.
⚠️ Caution:
CROSS JOINs can produce very large result sets. If you have tables with 1,000 rows each,
a CROSS JOIN will produce 1,000,000 rows. Always be careful with this operation.
Practical Use Cases for CROSS JOIN:
Generate combinations
Generate date ranges
1-- Generate all possible size and color combinations
2WITH sizes AS (
3 SELECT 'Small' AS size
4 UNION SELECT 'Medium'
5 UNION SELECT 'Large'
6 UNION SELECT 'X-Large'
7),
8colors AS (
9 SELECT 'Red' AS color
10 UNION SELECT 'Blue'
11 UNION SELECT 'Black'
12 UNION SELECT 'White'
13 UNION SELECT 'Green'
14)
15SELECT
16 s.size,
17 c.color
18FROM sizes s
19CROSS JOIN colors c
20ORDER BY s.size, c.color;1-- Generate a calendar for the current year
2WITH months AS (
3 SELECT generate_series(1, 12) AS month
4),
5days AS (
6 SELECT generate_series(1, 31) AS day
7)
8SELECT
9 EXTRACT(YEAR FROM CURRENT_DATE) AS year,
10 m.month,
11 d.day
12FROM months m
13CROSS JOIN days d
14WHERE
15 -- Filter out invalid dates like February 30
16 (d.day <= 28) OR
17 (d.day = 29 AND m.month = 2 AND EXTRACT(YEAR FROM CURRENT_DATE) % 4 = 0) OR
18 (d.day = 30 AND m.month != 2) OR
19 (d.day = 31 AND m.month IN (1, 3, 5, 7, 8, 10, 12))
20ORDER BY m.month, d.day;LATERAL joins (also known as LATERAL subqueries) are one of the most powerful advanced join techniques.
They allow you to reference columns from preceding tables in the FROM clause, enabling correlated subqueries
to be used in the FROM clause.
In other words, LATERAL allows each row from the left-hand side to be evaluated against a subquery on the right-hand side,
with the subquery being able to reference columns from the left-hand side. This is extremely useful for row-by-row processing.
Key Points About LATERAL:
The subquery runs once for each row from the preceding tables
Can reference columns from tables that appear to its left in the FROM clause
Available in PostgreSQL, Oracle (12c+), SQL Server (2016+, as CROSS/OUTER APPLY), and other modern DBMSs
Perfect for "for each row, find the top/latest N related rows"
Use Case 1: Finding the latest orders for each customer
The LATERAL join above finds the most recent order for each customer. Without LATERAL, you would need a more complex
solution involving window functions or a correlated subquery in the SELECT clause.
Use Case 2: Top N items per order
This query finds the most expensive item in each order, showing the order details alongside the item information.
Use Case 3: Advanced Reporting with Multiple LATERAL JOINs
This more complex example creates a comprehensive customer report by using multiple LATERAL JOINs
to calculate different metrics for each customer in a single query.
1-- Find the latest order for each customer
2SELECT
3 c.customer_id,
4 c.name,
5 o.order_id,
6 o.order_date,
7 o.total_amount
8FROM customers c
9CROSS JOIN LATERAL (
10 SELECT
11 order_id,
12 order_date,
13 total_amount
14 FROM orders
15 WHERE orders.customer_id = c.customer_id
16 ORDER BY order_date DESC
17 LIMIT 1
18) o;
19
20-- Alternative syntax in PostgreSQL
21SELECT
22 c.customer_id,
23 c.name,
24 o.order_id,
25 o.order_date,
26 o.total_amount
27FROM customers c,
28LATERAL (
29 SELECT
30 order_id,
31 order_date,
32 total_amount
33 FROM orders
34 WHERE orders.customer_id = c.customer_id
35 ORDER BY order_date DESC
36 LIMIT 1
37) o;1-- Find the highest-priced item in each order
2SELECT
3 o.order_id,
4 o.order_date,
5 c.name AS customer_name,
6 i.item_id,
7 p.name AS product_name,
8 i.price_each
9FROM orders o
10JOIN customers c ON o.customer_id = c.customer_id
11CROSS JOIN LATERAL (
12 SELECT
13 oi.item_id,
14 oi.product_id,
15 oi.price_each
16 FROM order_items oi
17 WHERE oi.order_id = o.order_id
18 ORDER BY oi.price_each DESC
19 LIMIT 1
20) i
21JOIN products p ON i.product_id = p.product_id
22ORDER BY o.order_date DESC;1-- Comprehensive customer report with latest order and spending metrics
2SELECT
3 c.customer_id,
4 c.name,
5 c.email,
6 c.city,
7 latest_order.order_id,
8 latest_order.order_date,
9 latest_order.total_amount,
10 order_count.count AS total_orders,
11 COALESCE(total_spend.sum, 0) AS total_spent,
12 CASE
13 WHEN COALESCE(total_spend.sum, 0) > 500 THEN 'Premium'
14 WHEN COALESCE(total_spend.sum, 0) > 200 THEN 'Regular'
15 ELSE 'New'
16 END AS customer_tier
17FROM customers c
18LEFT JOIN LATERAL (
19 -- Latest order
20 SELECT
21 order_id,
22 order_date,
23 total_amount
24 FROM orders
25 WHERE orders.customer_id = c.customer_id
26 ORDER BY order_date DESC
27 LIMIT 1
28) latest_order ON true
29LEFT JOIN LATERAL (
30 -- Order count
31 SELECT COUNT(*) AS count
32 FROM orders
33 WHERE orders.customer_id = c.customer_id
34) order_count ON true
35LEFT JOIN LATERAL (
36 -- Total spending
37 SELECT SUM(total_amount) AS sum
38 FROM orders
39 WHERE orders.customer_id = c.customer_id
40) total_spend ON true
41ORDER BY total_spent DESC;FULL OUTER JOIN returns all rows from both tables, with matching rows combined and NULL values for
non-matching rows. When combined with COALESCE, it becomes a powerful tool for data comparison and reconciliation.
Example: Inventory Reconciliation Across Warehouses
This query reconciles inventory between two warehouses, showing items available in only one location or both,
and calculating the combined inventory.
1-- Compare inventory levels between NYC and LA warehouses
2SELECT
3 COALESCE(nyc.product_id, la.product_id) AS product_id,
4 p.name AS product_name,
5 nyc.quantity AS nyc_quantity,
6 la.quantity AS la_quantity,
7 COALESCE(nyc.quantity, 0) + COALESCE(la.quantity, 0) AS total_quantity,
8 CASE
9 WHEN nyc.quantity IS NULL THEN 'LA Only'
10 WHEN la.quantity IS NULL THEN 'NYC Only'
11 ELSE 'Both Locations'
12 END AS availability
13FROM (
14 -- NYC Inventory
15 SELECT product_id, quantity
16 FROM inventory
17 WHERE warehouse_id = 1 -- New York warehouse
18) nyc
19FULL OUTER JOIN (
20 -- LA Inventory
21 SELECT product_id, quantity
22 FROM inventory
23 WHERE warehouse_id = 2 -- Los Angeles warehouse
24) la ON nyc.product_id = la.product_id
25JOIN products p ON COALESCE(nyc.product_id, la.product_id) = p.product_id
26ORDER BY availability, product_name;Self-joins are particularly useful for working with hierarchical data stored in a single table.
Let's create a quick example table for this:
This SELF JOIN allows us to connect employees with their managers, both stored in the same table.
1-- Sample Employees table with reporting structure
2CREATE TABLE employees (
3 employee_id INT PRIMARY KEY,
4 name VARCHAR(100),
5 title VARCHAR(100),
6 department VARCHAR(50),
7 manager_id INT,
8 FOREIGN KEY (manager_id) REFERENCES employees(employee_id)
9);
10
11INSERT INTO employees VALUES
12(1, 'John Smith', 'CEO', 'Executive', NULL),
13(2, 'Sarah Johnson', 'CTO', 'Technology', 1),
14(3, 'Michael Williams', 'CFO', 'Finance', 1),
15(4, 'James Brown', 'Engineering Director', 'Engineering', 2),
16(5, 'Patricia Davis', 'Marketing Director', 'Marketing', 1),
17(6, 'Robert Miller', 'Senior Developer', 'Engineering', 4),
18(7, 'Linda Wilson', 'Developer', 'Engineering', 6),
19(8, 'David Moore', 'Developer', 'Engineering', 6),
20(9, 'Elizabeth Taylor', 'Finance Manager', 'Finance', 3);
21
22-- Query showing employees with their managers
23SELECT
24 e.employee_id,
25 e.name AS employee_name,
26 e.title AS employee_title,
27 e.department,
28 m.employee_id AS manager_id,
29 m.name AS manager_name,
30 m.title AS manager_title
31FROM employees e
32LEFT JOIN employees m ON e.manager_id = m.employee_id
33ORDER BY
34 CASE WHEN e.manager_id IS NULL THEN 0 ELSE 1 END,
35 e.manager_id,
36 e.employee_id;NATURAL JOIN automatically joins tables based on columns with the same name. The USING clause is similar but
explicitly specifies which common columns to join on.
⚠️ Caution with NATURAL JOIN:
While convenient, NATURAL JOIN can be dangerous as it implicitly joins on ALL columns with the same name.
If table structures change (e.g., adding a new column with the same name), the join behavior will change.
USING is generally safer as it's explicit about which columns to join on.
Example: USING Clause
The USING clause simplifies joins when the joining columns have the same name. Unlike a regular join with ON,
the shared column appears only once in the result.
1-- Join with the USING clause
2SELECT
3 o.order_id,
4 customer_id, -- Note: shared columns appear only once with USING
5 o.order_date,
6 o.total_amount,
7 c.name AS customer_name
8FROM orders o
9JOIN customers c USING (customer_id)
10ORDER BY o.order_date DESC;Set operations in SQL allow you to combine or compare the results of two or more queries.
They treat query results as sets of rows and perform mathematical set operations on them.
Key Set Operations:
: Combines the results of two queries and removes duplicates
: Combines the results of two queries including duplicates (faster than UNION)
: Returns only rows that appear in both query results
in Oracle): Returns rows from the first query that don't appear in the second query
For set operations to work, the queries being combined must:
Have the same number of columns
Have compatible data types in corresponding columns
Return columns in the same order
UNION combines the results of two queries and removes duplicates, while UNION ALL includes duplicates.
UNION is useful for combining similar data from different tables or queries. UNION ALL is more efficient
when you know there are no duplicates or when you want to keep duplicates.
1-- Find all cities where we have either customers or warehouses
2SELECT
3 city,
4 'Customer Location' AS type
5FROM customers
6
7UNION
8
9SELECT
10 city,
11 'Warehouse Location' AS type
12FROM warehouses
13ORDER BY city;
14
15-- Find all product IDs that appear in either New Year or Summer promotions
16SELECT
17 product_id,
18 'New Year' AS promotion
19FROM promotion_products
20WHERE promotion_id = 1
21
22UNION ALL
23
24SELECT
25 product_id,
26 'Summer' AS promotion
27FROM promotion_products
28WHERE promotion_id = 3
29ORDER BY product_id;INTERSECT returns only the rows that appear in both query results.
INTERSECT is useful for finding common elements between different data sets, such as products
that appear in multiple promotions or locations that serve multiple purposes.
1-- Find products that are in both New Year and Holiday Season promotions
2SELECT product_id
3FROM promotion_products
4WHERE promotion_id = 1 -- New Year Sale
5
6INTERSECT
7
8SELECT product_id
9FROM promotion_products
10WHERE promotion_id = 5; -- Holiday Season
11
12-- Find cities that have both customers and warehouses
13SELECT city
14FROM customers
15
16INTERSECT
17
18SELECT city
19FROM warehouses;EXCEPT (called MINUS in Oracle) returns rows from the first query that don't appear in the second query.
EXCEPT is useful for finding differences between data sets, such as identifying gaps in coverage or
exclusive memberships.
1-- Find cities with customers but no warehouses
2SELECT city
3FROM customers
4
5EXCEPT
6
7SELECT city
8FROM warehouses;
9
10-- Find products that are in the New Year promotion but not in the Summer promotion
11SELECT product_id
12FROM promotion_products
13WHERE promotion_id = 1 -- New Year Sale
14
15EXCEPT
16
17SELECT product_id
18FROM promotion_products
19WHERE promotion_id = 3; -- Summer ClearanceSet operations can be combined to create more complex queries:
This complex example finds products that appear in exactly one of the three specified promotions,
demonstrating how set operations can be combined to perform sophisticated comparisons.
1-- Find products that are in exactly one of these promotions: New Year, Summer, Holiday
2-- (in one but not in either of the other two)
3
4-- First, get all products in any of the three promotions
5(SELECT product_id FROM promotion_products WHERE promotion_id IN (1, 3, 5))
6
7EXCEPT
8
9-- Then exclude products that are in at least two of these promotions
10(
11 -- Products in New Year AND Summer
12 (SELECT product_id FROM promotion_products WHERE promotion_id = 1
13 INTERSECT
14 SELECT product_id FROM promotion_products WHERE promotion_id = 3)
15
16 UNION
17
18 -- Products in New Year AND Holiday
19 (SELECT product_id FROM promotion_products WHERE promotion_id = 1
20 INTERSECT
21 SELECT product_id FROM promotion_products WHERE promotion_id = 5)
22
23 UNION
24
25 -- Products in Summer AND Holiday
26 (SELECT product_id FROM promotion_products WHERE promotion_id = 3
27 INTERSECT
28 SELECT product_id FROM promotion_products WHERE promotion_id = 5)
29);Combining CTEs with set operations creates powerful, readable queries for complex analyses:
This comprehensive example combines CTEs and set operations to analyze product availability
across different warehouses, creating a detailed inventory report.
1-- Analyze product availability across warehouses
2WITH
3all_products AS (
4 SELECT product_id, name
5 FROM products
6),
7nyc_inventory AS (
8 SELECT product_id
9 FROM inventory
10 WHERE warehouse_id = 1 -- New York warehouse
11),
12la_inventory AS (
13 SELECT product_id
14 FROM inventory
15 WHERE warehouse_id = 2 -- Los Angeles warehouse
16),
17chicago_inventory AS (
18 SELECT product_id
19 FROM inventory
20 WHERE warehouse_id = 3 -- Chicago warehouse
21),
22in_all_locations AS (
23 SELECT product_id FROM nyc_inventory
24 INTERSECT
25 SELECT product_id FROM la_inventory
26 INTERSECT
27 SELECT product_id FROM chicago_inventory
28),
29in_none AS (
30 SELECT product_id FROM all_products
31 EXCEPT
32 (
33 SELECT product_id FROM nyc_inventory
34 UNION
35 SELECT product_id FROM la_inventory
36 UNION
37 SELECT product_id FROM chicago_inventory
38 )
39)
40-- Final query combines all analyses
41SELECT
42 p.product_id,
43 p.name,
44 CASE
45 WHEN ia.product_id IS NOT NULL THEN 'All Warehouses'
46 WHEN in.product_id IS NOT NULL THEN 'Not Stocked'
47 WHEN n.product_id IS NOT NULL AND l.product_id IS NULL AND c.product_id IS NULL THEN 'NYC Only'
48 WHEN n.product_id IS NULL AND l.product_id IS NOT NULL AND c.product_id IS NULL THEN 'LA Only'
49 WHEN n.product_id IS NULL AND l.product_id IS NULL AND c.product_id IS NOT NULL THEN 'Chicago Only'
50 ELSE 'Multiple Locations'
51 END AS availability
52FROM all_products p
53LEFT JOIN in_all_locations ia ON p.product_id = ia.product_id
54LEFT JOIN in_none in ON p.product_id = in.product_id
55LEFT JOIN nyc_inventory n ON p.product_id = n.product_id
56LEFT JOIN la_inventory l ON p.product_id = l.product_id
57LEFT JOIN chicago_inventory c ON p.product_id = c.product_id
58ORDER BY
59 CASE
60 WHEN ia.product_id IS NOT NULL THEN 1
61 WHEN in.product_id IS NOT NULL THEN 5
62 ELSE 3
63 END,
64 p.name;Now let's explore some real-world applications of advanced joins and set operations to solve common data problems.
This example identifies active customers, dormant customers, and products they might be interested in,
using a combination of advanced joins and set operations:
This advanced analysis combines LATERAL joins, set operations, and aggregation to identify dormant customers
and recommend products based on the purchasing patterns of similar active customers.
1-- Comprehensive customer purchasing analysis
2WITH
3-- Active customers who purchased in the last 3 months
4active_customers AS (
5 SELECT DISTINCT c.customer_id, c.name
6 FROM customers c
7 JOIN orders o ON c.customer_id = o.customer_id
8 WHERE o.order_date > CURRENT_DATE - INTERVAL '3 months'
9),
10-- Dormant customers who haven't purchased in the last 3 months
11dormant_customers AS (
12 SELECT c.customer_id, c.name
13 FROM customers c
14 EXCEPT
15 SELECT customer_id, name
16 FROM active_customers
17),
18-- Products each customer has purchased
19customer_products AS (
20 SELECT DISTINCT
21 c.customer_id,
22 oi.product_id
23 FROM customers c
24 JOIN orders o ON c.customer_id = o.customer_id
25 JOIN order_items oi ON o.order_id = oi.order_id
26),
27-- Recommendations based on products purchased by similar customers
28recommendations AS (
29 SELECT
30 dc.customer_id AS dormant_customer_id,
31 p.product_id,
32 p.name AS product_name,
33 COUNT(DISTINCT ac.customer_id) AS num_active_customers_bought
34 FROM dormant_customers dc
35 CROSS JOIN LATERAL (
36 -- Find products dormant customer has purchased
37 SELECT product_id
38 FROM customer_products
39 WHERE customer_id = dc.customer_id
40 ) dp
41 -- Find active customers who bought the same products
42 JOIN customer_products acp ON dp.product_id = acp.product_id AND acp.customer_id != dc.customer_id
43 JOIN active_customers ac ON acp.customer_id = ac.customer_id
44 -- Find other products these active customers bought
45 JOIN customer_products acp2 ON ac.customer_id = acp2.customer_id
46 -- Exclude products the dormant customer already bought
47 LEFT JOIN customer_products dcp ON dc.customer_id = dcp.customer_id AND acp2.product_id = dcp.product_id
48 JOIN products p ON acp2.product_id = p.product_id
49 WHERE dcp.product_id IS NULL -- Only recommend products they haven't bought
50 GROUP BY dc.customer_id, p.product_id, p.name
51)
52-- Final output with top 3 recommendations per dormant customer
53SELECT
54 dc.customer_id,
55 dc.name AS customer_name,
56 STRING_AGG(r.product_name, ', ' ORDER BY r.num_active_customers_bought DESC) AS recommended_products
57FROM dormant_customers dc
58LEFT JOIN LATERAL (
59 SELECT product_name, num_active_customers_bought
60 FROM recommendations
61 WHERE dormant_customer_id = dc.customer_id
62 ORDER BY num_active_customers_bought DESC
63 LIMIT 3
64) r ON true
65GROUP BY dc.customer_id, dc.name
66ORDER BY dc.customer_id;This example identifies potential inventory issues across the supply chain:
This analysis identifies inventory risks by comparing current inventory levels to recent sales, and
highlights distribution issues where inventory is not balanced across warehouses.
1-- Inventory analysis across all warehouses
2WITH
3inventory_summary AS (
4 SELECT
5 p.product_id,
6 p.name AS product_name,
7 p.category,
8 p.price,
9 COALESCE(SUM(i.quantity), 0) AS total_inventory,
10 COUNT(DISTINCT i.warehouse_id) AS warehouse_count,
11 MAX(i.quantity) AS max_warehouse_quantity,
12 MIN(i.quantity) AS min_warehouse_quantity
13 FROM products p
14 LEFT JOIN inventory i ON p.product_id = i.product_id
15 GROUP BY p.product_id, p.name, p.category, p.price
16),
17sales_30_days AS (
18 SELECT
19 oi.product_id,
20 SUM(oi.quantity) AS units_sold_30d
21 FROM order_items oi
22 JOIN orders o ON oi.order_id = o.order_id
23 WHERE o.order_date > CURRENT_DATE - INTERVAL '30 days'
24 GROUP BY oi.product_id
25),
26inventory_risk AS (
27 SELECT
28 i.product_id,
29 i.product_name,
30 i.category,
31 i.price,
32 i.total_inventory,
33 COALESCE(s.units_sold_30d, 0) AS units_sold_30d,
34 i.warehouse_count,
35 i.max_warehouse_quantity,
36 i.min_warehouse_quantity,
37 CASE
38 WHEN i.total_inventory = 0 THEN 'Out of Stock'
39 WHEN i.total_inventory < COALESCE(s.units_sold_30d, 0) * 0.5 THEN 'Critical Low'
40 WHEN i.total_inventory < COALESCE(s.units_sold_30d, 0) THEN 'Low Stock'
41 WHEN i.total_inventory > COALESCE(s.units_sold_30d, 0) * 3 THEN 'Overstocked'
42 ELSE 'Healthy'
43 END AS inventory_status
44 FROM inventory_summary i
45 LEFT JOIN sales_30_days s ON i.product_id = s.product_id
46)
47-- Final analysis with warehouse distribution issues highlighted
48SELECT
49 ir.product_id,
50 ir.product_name,
51 ir.category,
52 ir.price,
53 ir.total_inventory,
54 ir.units_sold_30d,
55 ir.inventory_status,
56 ir.warehouse_count,
57 CASE
58 WHEN ir.warehouse_count > 1 AND
59 (ir.max_warehouse_quantity > ir.min_warehouse_quantity * 3)
60 THEN 'Unbalanced Distribution'
61 WHEN ir.warehouse_count = 0 THEN 'Not Stocked'
62 WHEN ir.warehouse_count = 1 THEN 'Single Warehouse'
63 ELSE 'Balanced Distribution'
64 END AS distribution_status
65FROM inventory_risk ir
66ORDER BY
67 CASE
68 WHEN ir.inventory_status = 'Out of Stock' THEN 1
69 WHEN ir.inventory_status = 'Critical Low' THEN 2
70 WHEN ir.inventory_status = 'Low Stock' THEN 3
71 WHEN ir.inventory_status = 'Overstocked' THEN 4
72 ELSE 5
73 END,
74 ir.category,
75 ir.product_name;This example analyzes the effectiveness of product promotions:
This comprehensive analysis measures the effectiveness of promotions by comparing sales during promotional
periods to sales in the 30 days before each promotion started, identifying which promotions had the greatest impact.
1-- Analyze effectiveness of promotions
2WITH
3promotion_periods AS (
4 SELECT
5 promotion_id,
6 name AS promotion_name,
7 start_date,
8 end_date,
9 discount_percent
10 FROM promotions
11),
12product_promotions AS (
13 SELECT
14 pp.promotion_id,
15 pp.product_id,
16 p.name AS product_name,
17 p.category,
18 p.price,
19 pr.promotion_name,
20 pr.start_date,
21 pr.end_date,
22 pr.discount_percent,
23 p.price * (1 - pr.discount_percent / 100) AS discounted_price
24 FROM promotion_products pp
25 JOIN products p ON pp.product_id = p.product_id
26 JOIN promotion_periods pr ON pp.promotion_id = pr.promotion_id
27),
28sales_during_promotion AS (
29 SELECT
30 pp.promotion_id,
31 pp.product_id,
32 pp.product_name,
33 pp.category,
34 pp.promotion_name,
35 pp.discount_percent,
36 COUNT(oi.item_id) AS units_sold,
37 SUM(oi.price_each * oi.quantity) AS revenue
38 FROM product_promotions pp
39 JOIN orders o ON o.order_date BETWEEN pp.start_date AND pp.end_date
40 JOIN order_items oi ON o.order_id = oi.order_id AND pp.product_id = oi.product_id
41 GROUP BY
42 pp.promotion_id,
43 pp.product_id,
44 pp.product_name,
45 pp.category,
46 pp.promotion_name,
47 pp.discount_percent
48),
49sales_before_promotion AS (
50 SELECT
51 pp.promotion_id,
52 pp.product_id,
53 COUNT(oi.item_id) AS units_sold,
54 SUM(oi.price_each * oi.quantity) AS revenue
55 FROM product_promotions pp
56 JOIN orders o ON o.order_date BETWEEN (pp.start_date - INTERVAL '30 days') AND (pp.start_date - INTERVAL '1 day')
57 JOIN order_items oi ON o.order_id = oi.order_id AND pp.product_id = oi.product_id
58 GROUP BY
59 pp.promotion_id,
60 pp.product_id
61)
62-- Final promotion effectiveness analysis
63SELECT
64 sdp.promotion_name,
65 sdp.product_name,
66 sdp.category,
67 sdp.discount_percent,
68 sdp.units_sold AS units_during_promo,
69 COALESCE(sbp.units_sold, 0) AS units_before_promo,
70 CASE
71 WHEN COALESCE(sbp.units_sold, 0) = 0 THEN NULL
72 ELSE ROUND((sdp.units_sold::numeric / sbp.units_sold) * 100 - 100, 1)
73 END AS sales_lift_pct,
74 sdp.revenue AS revenue_during_promo,
75 COALESCE(sbp.revenue, 0) AS revenue_before_promo,
76 sdp.revenue - COALESCE(sbp.revenue, 0) AS incremental_revenue,
77 CASE
78 WHEN sdp.units_sold > COALESCE(sbp.units_sold * 2, 0) THEN 'High Impact'
79 WHEN sdp.units_sold > COALESCE(sbp.units_sold * 1.2, 0) THEN 'Positive Impact'
80 WHEN sdp.units_sold < COALESCE(sbp.units_sold * 0.8, 1) THEN 'Negative Impact'
81 ELSE 'Neutral Impact'
82 END AS promotion_impact
83FROM sales_during_promotion sdp
84LEFT JOIN sales_before_promotion sbp ON sdp.promotion_id = sbp.promotion_id AND sdp.product_id = sbp.product_id
85ORDER BY
86 CASE
87 WHEN sdp.units_sold > COALESCE(sbp.units_sold * 2, 0) THEN 1
88 WHEN sdp.units_sold > COALESCE(sbp.units_sold * 1.2, 0) THEN 2
89 WHEN sdp.units_sold < COALESCE(sbp.units_sold * 0.8, 1) THEN 4
90 ELSE 3
91 END,
92 sdp.promotion_name,
93 sdp.category,
94 sdp.product_name;Advanced joins and set operations can be powerful, but they can also have significant performance implications
if not used carefully. Here are some key considerations and best practices.
Optimizing JOIN Performance:
Ensure proper indexing
Create indexes on join columns to improve join performance. For example:
CREATE INDEX idx_orders_customer_id ON orders(customer_id);
Join order matters
The query optimizer usually chooses the best join order, but sometimes it helps to start with the most selective tables (those that return fewer rows).
Limit data before joining
Use WHERE clauses to filter tables before joining them, reducing the number of rows processed.
Beware of CROSS JOINs
They produce Cartesian products, which can be extremely large. Always use them with caution.
Monitor LATERAL JOIN performance
LATERAL joins run the right-side query once for each row from the left side. Use LIMIT and efficient filtering.
Optimizing Set Operations:
UNION ALL vs UNION
Use UNION ALL instead of UNION when you know there are no duplicates or when duplicates are acceptable.
UNION ALL avoids the overhead of removing duplicates.
Minimize data before set operations
Filter each query to include only necessary columns and rows before applying set operations.
Consider indexes for INTERSECT and EXCEPT
These operations perform better with appropriate indexes on the columns being compared.
Consider EXISTS as alternative
Sometimes a correlated subquery with EXISTS can be more efficient than INTERSECT or EXCEPT.
Always analyze execution plans for complex queries using EXPLAIN or EXPLAIN ANALYZE in PostgreSQL
(or the equivalent in your database system).
Look for sequential scans, nested loops with large tables, missing indexes, and high row counts in the execution plan.
These can indicate potential performance issues.
1-- Analyze a complex query's execution plan
2EXPLAIN ANALYZE
3SELECT
4 c.customer_id,
5 c.name,
6 o.order_id,
7 o.order_date,
8 o.total_amount
9FROM customers c
10CROSS JOIN LATERAL (
11 SELECT
12 order_id,
13 order_date,
14 total_amount
15 FROM orders
16 WHERE orders.customer_id = c.customer_id
17 ORDER BY order_date DESC
18 LIMIT 1
19) o;In some cases, alternative approaches may perform better:
Instead of multiple LATERAL JOINs:
This alternative uses window functions instead of LATERAL JOINs to find the latest order for each customer,
which might perform better in some databases.
Instead of INTERSECT or EXCEPT:
These alternatives to EXCEPT might perform better in certain situations, depending on your database
and the indexes available.
1-- Using window functions instead of LATERAL for latest order
2SELECT
3 c.customer_id,
4 c.name,
5 o.order_id,
6 o.order_date,
7 o.total_amount
8FROM customers c
9LEFT JOIN (
10 SELECT
11 customer_id,
12 order_id,
13 order_date,
14 total_amount,
15 ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) AS rn
16 FROM orders
17) o ON c.customer_id = o.customer_id AND o.rn = 1;1-- Using EXISTS instead of EXCEPT
2SELECT c.city
3FROM customers c
4WHERE NOT EXISTS (
5 SELECT 1 FROM warehouses w WHERE w.city = c.city
6);
7
8-- Using JOIN with NULL check instead of EXCEPT
9SELECT c.city
10FROM customers c
11LEFT JOIN warehouses w ON c.city = w.city
12WHERE w.city IS NULL;Use proper indexes on join columns
Filter data before performing joins or set operations
Use UNION ALL instead of UNION when possible
Be cautious with CROSS JOINs and complex LATERAL JOINs
Analyze query execution plans
Consider alternative approaches for performance-critical queries
Test with realistic data volumes
Break complex queries into CTEs for readability and potential performance benefits
Important Note:
Performance characteristics can vary significantly between database systems. What works best in PostgreSQL
may not be optimal in Oracle, SQL Server, or MySQL. Always test your queries on your specific database
with realistic data volumes.
Master advanced JOIN types and set operations in SQL to solve complex data problems that can't be addressed with
basic joins alone. Learn how to use LATERAL JOINs, FULL OUTER JOINs, CROSS JOINs, UNION, INTERSECT, and EXCEPT
to perform sophisticated data analysis and build powerful reports.
Your progress is saved in this browser, on this device only.