Querying Data: SELECT

The SELECT statement is the most frequently used command in SQL. It is used to query and retrieve data from one or more tables.

Basic Selection

To retrieve all columns and all rows from a table, use the asterisk *.

SELECT * FROM users;

In production applications, it is considered a best practice to explicitly list the columns you need, rather than using *. This saves memory and bandwidth.

SELECT first_name, email FROM users;

Filtering Data (WHERE)

The WHERE clause filters the result set to only include rows that fulfill a specified condition.

SELECT * FROM users
WHERE last_name = 'Smith';

Common Operators

• =: Equal
• <>, !=: Not equal
• >, <, >=, <=: Greater/Less than
• BETWEEN: Between a range (inclusive)

  WHERE age BETWEEN 18 AND 30

• IN: Matches any value in a list

  WHERE status IN ('ACTIVE', 'PENDING')

• LIKE / ILIKE: Pattern matching. (ILIKE is PostgreSQL-specific and case-insensitive). % represents zero or more characters; _ represents exactly one character.

  WHERE email ILIKE '%@gmail.com'

Sorting Results (ORDER BY)

By default, the relational model does not guarantee the order in which rows are returned. If you need a specific order, you must use ORDER BY.

SELECT first_name, last_name, created_at
FROM users
ORDER BY created_at DESC; -- DESC for descending, ASC for ascending (default)

You can order by multiple columns:

ORDER BY last_name ASC, first_name ASC

Limiting Results (LIMIT & OFFSET)

To restrict the number of rows returned, use LIMIT. To skip a certain number of rows before returning the results, use OFFSET. This combination is commonly used for pagination.

-- Get page 3, where each page has 10 items
SELECT * FROM users
ORDER BY created_at DESC
LIMIT 10 OFFSET 20;

Pagination Performance

Using high OFFSET values (e.g., OFFSET 1000000) is very slow because the database must scan and skip all previous rows. For deep pagination, consider using "Keyset Pagination" (e.g., WHERE id > last_seen_id).