You closed module 1 with the GreenStore database loaded and verified, and with a promise: the first real query was one lesson away. Here it is. In this lesson you'll learn the most important statement in SQL, the one you'll use 90 % of the times you sit down in front of a database: SELECT. You'll see how to ask for specific columns, why SELECT * is convenient for exploring but dangerous in production, what the result set the engine hands back really is and —most important in the medium term— in what logical order PostgreSQL executes the clauses of a query, which is not the order you write them in. That last point looks theoretical today and will be the key to understanding modules 4 and 7.

1. The minimal query: SELECT ... FROM ...
2. Selecting specific columns
3. SELECT *: when yes and why not in production
4. The column order is decided by the SELECT
5. SELECT without FROM: SQL as a calculator
6. What the result set really is
7. The logical execution order of a query
8. Reading results in psql and the expanded mode \x
9. Common Mistakes and Tips
10. Exercises
11. Conclusion

A read query needs, as a minimum, two clauses:

Read it out loud backwards and you'll understand better what the engine does: "from the categories table, give me the name column".

In the relational-model jargon you saw in 01-05, SELECT performs a projection (choosing columns) and, when we add WHERE in lesson 02-03, we'll also perform a selection (choosing rows). That the keyword is called SELECT and does the projection is one of SQL's small historical inconsistencies; it's worth knowing so you don't get confused reading academic literature.

Notice you haven't told PostgreSQL how to walk the table, nor which file it's in, nor whether an index would help. You've only described the what. That's the declarative character of the language we saw in 01-01.

To ask for several columns you separate them with commas:

20 rows. Exactly the 20 you loaded in 01-06: SELECT with no filter returns all the rows of the table, and projects only the columns you've named. No cost, no stock, no active, no added_date: you didn't ask for them.

Writing details you already know from 01-03 and that apply here:

• The comma goes between columns, never after the last one. SELECT id, name, FROM products gives syntax error at or near "FROM".
• Putting one column per line isn't a whim: when the list grows to fifteen columns, you'll be glad you can add or remove one without rewriting the whole line.
• The names have to exist. If you write SELECT selling_price FROM products, PostgreSQL answers column "selling_price" does not exist. A \d products clears it up in a second.

The asterisk means "every column of the table, in the order they were defined":

It's convenient, and to explore a table you don't know it's the first thing anyone writes. The problem shows up when that * ends up inside an application, a report or a view.

Course rule: SELECT * to explore in psql; an explicit column list in any query you're going to save, version or run more than once.

There is an intermediate variant that is both common and correct: SELECT table.* in queries with several tables, to say "every column of this table". You'll see it in module 3.

The result doesn't have to respect the physical order of the table. Your list rules:

And nothing stops you from repeating a column, though it's rarely useful:

It returns three columns, two of them called id. PostgreSQL allows it; the clients and programming languages that access by name don't always. It's a hint that as soon as there are repeated names you'll need aliases, which is exactly the subject of the next lesson.

In PostgreSQL the FROM clause is optional. Without it, SELECT evaluates expressions and returns a single row:

That ?column? header means "I don't know what to call this": the expression has no name. You fix it with an alias (lesson 02-02).

Examples you'll use daily to try things out before dropping them into a big query:

Three things already visible here that will come back:

1. current_date returns the date of the day you run the query; the value above is only an example.
2. 12.50 * 1.21 gives 15.1250, with four decimals. PostgreSQL adds the scales of the operands when multiplying NUMERIC. To present money you'll have to round (lesson 02-02).
3. 3 * 12.50 * (1 - 0.05) is the calculation of the amount of line 18 of order_lines, with the numbers typed out by hand. In the next lesson you'll write it with columns instead of literals.

Dialect note: Oracle requires a FROM, which is why there you write SELECT 2+2 FROM dual;. MySQL, SQL Server, SQLite and PostgreSQL all accept SELECT without FROM.

What a query returns is called the result set and, conceptually, it's a relation: a temporary, unnamed table that exists only for as long as the query lasts. Three properties follow from that which are worth internalising today:

1. It's a table like any other. It has columns with names and types, and rows. That's why it will be usable as the source of another query (subqueries in FROM, module 7) or combined with another relation (UNION, module 3).
2. It modifies nothing. SELECT is a read-only operation: however many times you run it, the original table doesn't change. Modifying data is module 5.
3. It has no guaranteed order. This is the one that surprises people most.

On the third point: in the result of section 2 the products came out ordered by id, from 1 to 20. It looks like the table "is sorted". It isn't. That order is a side effect of the rows having been inserted one after another and PostgreSQL having read them in physical order. As soon as the table grows, rows are updated, an index comes into play or the engine uses several parallel processes, the order can change without warning and without anything going wrong.

The rule is blunt and admits no exceptions:

If the order matters, write ORDER BY. If you don't write it, you have no right to expect any particular order.

The ORDER BY clause is lesson 02-05. Until then, when in this lesson or the next you see results "ordered by id", understand that it's the order you'll probably see, not the one the engine promises you.

You write a query in one order and the engine resolves it in another. Understanding that difference is what later makes obvious things that otherwise look arbitrary (why an alias works in ORDER BY but not in WHERE, why HAVING exists on top of WHERE, why a subquery can see certain columns and not others).

The order you write it in:

The order it's logically executed in:

For now you're only using steps 1 and 3, so the diagram looks over the top. Keep it: we'll add clauses to this same scheme in every lesson of the module, and in module 4 we'll bring in GROUP BY and HAVING.

Two consequences you can already anticipate:

• Since WHERE runs before SELECT, when the time comes to filter you won't be able to use in WHERE a name invented in the SELECT: it doesn't exist yet.
• Since ORDER BY runs after SELECT, there you will be able to use it.

An important nuance: this is the logical order, the one that defines the meaning of the query. The actual execution plan the optimizer picks can be very different (reading an index, filtering while reading, stopping early), as long as the result is the same as the logical order's. Real plans are studied in module 8 with EXPLAIN.

When you run a query in psql you see something like this:

The pieces of that output:

Booleans print as t and f, not as true/false. In this course we'll write them as true/false in the result tables for readability.

When a row has many columns or long blocks of text, the output falls apart and becomes unreadable. That's what expanded mode is for:

Each row now takes up a vertical block. You can see perfectly that customer 1 has an empty referred_by_id (it's NULL: they arrived on their own) and customer 2 was referred by customer 1.

\x toggles between on and off; \x auto lets psql decide based on the terminal width, and it's the most comfortable option day to day.

Other metacommands that will save you time in this module:

Close the lesson by practising projection on the three tables you'll use most.

Who the customers are and where they're from:

The team, with its hierarchy in raw form:

There's Rosa Alcázar Vives's NULL, the only one with no manager. Seeing it as an empty cell is your first practical contact with nulls: module 4 devotes a whole lesson to them.

The status of the orders:

Notice that customer_id is a number, not a name. To know that order 10 is Camille Dubois's you have to go to customers, and that means combining two tables: which is exactly what module 3's JOIN does. Until then we'll always work with one table at a time.

• A stray comma before FROM. SELECT id, name, FROM products → syntax error at or near "FROM". The error points at FROM, but the fault is in the comma before it (the "look at the previous token" rule from 01-03).
• A missing comma between columns. SELECT name price FROM products raises no error: PostgreSQL reads price as an alias for name and returns a single column called price holding the product names. It's a silent failure; always count the columns in the result.
• column "..." does not exist. It's almost always a typo or a column that lives in another table. \d table before rewriting blindly.
• Mixing up quotes. SELECT "name" FROM products works (a lowercase identifier); SELECT 'name' FROM products returns 20 rows holding the literal text name. They aren't the same thing.
• Assuming the result comes sorted. It works today with 20 rows and fails the day the table has a million. ORDER BY or nothing.
• Leaving SELECT * in the code. Use it to explore, replace it with the column list as soon as the query is final.
• Tip: always look at the row count. If you expected 20 and psql says (0 rows), you've learned something before reading a single cell.
• Tip: test expressions without FROM. SELECT 3 * 12.50 * (1 - 0.05); validates the calculation in a second, with no table noise.
• Tip: turn on \x auto. It's the difference between reading a row of customers and wrestling with your terminal.

Write a query that returns the name, the country and the email of every supplier. Then answer: how many rows does it return and why is no filter needed to get them all?

On employees, write a query that shows the columns in this exact order: job_title, last_name, name, city. Justify why the result doesn't match the order the columns are defined in within the table.

Without using any table, calculate these three things with SELECT and explain the result:

1. The price with VAT (21 %) of product 15 (Ceremonial matcha green tea, €22.00).
2. The amount of line 27 of order_lines: 8 units at €1.95 with a discount of 0.15.
3. The gross margin of product 6 (price 18.90, cost 9.50).

Solution 1

It returns 5 rows. A query with no WHERE discards no rows: step 2 of the logical order simply doesn't exist, so everything that comes out of FROM reaches SELECT. Notice that supplier 5 shows up even though it has active = FALSE: nothing excludes it. Filtering it out will be the job of lesson 02-03.

Solution 2

The reasoning: the table defines the columns as id, name, last_name, job_title, manager_id, salary, hire_date, city, but only SELECT * uses that order. When you list columns, the result's order is your list's, because the projection builds a new relation with the shape you decide. It's the same reason you can omit columns or repeat them.

Solution 3

Three observations about the reasoning:

1. 22.00 * 1.21 gives 26.6200, not 26.62. When multiplying two NUMERIC values, PostgreSQL adds the scales: two decimals by two decimals give four. For a report you'll have to round.
2. The discount is a fraction, so 0.15 is 15 % and the factor applied is (1 - 0.15) = 0.85. Writing 8 * 1.95 * 0.15 would give the discount, not the amount. It's the most frequent misreading with this database.
3. The subtraction 18.90 - 9.50 keeps two decimals, because in addition and subtraction the result's scale is the larger of the two, not the sum.

(Yes, we've used AS ahead of time: without it the three columns would all be called ?column?. It's the first clause of the next lesson.)

You now know how to interrogate a table:

• The minimal query is SELECT columns FROM table;: FROM says where the rows come from and SELECT which columns are projected.
• SELECT * is for exploring, but in production it's replaced by the explicit column list: a stable contract, less traffic and better execution plans.
• The column order of the result is decided by you with your list, not by the table's definition.
• SELECT without FROM turns PostgreSQL into a calculator for testing expressions before working them into a query.
• The result set is a temporary, read-only relation with no guaranteed order: if the order matters, ORDER BY.
• You know the logical execution order FROM → WHERE → SELECT → ORDER BY → LIMIT, which we'll complete in every lesson of this module.
• You can read psql's output, count rows and use expanded mode \x when the rows are wide.

In the next lesson, Aliases, Expressions and Calculated Columns, you'll stop merely returning what's stored and start computing: prices with VAT, margins, percentage margins and the amount of an order line, the expression that will stay with you throughout the course. Along the way you'll give those columns decent names with AS and discover, thanks to the logical order you've just learned, why an alias works in some places and not in others.