Part 2: Joins, Set Operations & Constraints

1. The Core Join Logic

When linking distinct physical data structures, the join type defines how unmatched or structural records are handled.

• INNER JOIN: Filters out unmatched records from both tables. Only records that meet the evaluation criteria in both sets survive.
• LEFT (OUTER) JOIN: Preserves the entire left table. If no match exists on the right, those columns are padded with NULL primitives.
• RIGHT (OUTER) JOIN: The mirror image of a left join. It preserves all right-side records, padding missing left fields with NULL.
• FULL (OUTER) JOIN: Preserves both tables completely. Unmatched elements from either side are padded with NULL placeholders.

---

2. Senior Interview Classics: Self, Cross, and Anti-Joins

The Self-Join (Hierarchies & Timelines)

A self-join means evaluating a table against itself by generating two distinct logical aliases. It is the primary tool for processing hierarchy tracking (e.g., matching employees to managers) or looking for historical sequences.

High-Yield Scenario: Find employees who make more money than their managers

SELECT 
    emp.name AS employee_name, 
    emp.salary AS emp_salary,
    mgr.name AS manager_name, 
    mgr.salary AS mgr_salary
FROM employees emp
JOIN employees mgr ON emp.manager_id = mgr.employee_id
WHERE emp.salary > mgr.salary;

The Cross Join (Cartesian Explosions)

A CROSS JOIN pairs every single row of Table A with every single row of Table B. If Table A contains $1,000$ rows and Table B contains $1,000$ rows, the execution set explodes into $1,000 \times 1,000 = 1,000,000$ records.

• Legitimate Use Case: Generating configuration matrix permutations (e.g., crossing 10 distinct clothing styles against 5 sizing metrics).
• The Implicit Bug: Omitting the ON filtering criteria in an old comma-separated layout (FROM table_a, table_b) triggers a slow, hidden CROSS JOIN that can destabilize production environments.

Anti-Joins (Finding Missing records)

An Anti-Join filters out records that do not have a match in the target table. It is perfect for isolating data gaps or orphan values.

High-Yield Scenario: Find customers who have never placed an order

SELECT c.customer_id, c.name
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE o.customer_id IS NULL; -- Isolates unmatched left records

---

3. The Double-Counting Trap in Joins

This is a standard senior architectural interview question. If you join a Customers table to an Orders table on a 1-to-Many relationship, customer rows duplicate for every order they have placed.

If you attempt to run a simple calculation like SUM(c.credit_limit) across that combined output table, the value will be wildly inflated due to row expansion.

Architectural Fix: Never execute an aggregation across an unmanaged 1-to-Many join. Compute your metric aggregates inside an isolated subquery or Common Table Expression (CTE) before you execute the structural join.

---

4. Set Operations: UNION vs. UNION ALL

While joins bind columns horizontally, set operations stack datasets vertically.

Metric	UNION	UNION ALL
Row Mechanics	Stacks sets and explicitly deduplicates rows.	Stacks sets and retains all rows.
Performance Profile	Slower (forces a full disk/memory sort check).	Extremely fast (simple memory append).
Duplicates	Purged automatically.	Kept completely intact.

The Rules of Engagement

For set operators (UNION, UNION ALL, INTERSECT, EXCEPT) to compile successfully:

1. Both components must output the exact same number of columns.
2. The columns must share fully matching or compatible data types in identical sequence.

---

5. Constraints at the DDL Level

Constraints protect data integrity at the schema level — a favorite “design the table” interview topic.

• PRIMARY KEY — uniquely identifies a row; implicitly NOT NULL + UNIQUE; creates a clustered index (usually). One per table.
• FOREIGN KEY ... REFERENCES parent(col) — enforces referential integrity; the value must exist in the parent (or be NULL). Add ON DELETE CASCADE (delete child rows with the parent), ON DELETE SET NULL (null out children), or ON DELETE RESTRICT (block deleting a parent that has children).
• UNIQUE — no duplicate values (but multiple NULLs are usually allowed). Unlike PK, it doesn’t have to be NOT NULL.
• CHECK (condition) — validates a row-level condition (e.g. CHECK (salary > 0)).
• NOT NULL — the column can’t store NULL.
• DEFAULT value — applied when no value is given on insert.

Constraint	Duplicates?	NULLs?	Purpose
PRIMARY KEY	No	No	Row identity
UNIQUE	No	Yes (usually)	No duplicate values
FOREIGN KEY	Yes	Yes	Referential integrity
CHECK	Yes	Yes	Value-range rule
NOT NULL	Yes	No	Mandatory value

6. Choosing the Right Join (Scenario Guide)

• “Return only matching rows” → INNER JOIN.
• “Keep all rows from one side, NULLs where no match” → LEFT JOIN (or RIGHT JOIN for the other side).
• “Show all rows from both sides regardless of match” → FULL OUTER JOIN.
• “Every combination of A × B” → CROSS JOIN (cartesian product — careful, explodes quickly).
• “Rows that relate to other rows in the same table” → SELF JOIN (e.g., employees and their managers).
• “Rows in A with no match in B” → ANTI JOIN (WHERE b.id IS NULL on a LEFT JOIN, or NOT IN/NOT EXISTS).

7. EXISTS vs IN vs JOIN

Three ways to ask “are these related?” — they differ in semantics and performance:

• IN — value list / subquery match. Gotcha: NOT IN with a subquery that returns NULL returns no rows (NULL comparisons are unknown).
• EXISTS — boolean “is there at least one row?” — NULL-safe, stops at the first match (semi-join), typically fastest.
• JOIN — actually combines tables (gives you columns from both); can duplicate rows when there are multiple matches → needs DISTINCT.

-- EXISTS: existence check, NULL-safe, no dupes
SELECT c.name FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

-- JOIN: only when you need columns from both tables
SELECT c.name, o.id FROM customers c JOIN orders o ON o.customer_id = c.id;

Rule of thumb: EXISTS for existence, IN for small value lists, JOIN when you need both tables’ columns.