JOIN: SELECT * FROM customers c JOIN orders o ON c.id = o.customer_id; SUBQUERY: SELECT * FROM customers WHERE id IN (SELECT customer_id FROM orders);

Same result possible with JOIN or subquery

JOINs in FROM clause

Subqueries in WHERE, SELECT, or FROM

EXISTS for existence checks

IN for value lists

Core references in this topic include WHERE, =, BETWEEN. 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

Use with exact matches. Do not use = NULL.

BETWEEN

Checks whether a value falls inside an inclusive lower/upper range.

order_total BETWEEN 100 AND 500

EXISTS

Tests whether a correlated or non-correlated subquery returns at least one row.

WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id)

CHECK

Validates a row-level rule whenever data is inserted or updated.

CHECK (salary >= 0)

DISTINCT

Removes duplicate values from a projection or aggregate input set.

COUNT(DISTINCT customer_id)

Same result possible with JOIN or subquery

JOINs in FROM clause

Subqueries in WHERE, SELECT, or FROM

EXISTS for existence checks

IN for value lists

Use JOINs for combining data

Use EXISTS for existence checks

Use subqueries for clarity when appropriate

Test performance of both approaches

Consider readability and maintainability

Use EXPLAIN to compare

JOINs typically faster than subqueries

EXISTS faster than IN for large datasets