SELECT Queries

SELECT reads rows from a table. Every example here runs against one small employees table; create it first in a scratch database (createdb sqldemo).

-- setup.sql
CREATE TABLE employees (
    id      SERIAL PRIMARY KEY,
    name    TEXT NOT NULL,
    dept    TEXT NOT NULL,
    salary  INTEGER NOT NULL,
    bonus   INTEGER           -- NULL means "no bonus recorded"
);

INSERT INTO employees (name, dept, salary, bonus) VALUES
    ('Ana',   'Eng',   95000, 5000),
    ('Ben',   'Eng',   72000, NULL),
    ('Cara',  'Sales', 58000, 9000),
    ('Dev',   'Sales', 61000, NULL),
    ('Eli',   'Ops',   52000, 1000),
    ('Fay',   'Eng',   88000, 3000);

$ psql -d sqldemo -f setup.sql
CREATE TABLE
INSERT 0 6

Choosing Columns

List the columns you want, in the order you want them.

-- columns.sql
SELECT name, dept, salary
FROM employees;

$ psql -d sqldemo -f columns.sql
 name | dept  | salary 
------+-------+--------
 Ana  | Eng   |  95000
 Ben  | Eng   |  72000
 Cara | Sales |  58000
 Dev  | Sales |  61000
 Eli  | Ops   |  52000
 Fay  | Eng   |  88000
(6 rows)

SELECT * would also return id and bonus. Naming columns keeps the result stable when someone adds a column to the table later, and avoids shipping data you never use.

Aliases and Computed Columns

An expression in the select list becomes a new column; AS names it.

-- aliases.sql
SELECT name,
       salary / 12      AS monthly,
       salary + COALESCE(bonus, 0) AS total_pay,
       upper(dept)      AS "Department"
FROM employees
ORDER BY total_pay DESC;

$ psql -d sqldemo -f aliases.sql
 name | monthly | total_pay | Department 
------+---------+-----------+------------
 Ana  |    7916 |    100000 | ENG
 Fay  |    7333 |     91000 | ENG
 Ben  |    6000 |     72000 | ENG
 Cara |    4833 |     67000 | SALES
 Dev  |    5083 |     61000 | SALES
 Eli  |    4333 |     53000 | OPS
(6 rows)

salary / 12 divides two integers, so the result is truncated: 95000 / 12 is 7916.67, but PostgreSQL prints 7916. COALESCE(bonus, 0) turns a missing bonus into zero; without it, salary + NULL would be NULL for Ben and Dev. The alias in double quotes keeps its capital letter, and ORDER BY can use an alias because it runs after SELECT.

DISTINCT

DISTINCT removes duplicate rows from the result.

-- distinct.sql
SELECT DISTINCT dept
FROM employees
ORDER BY dept;

$ psql -d sqldemo -f distinct.sql
 dept  
-------
 Eng
 Ops
 Sales
(3 rows)

Six employees collapse into three departments. DISTINCT applies to the whole selected row, so SELECT DISTINCT dept, name would bring all six rows back.

WHERE and the NULL Trap

WHERE keeps only rows whose condition is true. That word “true” matters as soon as NULL is involved.

-- null_trap.sql
SELECT count(*) AS eq_null   FROM employees WHERE bonus = NULL;
SELECT count(*) AS is_null   FROM employees WHERE bonus IS NULL;
SELECT count(*) AS not_5000  FROM employees WHERE bonus <> 5000;

$ psql -d sqldemo -f null_trap.sql
 eq_null 
---------
       0
(1 row)

 is_null 
---------
       2
(1 row)

 not_5000 
----------
        3
(1 row)

bonus = NULL is never true, not even for the rows whose bonus is NULL, because comparing anything with an unknown value gives unknown. IS NULL is the test that works. The third query surprises people too: six employees, one with a 5000 bonus, yet only three rows count as “not 5000”. The two NULL bonuses are unknown, not “different”, so they are dropped.

Combining Conditions

AND and OR combine conditions; parentheses decide which binds first.

