Querying data

We have data in our users table. Alice, Bob, Carol, and a few others. Now let’s get it back out. The SELECT statement retrieves data from a table, and it is by far the most frequently used SQL statement. You will write variations of it for as long as you use databases, so it is worth taking the time to get comfortable with every piece of it.

Selecting every column

SELECT * FROM users;

The * means “every column.” FROM users specifies which table to read from. This returns all rows and all columns in the table.

This is fine for quick checks in the shell, but in application code you generally want to be more specific about which columns you need.

Selecting specific columns

SELECT id, email, name FROM users;

Instead of *, you list the columns you want. This is better practice in application code because it is explicit about what data you need. If someone adds a new column to the table later through a migration, your query still works and still returns exactly the same shape of data. No surprises.

Renaming columns with AS

You can rename a column in the output using AS:

SELECT name AS username, email AS contact FROM users;

username  contact
--------  -----------------
Alice     [email protected]
Bob       [email protected]

The actual column in the table is still called name. The alias only affects how it appears in the query result. This is useful when column names are unclear or when you want the output to be more readable.

Removing duplicates with DISTINCT

DISTINCT removes duplicate rows from the results:

SELECT DISTINCT name FROM users;

If three users happen to share the name “Alex,” this returns “Alex” only once. Without DISTINCT, it would appear three times.

One thing to know. DISTINCT applies to the entire row, not just one column. If you write SELECT DISTINCT name, email FROM users, two rows are considered duplicates only if both name and email match.

Filtering with WHERE

WHERE limits which rows are returned:

SELECT * FROM users WHERE id = 1;

This only returns the row where id is exactly 1. The = is a comparison operator. It checks for equality.

You can combine conditions with AND and OR:

SELECT * FROM users
WHERE name = 'Alice'
  AND email = '[email protected]';

Both conditions must be true for a row to be included.

SELECT * FROM users
WHERE name = 'Alice'
   OR name = 'Bob';

Either condition can be true. We will dive much deeper into filtering in the next lesson.

Counting rows

SELECT count(*) FROM users;

count(*) is an aggregate function. Instead of returning the rows themselves, it counts how many rows match the query. With no WHERE clause, it counts every row in the table.

You can combine counting with a filter:

SELECT count(*) FROM users WHERE name LIKE 'A%';

This counts how many users have a name starting with “A.” We will explain LIKE and the % wildcard in the next lesson.

LIMIT and OFFSET

LIMIT caps how many rows are returned:

SELECT * FROM users LIMIT 5;

This returns at most 5 rows. If the table only has 3 rows, you get 3.

OFFSET skips rows before starting to return results:

SELECT * FROM users LIMIT 5 OFFSET 10;

This skips the first 10 rows and then returns up to 5. This is the basic pattern for pagination. Page 1 is LIMIT 5 OFFSET 0, page 2 is LIMIT 5 OFFSET 5, page 3 is LIMIT 5 OFFSET 10, and so on.

[!TIP] LIMIT and OFFSET are most useful together with ORDER BY, which we will cover in a later lesson. Without an ORDER BY, the order of rows is not guaranteed, so “page 1” might return different rows each time. In practice, you almost always want to sort your results before paginating.

Exercises

Exercise 1: Write a query that selects only the name and email columns from the users table. Use AS to rename name to full_name in the output.

Exercise 2: Use count(*) to count how many users are in the table. Then add a WHERE clause to count only users whose name starts with a specific letter.

Exercise 3: Use LIMIT and OFFSET to paginate through your users, 2 at a time. Write the queries for page 1, page 2, and page 3.

We can select data and filter it with basic conditions. But WHERE has a lot more tools available. In the next lesson, we will go through all the filtering operators in detail.