SELECT, WHERE, ORDER BY, and LIMIT

SELECT reads rows. Every query is built from a few clauses, and they run in a fixed logical order that explains most beginner errors.

Order	Clause	Purpose
1	`FROM`	pick the table
2	`WHERE`	keep rows that match
3	`GROUP BY`	group rows
4	`HAVING`	keep groups that match
5	`SELECT`	compute the output columns
6	`DISTINCT`	remove duplicate rows
7	`ORDER BY`	sort
8	`LIMIT`	cut the result

You write SELECT first, but it runs after WHERE. This is why a column alias defined in SELECT cannot be used in WHERE.

Example 1: choose columns

SQL

SELECT name, salary FROM employees;

Output

  name  | salary 
--------+--------
 Meera  | 250000
 Ravi   | 180000
 Kiran  | 150000
 Neha   | 150000
 Arjun  | 110000
 Divya  | 160000
 Sameer |  90000
 Pooja  |  85000
 Vikram | 120000
 Anita  |  80000
 Rohan  |  95000
 Tara   |  70000
(12 rows)

Avoid SELECT * in application code: it returns columns you do not need and breaks when columns are added.

Example 2: filter with WHERE

SQL

SELECT name, salary
FROM employees
WHERE salary > 150000;

Output

 name  | salary 
-------+--------
 Meera | 250000
 Ravi  | 180000
 Divya | 160000
(3 rows)

Example 3: combine conditions

AND binds tighter than OR. Use parentheses to say what you mean.

SQL

SELECT name, dept_id, salary
FROM employees
WHERE dept_id = 1 AND (salary >= 180000 OR hired_on >= '2021-01-01');

Output

 name  | dept_id | salary 
-------+---------+--------
 Meera |       1 | 250000
 Ravi  |       1 | 180000
 Arjun |       1 | 110000
(3 rows)

Example 4: IN and BETWEEN

BETWEEN includes both ends.

SQL

SELECT name, salary
FROM employees
WHERE dept_id IN (2, 3)
  AND salary BETWEEN 85000 AND 120000;

Output

  name  | salary 
--------+--------
 Sameer |  90000
 Pooja  |  85000
 Vikram | 120000
(3 rows)

Example 5: pattern matching with LIKE

% matches any run of characters, _ matches exactly one. ILIKE (PostgreSQL) ignores case.

SQL

SELECT name FROM employees WHERE name LIKE '%a';

Output

 name  
-------
 Meera
 Neha
 Divya
 Pooja
 Anita
 Tara
(6 rows)

SQL

SELECT name FROM employees WHERE name LIKE '_a%';

Output

  name  
--------
 Ravi
 Sameer
 Tara
(3 rows)

Example 6: sort with ORDER BY

Without ORDER BY, row order is not guaranteed, even if it looks stable. Sort by several columns, each with its own direction.

SQL

SELECT name, dept_id, salary
FROM employees
WHERE dept_id = 1
ORDER BY salary DESC, name ASC;

Output

 name  | dept_id | salary 
-------+---------+--------
 Meera |       1 | 250000
 Ravi  |       1 | 180000
 Kiran |       1 | 150000
 Neha  |       1 | 150000
 Arjun |       1 | 110000
(5 rows)

Example 7: LIMIT and OFFSET

SQL

SELECT name, salary
FROM employees
ORDER BY salary DESC
LIMIT 3;

Output

 name  | salary 
-------+--------
 Meera | 250000
 Ravi  | 180000
 Divya | 160000
(3 rows)

OFFSET skips rows, which gives page 2 of a sorted list. Large offsets are slow because the database still reads and discards the skipped rows. See fixing slow queries for keyset pagination.

SQL

SELECT name, salary
FROM employees
ORDER BY salary DESC, id
LIMIT 3 OFFSET 3;

Output

  name  | salary 
--------+--------
 Kiran  | 150000
 Neha   | 150000
 Vikram | 120000
(3 rows)

Example 8: DISTINCT

DISTINCT removes duplicate rows from the result.

SQL

SELECT DISTINCT status FROM orders ORDER BY status;

Output

  status   
-----------
 cancelled
 delivered
 placed
 shipped
(4 rows)

SQL

SELECT DISTINCT city, status
FROM customers c
JOIN orders o ON o.customer_id = c.id
ORDER BY city, status;

Output

   city    |  status   
-----------+-----------
 Bengaluru | delivered
 Bengaluru | placed
 Bengaluru | shipped
 Delhi     | delivered
 Mumbai    | cancelled
 Mumbai    | delivered
 Pune      | placed
(7 rows)

Example 9: expressions and aliases

Columns can be computed. AS gives the result column a name.

SQL

SELECT name,
       salary,
       salary / 12 AS monthly,
       salary * 0.10 AS bonus
FROM employees
WHERE id <= 3;

Output

 name  | salary | monthly |  bonus   
-------+--------+---------+----------
 Meera | 250000 |   20833 | 25000.00
 Ravi  | 180000 |   15000 | 18000.00
 Kiran | 150000 |   12500 | 15000.00
(3 rows)

salary / 12 is integer division because both sides are integers. Use salary / 12.0 if you need decimals.

Example 10: you cannot use an alias in WHERE

WHERE runs before SELECT, so the alias does not exist yet.

SQL

SELECT name, salary / 12 AS monthly
FROM employees
WHERE monthly > 10000;

Output

ERROR:  column "monthly" does not exist
LINE 3: WHERE monthly > 10000;
              ^

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

SQL

SELECT name, monthly
FROM (SELECT name, salary / 12 AS monthly FROM employees) e
WHERE monthly > 10000;

Output

 name  | monthly 
-------+---------
 Meera |   20833
 Ravi  |   15000
 Kiran |   12500
 Neha  |   12500
 Divya |   13333
(5 rows)

ORDER BY runs after SELECT, so it can use the alias.

Summary

Clauses run as FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY, LIMIT.
Use parentheses whenever AND and OR are mixed.
Add a tie-breaker column to ORDER BY when paging so pages are stable.