PostgreSQL SELECT Statement: Complete Guide with WHERE, ORDER BY & LIMIT

The SELECT statement is the foundation of SQL. It’s how you retrieve data from your database. Whether you’re pulling a single record or analyzing millions of rows, you’ll use SELECT constantly.

This guide teaches you SELECT from the ground up, starting with the simplest queries and progressing to advanced patterns. By the end, you’ll write efficient queries to answer almost any data question.

What You’ll Learn

By finishing this guide, you’ll understand:

  • How SELECT retrieves data from tables
  • The WHERE clause and how to filter results
  • DISTINCT for removing duplicates
  • ORDER BY for sorting results
  • LIMIT and OFFSET for pagination
  • How to combine all these clauses effectively
  • Performance implications of each clause

Why SELECT Matters

You might think: “SELECT is simple—just get the data, right?” It’s true that SELECT is conceptually straightforward, but there’s a critical difference between “working” queries and “good” queries:

  • Working queries return the right data but might scan every row in a massive table
  • Good queries return the same data 100 times faster by using WHERE clauses effectively

SELECT is where performance happens. A slow SELECT query can bring down your entire application. Understanding this statement well now will pay dividends forever.

Understanding SELECT Fundamentals

Before we write your first query, let’s understand what SELECT does.

The Basic Concept

A SELECT statement is a question you ask the database:

"Show me the first_name and salary columns
from the employees table
where the salary is greater than 80,000"

That’s all SELECT does—it answers questions about your data.

SELECT Basic Syntax

Here’s the simplest possible SELECT:

SELECT * FROM employees;

Breaking it down:

  • SELECT * — Get all columns
  • FROM employees — From the employees table
  • The semicolon ; — Ends the statement

This returns every row and every column from the employees table.

Expected result (30 rows):

 emp_id | first_name | last_name |        email         | hire_date  | dept_id | salary
--------+------------+-----------+---------------------+------------+---------+----------
      1 | James      | Wilson    | james.wilson@co...  | 2020-01-15 |       1 | 75000.00
      2 | Mary       | Johnson   | mary.johnson@co...  | 2019-03-20 |       1 | 78000.00
      3 | Robert     | Smith     | robert.smith@co...  | 2021-05-10 |       1 | 72000.00
    ... (27 more rows)
(30 rows)

Select Specific Columns

Usually you don’t want all columns—you want specific ones:

SELECT first_name, salary FROM employees;

Result:

 first_name | salary
------------+----------
 James      | 75000.00
 Mary       | 78000.00
 Robert     | 72000.00
 Patricia   | 76000.00
 Michael    | 70000.00
(5 rows shown, 30 total)

Notice the output now has only two columns instead of seven. This is more efficient—you’re only asking the database for what you need.

Add an Alias for Clarity

Make columns more readable with AS:

SELECT first_name AS name, salary AS annual_pay FROM employees;

Result:

      name       | annual_pay
-----------------+----------
 James           | 75000.00
 Mary            | 78000.00
 Robert          | 72000.00
(30 rows)

The column headers now show “name” and “annual_pay” instead of the raw column names. This is especially useful when doing calculations or concatenations.

Filtering Data with WHERE

SELECT returns everything by default. The WHERE clause lets you ask specific questions.

WHERE Basic Syntax

SELECT first_name, salary FROM employees WHERE salary > 80000;

Breaking it down:

  • SELECT first_name, salary — Get these columns
  • FROM employees — From employees table
  • WHERE salary > 80000 — Only rows where salary is greater than 80,000

Result:

 first_name | salary
------------+----------
 David      | 105000.00
 Jennifer   | 102000.00
 Richard    | 98000.00
 Barbara    | 95000.00
 Charles    | 92000.00
 Susan      | 99000.00
 Joseph     | 96000.00
(7 rows)

Seven employees earn more than $80,000. Without the WHERE clause, you’d get all 30 employees.

WHERE Operators

PostgreSQL supports many comparison operators:

Operator Meaning Example
= Equal to salary = 75000
!= or <> Not equal to salary != 75000
> Greater than salary > 80000
>= Greater than or equal salary >= 80000
< Less than salary < 70000
<= Less than or equal salary <= 70000
BETWEEN Within a range salary BETWEEN 70000 AND 80000
IN Matches any in list dept_id IN (1, 2, 3)
LIKE Pattern matching first_name LIKE 'J%'
IS NULL Null value check manager_id IS NULL
IS NOT NULL Non-null value manager_id IS NOT NULL