-- where.sql
SELECT name, dept, salary
FROM employees
WHERE dept = 'Eng' AND salary > 80000
   OR dept = 'Ops';

$ psql -d sqldemo -f where.sql
 name | dept | salary 
------+------+--------
 Ana  | Eng  |  95000
 Eli  | Ops  |  52000
 Fay  | Eng  |  88000
(3 rows)

AND binds tighter than OR, so this reads as “(Eng and over 80000) or anyone in Ops”. Eli earns 52000 but still appears. The filtering lesson covers BETWEEN, IN, LIKE, and more precedence traps.

CASE: Choosing a Value Per Row

CASE returns a different value depending on a condition. The simple form compares one expression with a list of values; the searched form tests a full condition in each WHEN.

-- case.sql
SELECT name, salary,
       CASE dept WHEN 'Eng' THEN 'engineering'
                 WHEN 'Sales' THEN 'sales' END AS team,
       CASE WHEN salary >= 90000 THEN 'top'
            WHEN salary >= 60000 THEN 'mid'
            ELSE 'entry' END AS band
FROM employees
ORDER BY id;

$ psql -d sqldemo -f case.sql
 name | salary |    team     | band  
------+--------+-------------+-------
 Ana  |  95000 | engineering | top
 Ben  |  72000 | engineering | mid
 Cara |  58000 | sales       | entry
 Dev  |  61000 | sales       | mid
 Eli  |  52000 |             | entry
 Fay  |  88000 | engineering | mid
(6 rows)

team is a simple CASE on dept. Eli is in Ops, which no WHEN lists, and there’s no ELSE, so the result is NULL, shown as the blank. band is a searched CASE: Ana’s 95000 satisfies both >= 90000 and >= 60000, but only the first matching WHEN counts, so she is top. Two traps follow from those rules.

-- case_traps.sql
SELECT name, salary, bonus,
       CASE WHEN salary >= 60000 THEN 'mid'
            WHEN salary >= 90000 THEN 'top'
            ELSE 'entry' END AS band_reversed,
       CASE bonus WHEN NULL THEN 'none' ELSE 'yes' END AS bonus_simple,
       CASE WHEN bonus IS NULL THEN 'none' ELSE 'yes' END AS bonus_searched
FROM employees
ORDER BY id;

$ psql -d sqldemo -f case_traps.sql
 name | salary | bonus | band_reversed | bonus_simple | bonus_searched 
------+--------+-------+---------------+--------------+----------------
 Ana  |  95000 |  5000 | mid           | yes          | yes
 Ben  |  72000 |       | mid           | yes          | none
 Cara |  58000 |  9000 | entry         | yes          | yes
 Dev  |  61000 |       | mid           | yes          | none
 Eli  |  52000 |  1000 | entry         | yes          | yes
 Fay  |  88000 |  3000 | mid           | yes          | yes
(6 rows)

• Order matters. With the conditions swapped, >= 60000 catches Ana first, so nobody is ever top. Put the most specific condition first.
• CASE x WHEN NULL never matches. The simple form tests bonus = NULL, which is never true, so Ben and Dev, who have no bonus, get yes. Test for NULL with the searched form and IS NULL, as bonus_searched does.

ORDER BY

Sort by one or more columns; each gets its own direction.

-- order.sql
SELECT name, dept, bonus
FROM employees
ORDER BY dept, bonus DESC;

$ psql -d sqldemo -f order.sql
 name | dept  | bonus 
------+-------+-------
 Ben  | Eng   |      
 Ana  | Eng   |  5000
 Fay  | Eng   |  3000
 Eli  | Ops   |  1000
 Dev  | Sales |      
 Cara | Sales |  9000
(6 rows)

Rows sort by dept first, then by bonus descending inside each department. PostgreSQL treats NULL as larger than any value, so with DESC Ben’s and Dev’s missing bonuses come first. Add NULLS LAST after DESC to push them to the bottom.

LIMIT and OFFSET

LIMIT caps the number of rows; OFFSET skips rows first. Together they page through a sorted result.

