Anatomy of SELECT

Chapter 2 — "First Clearance"

The index is read — and QUERY keeps the promise. In the centre of the hall an amber holopanel unfolds with a soft click: the reading terminal opens under your account. The cursor blinks on an empty line. The archive is ready to answer — but it answers only well-posed questions. A query is a clearance protocol: every word in it stands exactly where it belongs.

QUERY: The terminal is yours. Speak to the archive short and exact — it isn’t deaf, it just won’t answer the muddled.

What SELECT really does

People often say that in SQL,
SELECT "picks columns".

That is a useful first explanation, but it is not the whole story.

What SELECT actually does is build the query result.

That result looks like a table: it has columns and rows. But those columns do not have to be direct copies of columns in the source table.

SELECT can show:

• columns from a table;
• literal numbers;
• text values;
• the results of calculations;
• expressions built from columns;
• the results of functions;
• columns given temporary names through aliases.

So SELECT answers the question:

What should appear in the final output?

The most common case is selecting data from a table:

SELECT name, price
FROM products;

This query says:

Show the values in the name and price columns from the products table.

But SELECT can do more than that. In PostgreSQL, for example, you can run a query without a table at all:

SELECT 1;

The result is a tiny table with one row and one column.

You can return text:

SELECT 'Kotomarket';

You can return the result of a calculation:

SELECT 2 + 2;

And you can give the result column a clear name right away:

SELECT 2 + 2 AS result;

A query like this does not "pull a column out of a table". It asks the database to evaluate an expression and return the result.

That is an important idea:

SELECT does not always use existing table columns. Sometimes it creates result columns on the fly as the query runs.

The simplest query against a table

When you need data from a table, the query usually looks like this:

SELECT columns
FROM table;

For example:

SELECT name, price
FROM products;

Let's break it down:

• SELECT name, price — specifies which values to show in the result;
• FROM products — specifies which table the rows come from;
• ; — marks the end of the SQL statement.

After SELECT, list the columns you need, separated by commas:

SELECT name, price, stock
FROM products;

This query will show three result columns:

• name;
• price;
• stock.

And if you want to see every column in the table, you can use *:

SELECT *
FROM products;

The asterisk means:

Show every column in the table.

That is convenient when you are exploring a table for the first time, but in real-world queries it is better not to rely on *.

Why?

Because SELECT * can return data you do not need: technical fields, long text descriptions, audit timestamps, and internal identifiers. On top of that, if the table's schema changes, the result of SELECT * changes with it.

While you are exploring and learning, * is fine.
For a well-designed query, it is better to list the columns you need explicitly.

Aliases: naming a column in the result

Sometimes column names that work well for developers are not especially friendly to other readers.

For example, a table may have a column called:

name

But in a report you would rather see:

product

That is what an alias is for — it gives a column a temporary name in the query result.

SELECT name AS product, price AS cost
FROM products;

You can read AS as "label this as":

name AS product

In other words:

Take the value from the name column, but label that column product in the result.

One important point: an alias does not rename the column in the table itself.
It changes only the column label in the result of that query.

The products table is not changed by a query like this.
The name column was there before, and it remains there.

An alias does not alter the archive.
It simply adds a label to the terminal output.

The data does not change: you are simply labelling the column so that the output looks more like a shop display than a warehouse invoice from 2024.

The archive reads a query differently from how you write it

You write a query top to bottom: SELECT first, then FROM. The archive runs it in a different — logical — order:

FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT

First the database decides where to pull rows from, then filters them, groups them and filters the groups (you’ll meet GROUP BY and HAVING in module 3) — and only after that computes the SELECT list itself, aliases included. Sorting and trimming the result come last.

Hence the classic asked in almost every interview: a SELECT alias isn’t visible in WHERE — at filtering time it doesn’t exist yet:

