Advanced JOIN Queries and Set Operations

# CHAPTER 15

Advanced JOIN Queries and Set Operations #

1. Introduction #

2. Learning Objectives #

• Use a SELF JOIN to link a table to itself.

• Understand the algorithmic explosion of a CROSS JOIN.

• Vertically stack query results using the UNION operator.

• Find overlapping datasets using INTERSECT.

• Subtract datasets using EXCEPT.

3. The SELF JOIN #

To join a table to itself, you MUST use Table Aliases to trick PostgreSQL into thinking it is looking at two different tables.

-- We open the employees table twice! 
-- Once as 'e' (the worker), and once as 'm' (the manager).
SELECT 
    e.first_name AS "Employee", 
    m.first_name AS "Manager"
FROM employees e
LEFT JOIN employees m 
    ON e.manager_id = m.emp_id;

*(We use LEFT JOIN because the CEO does not have a manager. An INNER JOIN would exclude the CEO from the report!)*

4. The CROSS JOIN (Cartesian Product) #

SELECT c.color_name, s.size_name
FROM colors c
CROSS JOIN sizes s;

DANGER: Do not run a CROSS JOIN on large tables. If you cross-join a 10,000-row table with another 10,000-row table, PostgreSQL will attempt to generate 100,000,000 rows and your server will crash.

5. Set Operations: UNION #

The Rules of UNION:

1. 1. Both queries must have the exact same number of columns.

1. 2. The columns must have compatible data types.

-- The company wants a master mailing list of ALL humans they interact with.
SELECT first_name, email, 'Customer' AS role FROM customers
UNION
SELECT first_name, email, 'Supplier' AS role FROM suppliers;

*(By default, UNION automatically removes exact duplicates. If you want to keep duplicates, use UNION ALL, which is also much faster).*

6. Set Operations: INTERSECT #

-- Find users who have BOTH a registered account AND are signed up for the newsletter
SELECT email FROM registered_users
INTERSECT
SELECT email FROM newsletter_subscribers;

7. Set Operations: EXCEPT #

-- Find registered users who have NEVER signed up for the newsletter
SELECT email FROM registered_users
EXCEPT
SELECT email FROM newsletter_subscribers;

8. Mini Project: The Corporate Hierarchy Report #

SELECT 
    worker.first_name AS "Staff Member",
    manager.first_name AS "Direct Supervisor",
    director.first_name AS "Department Head"
FROM employees worker
LEFT JOIN employees manager 
    ON worker.manager_id = manager.emp_id
LEFT JOIN employees director 
    ON manager.manager_id = director.emp_id
WHERE worker.department = 'IT';

9. Common Mistakes #

• UNION Column Mismatches: If Query A selects (name, email) and Query B selects (email, name), the UNION will execute, but the data will be hopelessly scrambled in the final output. The columns must align perfectly in the exact same order!

• Accidental Cross Joins: If you write SELECT * FROM users, orders; (using a comma instead of a JOIN clause), PostgreSQL executes a massive CROSS JOIN. Never use comma-separated FROM clauses.

10. Best Practices #

• Use UNION ALL when possible: Because standard UNION scans the final massive list to remove duplicates, it is heavily CPU-intensive. If you know mathematically that the two queries cannot have duplicates (or if you don't care), always use UNION ALL for a massive performance boost.

11. Exercises #

1. 1. Write a UNION ALL query that combines productname from the activeproducts table and the archived_products table.

1. 2. What is the fundamental difference between how a JOIN combines data and how a UNION combines data?

12. SQL Challenges #

SELECT e.first_name, e.salary, m.first_name AS manager, m.salary AS manager_salary
FROM employees e
INNER JOIN employees m ON e.manager_id = m.emp_id
WHERE e.salary > m.salary;

13. MCQ Quiz with Answers #

14. Interview Questions #

• Q: Explain how a SELF JOIN works under the hood. Why are Table Aliases strictly mandatory when performing one?

• Q: Describe a business use-case where executing a CROSS JOIN is actually the correct architectural choice, rather than a catastrophic mistake.

15. FAQs #

16. Summary #

17. Next Chapter Recommendation #