Mastering SQL Execution Order & Multi‑Table Join Optimization

SQL Execution Order

Execute FROM clause

Apply ON filter

Add rows from joins

Apply WHERE condition filter

Execute GROUP BY (aliases from SELECT become usable)

Execute HAVING

Evaluate SELECT list

Apply DISTINCT to remove duplicates

Execute ORDER BY

Execute LIMIT

Multi-Table Join Optimization Recommendations

Prefer explicit joins (LEFT/RIGHT/INNER) over implicit comma joins; implicit joins can cause Cartesian products and massive memory waste.

Select only the columns you need; avoid retrieving unnecessary fields.

Avoid using

*

because it forces the engine to look up the table definition first.

Use >= instead of > when possible, as it is slightly more efficient.

Place the most restrictive filter conditions first in the WHERE clause.

Prefer joins over subqueries; subqueries create temporary tables and add overhead.

When subqueries are necessary, replace IN with EXISTS (or use NOT EXISTS for NOT IN) and replace DISTINCT with EXISTS where applicable.

Ensure WHERE conditions can use indexes; avoid calculations, functions, IS (NOT) NULL, and leading wildcards on indexed columns.

Prefer filtering before GROUP BY (use WHERE instead of HAVING when possible).

Put ORDER BY on indexed columns, ideally the primary key.

Replace DISTINCT with EXISTS when appropriate to avoid extra sorting.

IN performs a hash join, EXISTS performs a loop join; choose based on table sizes (use IN for similar sized tables, IN for small inner table, EXISTS for large inner table).

String equality, IN, and LIKE 'prefix%' can use indexes; !=, NOT IN, LIKE '%suffix', and LIKE 'mid%mid' cannot.

Numeric equality, !=, IN, NOT IN can all use indexes.

General Index Advice

For single‑column indexes, choose the one that provides the best selectivity for the current query.

When creating composite indexes, place the most selective column first.

Prefer composite indexes that cover as many WHERE‑clause columns as possible.

Analyze statistics and rewrite queries to make the best use of available indexes.

Index Mnemonics

Full‑match loves me, left‑most prefix must be obeyed.

Leading column must stay, middle columns cannot be broken.

Avoid calculations on indexed columns; range queries invalidate later parts.

Put LIKE pattern at the far right; avoid * in covering indexes.

Do not use NULL, empty values, or OR on indexed columns.

Do not drop quotes on varchar literals; SQL remains simple.