BETWEEN for Ranges

Find employees with salaries between $70,000 and $80,000:

SELECT first_name, salary FROM employees
WHERE salary BETWEEN 70000 AND 80000;

Result:

 first_name | salary
------------+----------
 James      | 75000.00
 Mary       | 78000.00
 Robert     | 72000.00
 Patricia   | 76000.00
(4 rows)

IN for Multiple Values

Find employees in specific departments:

SELECT first_name, dept_id FROM employees
WHERE dept_id IN (1, 2);

Result:

 first_name | dept_id
------------+---------
 James      |       1
 Mary       |       1
 Robert     |       1
 Patricia   |       1
 Michael    |       1
 Linda      |       1
 David      |       2
 Jennifer   |       2
 Richard    |       2
(9 rows)

This is cleaner than:

WHERE dept_id = 1 OR dept_id = 2;

LIKE for Pattern Matching

Find employees whose first names start with “J”:

SELECT first_name FROM employees WHERE first_name LIKE 'J%';

The % is a wildcard meaning “any characters.” Result:

 first_name
------------
 James
 Jennifer
 Joseph
 Jessica
(4 rows)

Find names containing “ar”:

SELECT first_name FROM employees WHERE first_name LIKE '%ar%';

Result:

 first_name
------------
 Barbara
 Margaret
(2 rows)

Combining Conditions with AND/OR

Find highly paid employees in the engineering department:

SELECT first_name, salary, dept_id FROM employees
WHERE salary > 90000 AND dept_id = 2;

The AND means both conditions must be true. Result:

 first_name | salary  | dept_id
------------+---------+---------
 David      | 105000  |       2
 Jennifer   | 102000  |       2
 Richard    | 98000   |       2
 Barbara    | 95000   |       2
 Charles    | 92000   |       2
 Susan      | 99000   |       2
 Joseph     | 96000   |       2
(7 rows)

Or find employees who either earn over $100,000 OR work in sales:

SELECT first_name, salary, dept_id FROM employees
WHERE salary > 100000 OR dept_id = 1;

The OR means at least one condition must be true. You get all highly paid employees plus all sales employees. Result:

 first_name | salary  | dept_id
------------+---------+---------
 James      | 75000   |       1
 Mary       | 78000   |       1
 Robert     | 72000   |       1
 Patricia   | 76000   |       1
 Michael    | 70000   |       1
 Linda      | 73000   |       1
 David      | 105000  |       2
 Jennifer   | 102000  |       2
(20 rows)

IS NULL for Missing Values

Find employees with no email address:

SELECT first_name FROM employees WHERE email IS NULL;

In our test data, all employees have emails, so this returns 0 rows. But in real data, some fields might be missing.

Removing Duplicates with DISTINCT

Suppose you want to know which departments have employees. You could do:

SELECT dept_id FROM employees;

This returns 30 rows, one for each employee:

 dept_id
---------
       1
       1
       1
       1
       1
       1
       2
       2
       2
       2
       2
       2
       2
       2
       3
       3
       3
       3
       3
       3
       4
       4
       4
       4
       4
       4
       4
       4
       4
(30 rows)

But you only care about the unique departments. Add DISTINCT:

SELECT DISTINCT dept_id FROM employees;

Result:

 dept_id
---------
       1
       2
       3
       4
(4 rows)

Four unique departments. Much cleaner.

DISTINCT on Multiple Columns

Find unique department and job combinations:

SELECT DISTINCT dept_id, first_name FROM employees LIMIT 8;

Result:

 dept_id | first_name
---------+------------
       1 | James
       1 | Mary
       1 | Robert
       1 | Patricia
       1 | Michael
       1 | Linda
       2 | David
       2 | Jennifer
(8 rows)

Each unique combination of (dept_id, first_name) appears once.

Sorting Results with ORDER BY

By default, SELECT returns rows in no particular order (usually insertion order, but that’s not guaranteed). ORDER BY lets you sort.

ORDER BY Ascending (Default)

Sort employees by salary, lowest to highest:

SELECT first_name, salary FROM employees ORDER BY salary;

Result:

 first_name | salary
------------+----------
 Paul       | 64000.00
 Anthony    | 67000.00
 Mike       | 67000.00
 Margaret   | 65000.00
 Mark       | 69000.00
 Steven     | 64000.00
 Sandra     | 71000.00
 Ashley     | 66000.00
 Matthew    | 68000.00
