Joins, Subqueries, and Set Operations

Combine related data with joins, understand null behavior, and compare subqueries with set operations.

INNER JOIN and LEFT JOIN

A join combines rows based on a relationship. INNER JOIN returns matching pairs only. LEFT JOIN preserves every row from the left input and fills right-side columns with NULL when no match exists. Use table aliases and qualify columns to avoid ambiguity.

A common mistake is placing a filter on the right-side table in WHERE after a LEFT JOIN. That can remove NULL-extended rows and make the result behave like an inner join. If the condition determines which right-side rows may match while preserving all left rows, it may belong in the ON clause instead.

Join customers and orders

SELECT c.id, c.name, o.id AS order_id, o.total
FROM customers AS c
LEFT JOIN orders AS o
  ON o.customer_id = c.id
ORDER BY c.id, o.id;

💡 This returns all customers, including those with no orders. For customers without orders, the order columns are NULL.

Key Takeaways

INNER JOIN keeps matches; LEFT JOIN preserves all left-side rows.
Use aliases and qualify shared column names.
WHERE filters can remove unmatched rows after a LEFT JOIN.

Knowledge Check

Which join preserves all rows from the left table even when there is no match?

RIGHT, FULL, CROSS, and Self Joins

RIGHT JOIN preserves the right input and is often rewritten as a LEFT JOIN by reversing table order for readability. FULL OUTER JOIN preserves unmatched rows from both sides. CROSS JOIN forms a Cartesian product, combining every row from one input with every row from the other, so row counts can grow rapidly. A self join joins a table to itself, often to represent hierarchies such as employees and managers.

Choose joins based on the result semantics, not just syntax familiarity. Inspect cardinality and keys: joining two one-to-many relationships at once can multiply rows and inflate aggregate results unless the data is aggregated at the right grain first.

Self join for an employee hierarchy

SELECT e.name AS employee, m.name AS manager
FROM employees AS e
LEFT JOIN employees AS m
  ON e.manager_id = m.id;

💡 The same table appears twice with different aliases. The LEFT JOIN includes employees who do not have a manager assigned.

Key Takeaways

FULL OUTER JOIN preserves unmatched rows from both sides.
CROSS JOIN creates every possible row pair.
Understand join cardinality before aggregating joined data.

Knowledge Check

What does a CROSS JOIN produce?

Subqueries, EXISTS, IN, and UNION

A subquery is a query nested inside another statement. EXISTS tests whether at least one row is returned and is often expressive for relationship checks. IN compares a value with a set of values; be mindful that NULL values can affect NOT IN logic. UNION combines result sets and removes duplicates, while UNION ALL preserves duplicates and is often faster.

Use the form that best expresses intent, then inspect the query plan for performance-sensitive workloads. Correlated subqueries reference values from an outer query; PostgreSQL may optimize them, but performance depends on indexes, cardinality, and query shape.

Find customers with orders

SELECT c.id, c.name
FROM customers AS c
WHERE EXISTS (
  SELECT 1
  FROM orders AS o
  WHERE o.customer_id = c.id
);

💡 EXISTS returns true when the subquery finds at least one order for the current customer. The selected value inside the EXISTS subquery is not used as output.

Key Takeaways

EXISTS tests whether matching rows exist.
UNION removes duplicates; UNION ALL keeps them.
Handle NULL carefully with NOT IN; NOT EXISTS can be safer for anti-joins.

Knowledge Check

Which set operation preserves duplicates?

INNER JOIN and LEFT JOIN

RIGHT, FULL, CROSS, and Self Joins

Subqueries, EXISTS, IN, and UNION

STAY UPDATED WITH BYTES!