-- Subquery with IN

SELECT * FROM products

WHERE category_id IN (SELECT id FROM categories WHERE active = true);

-- Subquery with comparison

SELECT * FROM orders

WHERE total > (SELECT AVG(total) FROM orders);

-- EXISTS for related data

SELECT * FROM customers c

WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id AND o.total > 1000);

-- CTE for complex filtering

WITH high_value_orders AS (

SELECT customer_id, SUM(total) AS total_spent

FROM orders

GROUP BY customer_id

HAVING SUM(total) > 10000

)

SELECT c.* FROM customers c

JOIN high_value_orders h ON c.id = h.customer_id;

-- Multiple CTEs

WITH active_categories AS (

SELECT id FROM categories WHERE active = true

),

recent_products AS (

SELECT * FROM products WHERE created_date >= CURRENT_DATE - INTERVAL '30 days'

)

SELECT * FROM recent_products WHERE category_id IN (SELECT id FROM active_categories);

Subqueries in WHERE must return compatible data type

Scalar subqueries return single value

IN subqueries return list of values

EXISTS subqueries return boolean

CTEs defined with WITH before main query

Multiple CTEs separated by commas

Core references in this topic include WHERE, =, <, >, <=, >=. Learn what each one does, when to use it, and the execution or engine rules that matter.

WHERE

Filters rows before projection and sorting. It decides which rows continue through the query pipeline.

SELECT ... FROM table WHERE condition;

Most performance issues start with a weak WHERE clause or a missing supporting index.

=

Returns rows where the left and right values are exactly equal.

column = value