-- Won't work: at the WHERE step the sale_price alias doesn't exist yet
SELECT name, price * 0.9 AS sale_price
FROM products
WHERE sale_price < 3000;   -- ERROR: column "sale_price" does not exist

-- This works: repeat the expression
SELECT name, price * 0.9 AS sale_price
FROM products
WHERE price * 0.9 < 3000;

But ORDER BY sale_price will work: sorting runs after SELECT, when the alias already exists. In PostgreSQL an alias is also allowed in GROUP BY (that’s an extension, not the standard), while in WHERE it works in no major DBMS.

The order is strictly logical: it describes the meaning of the query. Physically the planner is free to reorder steps for speed, but the result must match the logical order.

A query result is a table too

When you run a query, the database returns the result as a table.

For example:

SELECT name AS product, price AS cost
FROM products
LIMIT 5;

might return:

This result table does not have to include every column from the products table.

The products table may have dozens of columns:

• id;
• name;
• category;
• price;
• stock;
• created_at;
• updated_at;
• and more.

But you asked for only two columns in the result:

SELECT name AS product, price AS cost

so that is all the terminal shows.

A query is not the table itself.
It is an instruction the database follows to assemble a temporary result.

SELECT without a table

In most teaching examples, SELECT is used together with FROM:

SELECT name
FROM products;

But in PostgreSQL, SELECT can also be used without a table.

For example:

SELECT 1;

The database returns one row containing the number 1.

You can return text:

SELECT 'Archive online';

You can perform a calculation:

SELECT 10 * 3;

You can give the result column a clear name:

SELECT 10 * 3 AS total;

The result will look roughly like this:

What is this good for?

First, it is a handy way to test simple expressions.
Second, it helps you grasp an important idea: SELECT does not merely copy columns from a table. It builds the columns in the result.

Sometimes those columns come from a table.
Sometimes they are calculated directly in the query.

QUERY: The archive does not have to open a drawer to answer a question. Sometimes the answer is already contained in the question itself.

Note: this course uses PostgreSQL. In some other database systems, the syntax for running a query without a table may differ.

Expressions in SELECT

The SELECT list can contain expressions as well as column names.

For example, the products table contains each product's price:

price

If you need to show the price with a 10% discount, you can write:

SELECT name, price, price * 0.9 AS sale_price
FROM products;

Here:

• name — an ordinary column;
• price — an ordinary column;
• price * 0.9 — a calculation;
• AS sale_price — the name of the calculated column in the result.

For every row, the database takes the price value, multiplies it by 0.9, and shows the answer in a new column called sale_price.

If the table contains data like this:

then this query:

SELECT name, price, price * 0.9 AS sale_price
FROM products;

returns a result like this:

An important detail: the sale_price column has not been added to the products table.

It exists only in the query result.

In other words, SELECT can create calculated columns in the result.

AS: a temporary label, not a change to the table

You can use AS to give an alias to:

• an ordinary column;
• a calculated expression;
• a text value;
• the result of a function.

Examples:

SELECT name AS product_name
FROM products;

SELECT price * 0.9 AS sale_price
FROM products;

SELECT 'Kotomarket' AS archive_name;

AS makes the result easier to understand.

Compare this:

SELECT price * 0.9
FROM products;

The column heading in the result may look technical and rather cryptic.

This is better:

SELECT price * 0.9 AS sale_price
FROM products;

Now it is obvious at a glance that this is the discounted price.

Sometimes you can omit AS:

SELECT name product_name
FROM products;

But for beginners, and for readable teaching code, it is better to write AS explicitly:

SELECT name AS product_name
FROM products;

That makes the query easier to read.

Aliases containing spaces

If you want to use an alias that contains spaces, you need double quotes:

SELECT price * 0.9 AS "sale price"
FROM products;

But in both teaching examples and real-world queries, it is usually more convenient to use short names without spaces:

SELECT price * 0.9 AS sale_price
FROM products;

An alias like that is simpler to reuse later in ORDER BY and easier to read in the code.

