Catalog My learning Blog
PostgreSQL Course: Beginner to Production in 30 Days · Phase 1 · Lesson 3 · 6 min read

PostgreSQL SELECT Queries for Beginners

On this page
  1. SELECT basics: six questions about one table
  2. "Who works here?"
  3. "Which engineers earn over 90,000?"
  4. "Whose hire date is missing?"
  5. "Who are the three highest earners?"
  6. "Show me page 2"
  7. "Which departments do we have, and who's the top earner in each?"
  8. Why you can't filter on an alias
  9. Check yourself

SELECT basics: six questions about one table

Most of the SQL you'll write is SELECT. Someone asks a question, and you turn it into a query. This lesson works through six typical questions about one small table, and each one teaches a piece of the statement.

Create the table in your training_db and load the sample rows. Day 5 covers CREATE TABLE properly, so just run it for now.

SQL
CREATE TABLE employees (
  id          int GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  first_name  text NOT NULL,
  last_name   text NOT NULL,
  department  text,
  salary      numeric(10,2),
  hire_date   date,
  email       text
);

INSERT INTO employees (first_name, last_name, department, salary, hire_date, email) VALUES
  ('Ana',  'Silva',   'Engineering',  95000, '2019-03-14', 'ana.silva@example.com'),
  ('Ben',  'Ortiz',   'Sales',        61000, '2021-07-01', 'ben.ortiz@example.com'),
  ('Chen', 'Wu',      'Engineering', 112000, '2016-11-23', 'chen.wu@example.com'),
  ('Dana', 'Kaur',    'Marketing',    72000, '2022-01-10', 'dana.kaur@example.com'),
  ('Eli',  'Novak',   'Sales',        58000, '2023-05-30', 'eli.novak@example.com'),
  ('Fay',  'Adeyemi', 'Engineering',  88000, NULL,         'fay.adeyemi@example.com');

"Who works here?"

SELECT * returns every column. It's fine for poking around in psql. In application code, name the columns you need. The query then says what it depends on, fetches less data, and doesn't change shape when someone adds a column next month.

SQL
SELECT first_name, last_name, department FROM employees;

You can also compute values and name them with AS:

SQL
SELECT first_name || ' ' || last_name AS full_name,
       round(salary / 12, 2) AS monthly_salary
FROM employees;

"Which engineers earn over 90,000?"

WHERE keeps only the rows where a condition is true. Combine conditions with AND, OR and NOT:

SQL
SELECT first_name, salary
FROM employees
WHERE department = 'Engineering' AND salary > 90000;

That returns Ana and Chen. A few shortcuts save typing:

SQL
SELECT * FROM employees WHERE salary BETWEEN 60000 AND 90000;    -- both ends included
SELECT * FROM employees WHERE department IN ('Sales', 'Marketing');
SELECT * FROM employees WHERE email ILIKE '%ORTIZ%';             -- case-insensitive
SELECT * FROM employees WHERE hire_date >= CURRENT_DATE - INTERVAL '5 years';

In LIKE and ILIKE patterns, % matches any run of characters (including none) and _ matches exactly one. LIKE is case-sensitive; ILIKE is the PostgreSQL version that ignores case.

Watch out when you mix AND with OR. AND binds tighter, so this query doesn't mean what it looks like:

SQL
-- Returns every Sales employee, plus engineers over 100,000
SELECT * FROM employees
WHERE department = 'Sales' OR department = 'Engineering' AND salary > 100000;

If you meant "Sales or Engineering, and over 100,000", add parentheses around the OR. When you mix the two, always use parentheses, even when you think you don't need them.

"Whose hire date is missing?"

Fay has no hire date. Try the obvious query:

SQL
SELECT * FROM employees WHERE hire_date = NULL;   -- returns nothing

It returns zero rows. NULL means "unknown", and comparing anything to unknown gives unknown, not true. WHERE only keeps rows where the condition is true. Use the special tests instead:

SQL
SELECT * FROM employees WHERE hire_date IS NULL;
SELECT * FROM employees WHERE hire_date IS NOT NULL;

The same logic catches people with <>. WHERE department <> 'Sales' also skips any row whose department is NULL.

"Who are the three highest earners?"

Without ORDER BY, PostgreSQL returns rows in whatever order is cheapest. That order can change between runs, so never rely on it. Sort explicitly, then take the top three with LIMIT:

SQL
SELECT first_name, salary
FROM employees
ORDER BY salary DESC
LIMIT 3;

You can sort by several columns, and decide where NULLs go. By default NULLs come last when sorting ascending and first when sorting descending. That's why "most recent hires" needs a fix, or Fay's unknown date comes out on top:

SQL
SELECT first_name, hire_date
FROM employees
ORDER BY hire_date DESC NULLS LAST
LIMIT 3;

LIMIT without ORDER BY gives you "some three rows", not "the top three". Pair them every time.

"Show me page 2"

OFFSET skips rows before LIMIT takes its share. It's the simplest way to page:

SQL
SELECT id, first_name FROM employees ORDER BY id LIMIT 2 OFFSET 2;

It has a cost on big tables. To skip 100,000 rows, PostgreSQL still has to read and throw away 100,000 rows, so deep pages get slower and slower. The faster pattern is keyset pagination: remember the last id you showed and start after it.

SQL
SELECT id, first_name FROM employees WHERE id > 2 ORDER BY id LIMIT 2;

With an index on id, that jumps straight to the right spot however deep you go. Day 11 explains why.

"Which departments do we have, and who's the top earner in each?"

DISTINCT removes duplicate rows from the result:

SQL
SELECT DISTINCT department FROM employees;

PostgreSQL adds a handy extension, DISTINCT ON. It keeps the first row for each value, where "first" is decided by your ORDER BY. That gives you "one row per group" in a single query:

SQL
SELECT DISTINCT ON (department) department, first_name, salary
FROM employees
ORDER BY department, salary DESC;

The ORDER BY must start with the DISTINCT ON columns. The rest of the ORDER BY decides which row wins, here the highest salary.

Why you can't filter on an alias

One last thing confuses everyone at first. This fails:

SQL
SELECT salary / 12 AS monthly FROM employees WHERE monthly > 7000;
-- ERROR: column "monthly" does not exist

You write SELECT first, but PostgreSQL processes it near the end. WHERE runs before the SELECT list exists, so the alias isn't defined yet. ORDER BY runs after SELECT, which is why ORDER BY monthly works fine.

The order PostgreSQL evaluates a query in. Anything defined in SELECT is only visible to ORDER BY and LIMIT.
The order PostgreSQL evaluates a query in. Anything defined in SELECT is only visible to ORDER BY and LIMIT.

Repeat the expression in WHERE (WHERE salary / 12 > 7000) or use a subquery, which Day 9 covers.

Check yourself

Without ORDER BY, row order isn't defined, so LIMIT takes whichever ten rows come out first. Add an ORDER BY on a unique column, or a combination that is unique.

For those rows manager_id is NULL, so the comparison is unknown and WHERE drops them. Write WHERE manager_id <> 5 OR manager_id IS NULL, or use manager_id IS DISTINCT FROM 5.

Switch to keyset pagination: pass the last id from the previous page and query WHERE id > :last_id ORDER BY id LIMIT 50. With an index on id, every page costs about the same.

WHERE is evaluated before the SELECT list, so the alias full_name doesn't exist yet when the filter runs. Repeat the expression in WHERE, or wrap the query in a subquery or CTE and filter on the alias outside it. ORDER BY is the exception: it runs last, so it can use aliases.

Both return the same rows, but the range condition can use an index on hire_date, while wrapping the column in a function forces PostgreSQL to compute it for every row. A half-open range with < on the upper bound also handles timestamps correctly, including times late on December 31.