-- page.sql
SELECT name, salary
FROM employees
ORDER BY salary DESC
LIMIT 2 OFFSET 2;

$ psql -d sqldemo -f page.sql
 name | salary 
------+--------
 Ben  |  72000
 Dev  |  61000
(2 rows)

This is “page 2” with two rows per page: the two highest earners are skipped. Without ORDER BY, the rows on each page would be whatever order the plan happened to produce.

Why Large OFFSETs Are Slow

OFFSET still produces every skipped row. Measure it on 200,000 generated rows.

-- offset_cost.sql
CREATE TABLE events AS
SELECT g AS id, 'event ' || g AS label
FROM generate_series(1, 200000) AS g;
ALTER TABLE events ADD PRIMARY KEY (id);
ANALYZE events;

EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT * FROM events ORDER BY id LIMIT 3 OFFSET 150000;

EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT * FROM events WHERE id > 150000 ORDER BY id LIMIT 3;

$ psql -d sqldemo -f offset_cost.sql
SELECT 200000
ALTER TABLE
ANALYZE
                                QUERY PLAN                                 
---------------------------------------------------------------------------
 Limit (actual rows=3 loops=1)
   ->  Index Scan using events_pkey on events (actual rows=150003 loops=1)
(2 rows)

                              QUERY PLAN                              
----------------------------------------------------------------------
 Limit (actual rows=3 loops=1)
   ->  Index Scan using events_pkey on events (actual rows=3 loops=1)
         Index Cond: (id > 150000)
(3 rows)

Both queries return the same three rows. The OFFSET plan’s index scan reports rows=150003: it walked 150,000 rows just to discard them. The second query is keyset pagination: it remembers the last id seen and lets the index start there, reading only 3 rows. Page 50,000 of the offset version costs 50,000 times more than page 1.

SELECT Without FROM

PostgreSQL can evaluate expressions with no table at all, which makes it a handy calculator.

-- no_from.sql
SELECT 7 / 2 AS int_div, 7 / 2.0 AS num_div, 7 % 2 AS remainder, 'ab' || 'cd' AS joined;

$ psql -d sqldemo -f no_from.sql
 int_div |      num_div       | remainder | joined 
---------+--------------------+-----------+--------
       3 | 3.5000000000000000 |         1 | abcd
(1 row)

7 / 2 is 3, because both operands are integers. Writing one side as 2.0 makes it a numeric division, which PostgreSQL prints with sixteen decimal places unless you round it. This is the same truncation aliases.sql hit with salary / 12.

Practice

1. List the names and salaries of everyone outside Eng, highest salary first.

-- practice_not_eng.sql
SELECT name, salary
FROM employees
WHERE dept <> 'Eng'
ORDER BY salary DESC;

$ psql -d sqldemo -f practice_not_eng.sql
 name | salary 
------+--------
 Dev  |  61000
 Cara |  58000
 Eli  |  52000
(3 rows)

2. Fix aliases.sql so the monthly salary keeps two decimal places.

-- practice_monthly.sql
SELECT name, round(salary / 12.0, 2) AS monthly
FROM employees
ORDER BY monthly DESC
LIMIT 3;

$ psql -d sqldemo -f practice_monthly.sql
 name | monthly 
------+---------
 Ana  | 7916.67
 Fay  | 7333.33
 Ben  | 6000.00
(3 rows)

3. Find everyone whose bonus is not 5000, including those with no bonus recorded.

-- practice_not_5000.sql
SELECT name, bonus
FROM employees
WHERE bonus IS DISTINCT FROM 5000
ORDER BY name;

$ psql -d sqldemo -f practice_not_5000.sql
 name | bonus 
------+-------
 Ben  |      
 Cara |  9000
 Dev  |      
 Eli  |  1000
 Fay  |  3000
(5 rows)

4. Return page 3 of employees sorted by name, two per page, using LIMIT and OFFSET.

-- practice_page3.sql
SELECT name FROM employees ORDER BY name LIMIT 2 OFFSET 4;

$ psql -d sqldemo -f practice_page3.sql
 name 
------
 Eli
 Fay
(2 rows)