The Select Statement

The SELECT statement is the most fundamental tool in SQL. It is how we ask the database for information. However, there is a major difference between how we write a query and how the database executes it.

Definition: The “Big 6” Clauses

Almost every retrieval query in SQL is built using these six clauses, and they must be written in this specific order:

1. SELECT - Which columns do you want?
2. FROM - Which table(s) are they in?
3. WHERE - Which rows meet your criteria?
4. GROUP BY - How should rows be aggregated?
5. HAVING - Which groups meet your criteria?
6. ORDER BY - How should the results be sorted?

Context: Syntax vs. Logic

If you were explaining a task to a human, you might say: “Go to the kitchen (FROM), find the cookies (WHERE), and bring me two (SELECT).”

SQL syntax puts SELECT first for readability, but logically, the database cannot “select” something until it knows where it is coming from.

Detailed Explanation: Logical Order of Execution

Understanding the order in which the database processes your query is the “Superpower” of SQL experts. It explains why certain aliases work in some places but fail in others.

The logical order is:

1. FROM: The database first identifies the source table.
2. WHERE: It filters the raw rows based on your conditions.
3. GROUP BY: It groups the remaining rows.
4. HAVING: It filters the groups.
5. SELECT: It finally picks the columns (and calculates any expressions or aliases).
6. ORDER BY: It sorts the final result set.
7. LIMIT / OFFSET: It discards rows outside the requested range.

Danger

The Alias Trap: Because SELECT happens after WHERE, you cannot use an alias defined in the SELECT clause inside the WHERE clause.

Incorrect: SELECT Price * 1.1 AS NewPrice FROM Products WHERE NewPrice > 100;
The database doesn’t know what ‘NewPrice’ is yet when it’s filtering rows!

Example: A Standard Query

Imagine a Products table:

ProductID	Name	Category	Price
1	Laptop	Electronics	1200
2	Mouse	Electronics	25
3	Desk	Furniture	350
4	Phone	Electronics	800

We want to find the names of Electronics that cost more than $100.

Basic Select

SELECT Name, Price
FROM Products
WHERE Category = 'Electronics' AND Price > 100;

Output

Name	Price
Laptop	1200
Phone	800

Under the Hood: Projection and Selection

In relational algebra:

• Selection ($\sigma$): This is the WHERE clause. It selects specific rows.
• Projection ($\pi$): This is the SELECT clause. It selects specific columns (attributes).

When you run SELECT Name FROM Products WHERE ID = 1, the engine:

1. Opens the Products table (FROM).
2. Finds the row where ID = 1 (Selection).
3. Discards all columns except Name (Projection).
4. Returns the result.

The SELECT * Performance Hit

While SELECT * (select all columns) is convenient for exploration, it is generally discouraged in production code.

1. Network Traffic: Sending unneeded data over the network wastes bandwidth.
2. Memory/I/O: The database must read more data from the disk and store it in the buffer cache.
3. Fragility: If a column is added or removed from the table, your application might break if it expects a specific number of columns.

Tip

Always explicitly name the columns you need. It makes your code faster and more resilient.