🚀 LEVEL UP TO SENIOR:Unlock 500+ Advanced Practical Challenges & Exercises.

🎓 COURSERA PARTNER:Earn professional Google, Meta, and IBM certificates to supercharge your resume.

Tools

HTML MASTER CLASS /// LEARN TAGS /// BUILD STRUCTURE /// SEMANTIC WEB /// HTML MASTER CLASS /// LEARN TAGS ///

Execution Plans in SQL & Databases

Learn about Execution Plans in this comprehensive SQL & Databases development tutorial. The Brain of the DB.

⚡ Total XP: 0|💻 sql XP: 0

Skill Matrix

UNLOCK NODES BY LEARNING NEW TAGS.

Select an unlocked node to view details root

🚀 LEVEL UP TO SENIOR:Unlock 500+ Advanced Practical Challenges & Exercises.

🎓 COURSERA PARTNER:Earn professional Google, Meta, and IBM certificates to supercharge your resume.

1The Query Optimizer

When you send a query, the DB's 'Query Optimizer' analyzes it. It looks at table sizes and indexes, and generates dozens of potential 'Execution Plans' to fetch the data. It calculates the cost of each plan, picks the cheapest/fastest one, and executes it. This all happens in milliseconds.

2Step-by-Step Breakdown

The Written Order. When you write a query, you write it in English order: SELECT fields FROM table WHERE condition. But the database DOES NOT execute it in that order.

The Execution Order. The database engine executes queries logically. Step 1: FROM (Which table?). Step 2: WHERE (Filter the rows). Step 3: SELECT (Grab the specific columns).

Why it matters. If you create an alias in the SELECT clause (like 'SELECT name AS n'), you CANNOT use that alias in the WHERE clause, because the WHERE clause executes BEFORE the SELECT clause.

Aliases (AS). You can rename columns or tables temporarily in your result set using the 'AS' keyword. 'SELECT first_name AS Name'. This is purely cosmetic for the output.

Knowledge Check. Why will the query SELECT total_price AS t FROM orders WHERE t > 100 result in an error?

→Because you cannot alias numbers
→Because the WHERE clause executes before the SELECT clause creates the alias

Expressions. SQL can perform math and string manipulation directly in the SELECT clause. 'SELECT salary * 12 AS annual_salary FROM employees;'.

Functions. Databases have built-in functions. 'SELECT UPPER(name) FROM users;' will return all names in uppercase. 'SELECT NOW();' returns the current server time.

DISTINCT. If a table has 50 users from 'Spain' and 50 from 'France', 'SELECT country' returns 100 rows. 'SELECT DISTINCT country' returns only 2 rows (unique values).

Result Sets. A query always returns a 'Result Set'. Even if it returns 1 row, or 0 rows, the output is always formatted as a virtual table.

Summary. Always remember: FROM runs first, SELECT runs last.

Level Up 🚀

Advanced cheat sheets, SEO tricks, and interview prep for this topic.

Browser Support

ChromeSupported

Fully supported.

FirefoxSupported

Fully supported.

SafariSupported

Fully supported.

EdgeSupported

Fully supported.

Accessibility (A11y)

1Query Result Tables Deserve Real Table Markup, Not Styled Divs

When rendering the 'virtual table' a SELECT query returns, use a genuine HTML <table> with <th> column headers (matching the aliases from your SELECT ... AS clauses) so screen reader users can navigate results by row and column instead of a flat list of unrelated text.

SEO Implications

1
Correct Aliasing Produces Cleaner, More Crawlable JSON APIs
An API endpoint that renames raw SQL columns via 'SELECT price * 12 AS annual_salary' produces self-descriptive JSON keys, which matters for any documentation pages or structured data snippets that expose that API's shape to search engines.

Best Practices

Remember the Logical Execution Order: FROM, WHERE, then SELECT

Because WHERE is evaluated before SELECT, you cannot filter using an alias defined in the SELECT clause in the same query's WHERE clause — if you need that, either repeat the full expression in WHERE or wrap the query and filter in an outer SELECT.

Use DISTINCT Deliberately, Not as a Default Fix for Duplicate-Looking Rows

DISTINCT deduplicates the entire row as returned, which can hide a real bug (like an unintended JOIN fan-out) instead of fixing it — investigate why duplicates appear before reaching for DISTINCT as a quick patch.

Frequent Bugs

THE BUG

A query like 'SELECT total_price AS t FROM orders WHERE t > 100' fails with a 'column "t" does not exist' error.

THE FIX

The WHERE clause executes before the SELECT clause creates the alias, so 't' doesn't exist yet at that point in execution. Repeat the original expression in WHERE (WHERE total_price > 100), or filter in a subquery/CTE that already has the alias applied.

Real-World Examples

Computing an Annual Salary Column With Aliasing

An HR report needed to show each employee's monthly salary alongside a computed annual figure, with a clean column name for the frontend to consume directly.

SELECT
  name,
  salary AS monthly_salary,
  salary * 12 AS annual_salary
FROM employees
WHERE department = 'Engineering';

Interview Prep

Pascual Vila

Frontend Instructor // Code Syllabus

Common Pitfalls & Errors

The Error //

Referencing a SELECT alias inside the same query's WHERE clause

-- Wrong: 't' doesn't exist yet at the WHERE stage
SELECT total_price AS t FROM orders WHERE t > 100;

-- Correct
SELECT total_price AS t FROM orders WHERE total_price > 100;

The Solution //

WHERE executes before SELECT in the engine's logical evaluation order, so an alias created in SELECT doesn't exist yet when WHERE runs, causing a 'column does not exist' error. Repeat the full expression in WHERE instead of the alias, or filter in an outer query/CTE.

The Error //

Reaching for DISTINCT to hide duplicate rows caused by an unintended JOIN

-- Symptom: same user appears once per order due to the JOIN
SELECT DISTINCT users.name FROM users JOIN orders ON orders.user_id = users.id;

-- Better: fix the real question being asked
SELECT users.name, COUNT(orders.id) FROM users JOIN orders ON orders.user_id = users.id GROUP BY users.name;

The Solution //

DISTINCT deduplicates the final result set, but if duplicates are appearing because a JOIN is fanning out rows (e.g. matching one user to many orders), DISTINCT just papers over the real problem and can silently drop legitimately different rows. Investigate the JOIN condition before defaulting to DISTINCT.