Do not mix these two up:

• single quotes '...' — for text;
• double quotes "..." — for column names, aliases, and other identifiers when they contain spaces or need their exact spelling preserved.

A text value:

SELECT 'sale price' AS label;

A quoted result-column name:

SELECT price * 0.9 AS "sale price"
FROM products;

SELECT *: a quick look at everything

The asterisk * means "all columns".

SELECT *
FROM products;

A query like this is handy when you explore a table for the first time and want to see what data it contains.

But SELECT * is like a floodlight.
It illuminates everything at once: the data you need, the data you do not need, and temporary or technical fields.

In exploratory queries, it helps you get your bearings quickly.
In real-world queries, it is better to be explicit:

SELECT name, price, stock
FROM products;

That way you control the result precisely.

Beginners often use this query:

SELECT *
FROM products;

But when all you need is the name and the price, it returns more data than necessary.

A better query is:

SELECT name, price
FROM products;

A query like that is clearer, tidier, and safer if the table's schema changes later.

A walkthrough of your first query

Let's look at the query you ran in the terminal:

SELECT name AS product, price AS cost
FROM products
LIMIT 5;

It reads like this:

From the products table, show the values in the name and price columns,
but label them product and cost in the result,
and return at most five rows.

Line by line:

SELECT name AS product, price AS cost

This builds the result columns.

FROM products

This specifies which table the data comes from.

LIMIT 5

This limits the output to five rows.

LIMIT is useful when a table is large. If the archive holds thousands or millions of products, you do not always need to return all of them at once. Sometimes a few rows are enough to see what the data looks like.

QUERY: You do not need to open the whole airlock just to check the pressure. Five rows are enough to tell whether the archive is responding correctly.

Where an alias is visible and where it is not

The rule you need here:

An alias defined in SELECT cannot be used in the WHERE clause, but it can be used in the ORDER BY clause.

So:

An example where the alias cannot be used:

SELECT name, price * 0.9 AS sale_price
FROM products
WHERE sale_price < 3000;

An example where the alias can be used:

SELECT name, price * 0.9 AS sale_price
FROM products
ORDER BY sale_price;

Once you remember the logical order, the rule feels much less arbitrary.

Common mistakes in your first SELECT queries

Mistake 1. Forgetting the comma between columns

If you meant to select two columns, this is not the query you want:

SELECT name price
FROM products;

The database treats price as an alias for name, not as a second column.

Write this instead:

SELECT name, price
FROM products;

Mistake 2. Putting FROM before SELECT

Incorrect:

FROM products
SELECT name, price;

Correct:

SELECT name, price
FROM products;

SQL clauses must appear in a specific syntactic order: SELECT first, then FROM.

In SQL's logical processing order, however, FROM comes first.

Mistake 3. Thinking that an alias changes the table

SELECT name AS product
FROM products;

This does not add a product column to the table.

The alias exists only in the result of that query.

Mistake 4. Using single quotes for an alias name

This is invalid in PostgreSQL:

SELECT price AS 'cost'
FROM products;

In PostgreSQL, single quotes are used for text strings, not for column names.

Correct:

SELECT price AS cost
FROM products;

Or, if you need an alias with a space in it:

SELECT price AS "product cost"
FROM products;

Mistake 5. Using an alias in WHERE

Incorrect:

SELECT price * 0.9 AS sale_price
FROM products
WHERE sale_price < 3000;

Correct:

SELECT price * 0.9 AS sale_price
FROM products
WHERE price * 0.9 < 3000;

Mistake 6. Always writing SELECT *

For a first look at a table, this is fine:

SELECT *
FROM products;

But for a clear, focused query, this is better:

SELECT name, price
FROM products;

It makes it immediately clear which columns you actually need.

QUERY: Level-one clearance granted. You no longer see the archive as a wall of data. You now know how to tell it exactly what shape its answer should take.