Mastering SQL Execution Order and Multi-Table Join Optimization

SQL Execution Order

Execute FROM clause

Apply ON filter

Add outer rows for joins

Apply WHERE condition filter

Execute GROUP BY (aliases from SELECT can be used thereafter)

Execute HAVING

Process SELECT list

Apply DISTINCT to remove duplicates

Execute ORDER BY

Execute LIMIT

Multi-Table Join Optimization Tips

1. Prefer explicit joins (LEFT JOIN, RIGHT JOIN, INNER JOIN) and avoid implicit comma joins; implicit joins can cause massive Cartesian products and waste memory.

2. Select only the columns you need; avoid retrieving many redundant fields.

3. Avoid using *; specifying columns prevents extra dictionary lookups.

4. Prefer >= over > for slightly better performance.

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

select * from score where sno in (
    select sno from score where language>60 and math>80 and english>80
    order by total_score desc
) -- slower

select sno, language, math, english, total_score from score where exists (
    select sno from score where english>=80 and math>=80 and language>=80
    order by total_score desc
) -- faster

6. Use joins instead of subqueries when possible, as subqueries create temporary tables and add overhead.

select a.id, a.name from a where a.id in (
    select b.aid from b where b.id=123
);

select a.id, a.name from a inner join b on a.id=b.aid where b.id=123;

7. Prefer EXISTS (or inner join) over IN, and NOT EXISTS (or outer join) over NOT IN.

8. Use EXISTS to replace DISTINCT when appropriate.

9. Ensure WHERE conditions use indexes; avoid calculations, functions, IS NULL, or wildcards on indexed columns.

10. Replace HAVING with WHERE to filter before GROUP BY.

11. Put ORDER BY on indexed columns, preferably the primary key.

General Index Recommendations

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

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

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

Analyze statistics and adjust query writing to select suitable indexes.

Index Mnemonics

Full match is best; obey the left‑most prefix rule.

Leading column must exist; middle columns must be continuous.

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

Put LIKE patterns with trailing % at the rightmost position; avoid * in covering indexes.

Minimize use of <> , NULL, OR as they can disable indexes.

Do not drop quotes on VARCHAR values; SQL remains simple.