(10 rows shown, 30 total)

The results are sorted from lowest salary to highest.

ORDER BY Descending

Sort highest to lowest:

SELECT first_name, salary FROM employees ORDER BY salary DESC;

Result:

 first_name | salary
------------+----------
 David      | 105000.00
 Jennifer   | 102000.00
 Susan      | 99000.00
 Richard    | 98000.00
 Joseph     | 96000.00
 Barbara    | 95000.00
 Charles    | 92000.00
(10 rows shown, 30 total)

The DESC keyword means descending (high to low).

ORDER BY Multiple Columns

Sort by department first, then by salary within each department:

SELECT first_name, dept_id, salary FROM employees
ORDER BY dept_id, salary DESC;

Result:

 first_name | dept_id | salary
------------+---------+----------
 Mary       |       1 | 78000.00
 Patricia   |       1 | 76000.00
 James      |       1 | 75000.00
 Robert     |       1 | 72000.00
 Linda      |       1 | 73000.00
 Michael    |       1 | 70000.00
 David      |       2 | 105000.00
 Jennifer   |       2 | 102000.00
 Susan      |       2 | 99000.00
 Richard    |       2 | 98000.00
(10 rows shown, 30 total)

Employees are grouped by department, and within each department, they’re sorted by salary high to low.

Limiting Results with LIMIT and OFFSET

When you have millions of rows, you can’t display them all at once. LIMIT retrieves only a specific number of rows.

LIMIT for Top N Results

Get the top 5 highest-paid employees:

SELECT first_name, salary FROM employees
ORDER BY salary DESC LIMIT 5;

Result:

 first_name | salary
------------+----------
 David      | 105000.00
 Jennifer   | 102000.00
 Susan      | 99000.00
 Richard    | 98000.00
 Joseph     | 96000.00
(5 rows)

You asked for 5 rows, and you got exactly 5.

OFFSET for Pagination

Show employees 6-10:

SELECT first_name, salary FROM employees
ORDER BY salary DESC LIMIT 5 OFFSET 5;

Breaking it down:

  • LIMIT 5 — Return 5 rows
  • OFFSET 5 — Skip the first 5 rows

Result:

 first_name | salary
------------+----------
 Barbara    | 95000.00
 Charles    | 92000.00
 Robert     | 98000.00
 Mary       | 78000.00
 Patricia   | 76000.00
(5 rows)

This is how pagination works. To show page 1 (rows 1-5): LIMIT 5 OFFSET 0. To show page 2 (rows 6-10): LIMIT 5 OFFSET 5.

Combining Everything: Complex Queries

Now let’s combine WHERE, DISTINCT, ORDER BY, and LIMIT to answer real questions.

Query: Show the 3 Highest-Paid Sales Employees

Sales is department 1.

SELECT first_name, last_name, salary FROM employees
WHERE dept_id = 1
ORDER BY salary DESC
LIMIT 3;

Result:

 first_name | last_name | salary
------------+-----------+----------
 Mary       | Johnson   | 78000.00
 Patricia   | Brown     | 76000.00
 James      | Wilson    | 75000.00
(3 rows)

Query: Show departments with at least 6 employees

SELECT DISTINCT dept_id FROM employees
WHERE dept_id IS NOT NULL
ORDER BY dept_id;

Result:

 dept_id
---------
       1
       2
       3
       4
(4 rows)

All 4 departments have at least 6 employees (since each department has exactly 6-8).

Query: Find Recent Engineering Hires

Engineering is department 2. Hired in 2021 or later.

SELECT first_name, last_name, hire_date FROM employees
WHERE dept_id = 2 AND EXTRACT(YEAR FROM hire_date) >= 2021
ORDER BY hire_date DESC;

Result:

 first_name | last_name |  hire_date
------------+-----------+------------
 Jessica    | White     | 2022-06-01
 Barbara    | Taylor    | 2021-01-25
 Jennifer   | Martinez  | 2019-08-05
 Richard    | Rodriguez | 2020-11-17
(4 rows)

Common Errors & Solutions

Error: “column reference is ambiguous”

Problem:

SELECT first_name, salary FROM employees, departments;

You’ve queried two tables but haven’t specified which table’s columns you want.

Solution: Specify the table name:

SELECT employees.first_name, employees.salary
FROM employees, departments;

Or use the alias method (covered in the JOINs guide):

SELECT e.first_name, e.salary
FROM employees e, departments d;

Error: “column does not exist”

