Basic Querying and Filtering

You write a SELECT with the columns first. The database does not think that way. It starts with the table, narrows the rows, and only then decides what to show.

Once you see the real order, a whole class of confusing errors stops being confusing.

The shape of a query#

SELECT   full_name, city          -- which columns to return
FROM     customers                -- which table to read
WHERE    country = 'India'        -- which rows to keep
ORDER BY full_name                -- how to sort the result
LIMIT    10;                      -- how many rows to return at most

 full_name  |   city
------------+-----------
 Aisha Khan | Mumbai
 Farhan Ali | Bengaluru
(2 rows)

Strings go in single quotes. Double quotes mean an identifier (a table or column name). WHERE country = "India" fails with column "India" does not exist, because PostgreSQL read it as a column.

The order the database evaluates clauses#

This is the diagram worth keeping on your wall.

 1. FROM       which tables supply the rows
 2. WHERE      discard rows that fail the filter
 3. GROUP BY   collect remaining rows into groups
 4. HAVING     discard groups that fail the filter
 5. SELECT     compute the output columns (aliases are created here)
 6. DISTINCT   remove duplicate output rows
 7. ORDER BY   sort
 8. LIMIT      cut the result off

Three consequences follow directly.

You cannot use a SELECT alias in WHERE. WHERE runs at step 2, the alias does not exist until step 5.

SELECT quantity * unit_price AS line_total
FROM order_items
WHERE line_total > 100;          -- ERROR: column "line_total" does not exist

Repeat the expression, or wrap the query in a subquery.

SELECT quantity * unit_price AS line_total
FROM order_items
WHERE quantity * unit_price > 100;

You can use an alias in ORDER BY. That runs at step 7, after the alias exists.

WHERE filters rows before grouping, HAVING filters groups after. The next module relies on this.

Choosing columns and computing values#

SELECT * returns every column. It is fine in a terminal and a poor habit in application code, because adding a column later changes what your code receives, and you pay to move data you never use.

Name the columns you need. Expressions work anywhere a column can:

SELECT product_id, quantity, unit_price,
       quantity * unit_price AS line_total
FROM order_items
WHERE order_id = 6
ORDER BY product_id;

 product_id | quantity | unit_price | line_total
------------+----------+------------+------------
          2 |        1 |      29.50 |      29.50
          3 |        2 |      45.00 |      90.00
          8 |        2 |      24.00 |      48.00

Use AS for aliases. If the alias has spaces or capitals, quote it with double quotes (AS "Line Total"). Most people just use snake_case.

Filtering with WHERE#

WHERE keeps only the rows for which the condition is TRUE. Rows where it is false, or unknown because of a NULL, are dropped.

SELECT order_id, status FROM orders WHERE status = 'pending';
SELECT name, unit_price FROM products WHERE unit_price > 100 AND stock_qty > 0;

The full set of operators gets its own lesson. At this point it is enough to know that conditions combine with AND and OR, and that comparing with NULL needs special care.

Sorting with ORDER BY#

SELECT name, unit_price FROM products ORDER BY unit_price DESC LIMIT 3;

      name       | unit_price
-----------------+------------
 Standing Desk   |     499.00
 Ergonomic Chair |     349.00
 27-inch Monitor |     289.00

You can sort by several columns. The later ones break ties in the earlier ones.

SELECT category, name, unit_price
FROM products
ORDER BY category ASC, unit_price DESC;

ASC is the default. You can sort by an alias, an expression, or an ordinal (ORDER BY 2), though ordinals break the moment someone reorders the SELECT list.

NULL sorts as larger than any value in PostgreSQL, so it comes last ascending and first descending. Override it when it matters:

SELECT full_name, phone FROM customers ORDER BY phone DESC NULLS LAST;

Text sorting follows the database's collation. Depending on the setting, 'apple' and 'Banana' may sort in a different order than you expect. Check the collation if sort order matters for users.

Removing duplicates with DISTINCT#

SELECT DISTINCT country FROM customers ORDER BY country;

 country
---------
 France
 Germany
 India
 Ireland
 Japan
 Spain
 USA
(7 rows)

Ten customers, seven countries. DISTINCT applies to the whole row of selected columns, not to the first column. SELECT DISTINCT country, city returns each unique pair.

PostgreSQL adds a useful extension, DISTINCT ON, which keeps the first row of each group according to your ORDER BY:

SELECT DISTINCT ON (customer_id) customer_id, order_id, order_date
FROM orders
ORDER BY customer_id, order_date DESC;     -- latest order per customer

 customer_id | order_id | order_date
-------------+----------+------------
           1 |       10 | 2024-05-02
           2 |        7 | 2024-04-05
           3 |       13 | 2024-06-03
           4 |        5 | 2024-03-02
 ...

It is not standard SQL. Module 4 shows the portable version using window functions.

Limiting results#

SELECT order_id, order_date FROM orders ORDER BY order_date DESC LIMIT 5;
SELECT order_id, order_date FROM orders ORDER BY order_date DESC LIMIT 5 OFFSET 5;

LIMIT without ORDER BY returns an arbitrary set of rows. It looks fine in testing and returns different rows in production. Always sort when you limit.

OFFSET skips rows, which makes simple paging easy and large offsets slow, because the database still produces and discards every skipped row. Module 7 covers the better technique.

The standard spelling is FETCH FIRST 5 ROWS ONLY. PostgreSQL accepts both. SQL Server uses TOP, Oracle older versions use ROWNUM.

Common mistakes#

Using a select alias in WHERE. Evaluation order again. Repeat the expression or use a subquery.

Using double quotes for strings. Single quotes are values. Double quotes are names.

LIMIT with no ORDER BY. You get a result, not the result.

Using DISTINCT to hide a bad join. If duplicates appear after a join, the join is probably wrong. DISTINCT hides the symptom and costs a sort or hash on every run.

SELECT * in production code. It couples your application to column order and ships data you do not need.

Practice#

1. List the name and price of every product costing between 30 and 100 inclusive, cheapest first. You should get 4 rows.
2. List the three most recently registered customers by created_at. You should see Jon Smith, Isla Murphy, Hiro Tanaka.
3. List the distinct categories in products, alphabetically. There are 6.
4. Show each order line in order 9 with a computed line_total, then try to filter on line_total using its alias. Read the error, then fix it two ways.
5. Use DISTINCT ON to find the cheapest product in each category.