Skip to content

09-set-operations.md

Latest commit

 

History

History
89 lines (66 loc) · 3.21 KB

09-set-operations.md

File metadata and controls

89 lines (66 loc) · 3.21 KB
Raw

Chapter 09: Set Operations

Set operations allow you to combine the results of two or more queries into a single result set. While JOIN combines columns from different tables horizontally, set operations combine rows vertically.

9.1 UNION — Combine and Deduplicate

UNION combines the result sets of two queries and removes duplicate rows. All queries must have the same number of columns with compatible data types.

-- All unique cities from both customers and sellers
SELECT customer_city AS city, customer_state AS state
FROM customers
UNION
SELECT seller_city, seller_state
FROM sellers
ORDER BY state, city
LIMIT 10;

9.2 UNION ALL — Combine and Keep Duplicates

UNION ALL is similar to UNION but it keeps all records, including duplicates. It's faster than UNION because it doesn't need to perform a deduplication step.

-- Unified count of records in each table
SELECT 'customers' AS table_name, COUNT(*) AS row_count FROM customers
UNION ALL
SELECT 'orders', COUNT(*) FROM orders
UNION ALL
SELECT 'products', COUNT(*) FROM products;

9.3 INTERSECT — Rows in Both Queries

INTERSECT returns only the rows that appear in the results of both queries.

-- States that have both customers AND sellers
SELECT customer_state AS state FROM customers
INTERSECT
SELECT seller_state FROM sellers
ORDER BY state;

9.4 EXCEPT — Rows in First but Not Second

EXCEPT (or MINUS in some databases) returns rows from the first query that are not present in the second.

-- States with customers but NO sellers
SELECT DISTINCT customer_state AS state FROM customers
EXCEPT
SELECT DISTINCT seller_state FROM sellers
ORDER BY state;

9.5 Set Operation Rules

  1. Column Count: Both queries must have the same number of columns.
  2. Data Types: Coresponding columns must have compatible data types.
  3. Column Names: Result column names are inherited from the first query.
  4. Sorting: ORDER BY must be placed at the end of the entire statement.

Exercises

  1. Use UNION ALL to create a summary showing 'Buyers' with the count of distinct customers and 'Sellers' with the count of distinct sellers.
  2. Find cities that are in the customers table but NOT in the sellers table. How many are there?
  3. Use INTERSECT to find product categories that have been both sold and reviewed.
  4. Create a unified list showing all unique cities from customers, sellers, and geolocation tables.
Solutions 
-- Exercise 1
SELECT 'Buyers', COUNT(DISTINCT customer_id) FROM customers UNION ALL SELECT 'Sellers', COUNT(DISTINCT seller_id) FROM sellers;

-- Exercise 2
SELECT COUNT(*) FROM (SELECT DISTINCT customer_city FROM customers EXCEPT SELECT DISTINCT seller_city FROM sellers);

-- Exercise 3
SELECT p.product_category_name FROM order_items oi JOIN products p ON oi.product_id = p.product_id INTERSECT SELECT p.product_category_name FROM order_reviews r JOIN orders o ON r.order_id = o.order_id JOIN order_items oi ON o.order_id = oi.order_id JOIN products p ON oi.product_id = p.product_id;

-- Exercise 4
SELECT customer_city FROM customers UNION SELECT seller_city FROM sellers UNION SELECT geolocation_city FROM geolocation;