Problem:

SELECT first_name, salarry FROM employees;

Typo: “salarry” instead of “salary”. PostgreSQL can’t find that column.

Solution: Check the spelling and run \d employees to see exact column names:

SELECT first_name, salary FROM employees;

Error: “WHERE clause must use aggregate function”

Problem:

SELECT dept_id, salary FROM employees WHERE COUNT(*) > 5;

You’re trying to use COUNT (an aggregate function) in WHERE.

Solution: Use HAVING instead of WHERE for aggregate conditions (covered in the GROUP BY guide):

SELECT dept_id, COUNT(*) FROM employees
GROUP BY dept_id
HAVING COUNT(*) > 5;

Performance Tips

1. Use WHERE to Filter Early

Don’t do this:

SELECT * FROM employees ORDER BY salary LIMIT 10;

This sorts all 30 employees, then picks 10. Slow.

Do this instead:

SELECT * FROM employees WHERE dept_id = 2 ORDER BY salary LIMIT 10;

Filter first, sort later.

2. Select Only Columns You Need

Don’t do this:

SELECT * FROM employees;

This retrieves all 7 columns even if you only need 2.

Do this instead:

SELECT first_name, salary FROM employees;

Fewer columns = faster queries + less network traffic.

3. Use LIMIT for Large Result Sets

Don’t retrieve millions of rows to your application:

SELECT * FROM employees;  -- Bad for huge tables

Do this:

SELECT * FROM employees LIMIT 100;

Retrieve in batches. Use OFFSET to paginate through results.

4. Avoid SELECT * in Production

In development, SELECT * is fine for exploration. In production, always specify columns:

SELECT first_name, email, salary FROM employees;

If someone adds a new column to employees later, your application code doesn’t break.

FAQ

Q: What’s the difference between WHERE and HAVING? A: WHERE filters rows before grouping. HAVING filters groups after aggregation. We cover this in detail in the GROUP BY guide.

Q: Can I use column aliases in WHERE? A: No. WHERE is evaluated before aliases are created. Use the original column name in WHERE:

-- This doesn't work
SELECT salary * 1.1 AS increased_salary FROM employees
WHERE increased_salary > 80000;

-- This works
SELECT salary * 1.1 AS increased_salary FROM employees
WHERE salary * 1.1 > 80000;

Q: Is LIMIT guaranteed to return the same rows each time? A: Only if you use ORDER BY. Without ORDER BY, the order is unpredictable. Always use ORDER BY with LIMIT for consistent results.

Q: Can I ORDER BY a column that’s not in the SELECT? A: Yes, absolutely:

SELECT first_name FROM employees ORDER BY salary DESC;

You’re ordering by salary even though you’re only selecting first_name.

Q: Does WHERE or ORDER BY execute first? A: WHERE executes first (filters rows), then ORDER BY sorts the results. This is why filtering before sorting is faster.

Q: How many rows is “too many” for LIMIT? A: There’s no hard limit, but LIMIT 1000000 is unusual. If you’re selecting millions of rows, you probably want to aggregate or filter differently.

Q: Can I combine LIMIT and OFFSET efficiently? A: Yes, but large OFFSET values are slow. If you’re paginating through millions of rows, consider using a “keyset pagination” approach instead (covered in the Advanced Pagination guide).

Q: What’s the performance difference between != and <>? A: They’re identical. Both mean “not equal.” Use whichever you prefer—most SQL developers use !=.

Q: Can SELECT modify data? A: No. SELECT only reads data. To modify data, you’d use INSERT, UPDATE, or DELETE (not covered in this guide).

Next Steps

You now understand SELECT fundamentals. The next steps depend on what you want to learn:

Want to learn filtering better? Check out the WHERE Clause Advanced Filtering guide for complex conditions, pattern matching, and null handling.

Want to remove duplicates and group data? Read the DISTINCT and GROUP BY Explained guide to aggregate data and calculate summaries.

Want to combine data from multiple tables? Jump to the PostgreSQL JOINs Complete Guide to learn how to query across related tables.

Want to learn pagination patterns? See the LIMIT/OFFSET vs. Pagination Patterns guide for efficient data retrieval at scale.

Keep practicing with the employee database. Try writing your own queries to answer questions like:

  • “Show me all employees hired after 2021”
  • “List departments with their employee counts”
  • “Find the 10 highest-paid employees”

The more you practice, the more natural SELECT becomes. You’re building the foundation for all SQL work to come!