SQL SELECT: Your First Queries

SELECT is the single most important statement in SQL. You will write more SELECT queries in your career than every other SQL statement combined. This post teaches the parts you reach for every day, with a small running example so each query answers a real question.

If you haven’t read it yet, start with What Is SQL? for the background.

The running dataset

Throughout this post we’ll use two small tables.

CREATE TABLE users (
  id      INTEGER PRIMARY KEY,
  name    TEXT NOT NULL,
  email   TEXT NOT NULL UNIQUE,
  country TEXT,
  age     INTEGER
);

INSERT INTO users (id, name, email, country, age) VALUES
  (1, 'Ada',     'ada@example.com',     'UK', 36),
  (2, 'Linus',   'linus@example.com',   'FI', 55),
  (3, 'Grace',   'grace@example.com',   'US', 41),
  (4, 'Margaret','margaret@example.com','US', 29),
  (5, 'Dennis',  'dennis@example.com',  'UK', 62);

CREATE TABLE orders (
  id      INTEGER PRIMARY KEY,
  user_id INTEGER REFERENCES users(id),
  total   REAL NOT NULL,
  status  TEXT NOT NULL
);

INSERT INTO orders (id, user_id, total, status) VALUES
  (101, 1, 49.00,  'paid'),
  (102, 1, 12.50,  'paid'),
  (103, 2, 199.00, 'refunded'),
  (104, 3, 75.00,  'paid'),
  (105, 4, 22.00,  'pending'),
  (106, 4, 10.00,  'paid'),
  (107, 5, 300.00, 'paid');

Read those tables carefully. Every query below operates on this data.

The shape of a SELECT

A basic SELECT answers two questions: which columns do you want, and which rows do they come from?

SELECT column_list
FROM table_name;

The simplest possible query:

SELECT name, country
FROM users;

Returns:

name     | country
---------+--------
Ada      | UK
Linus    | FI
Grace    | US
Margaret | US
Dennis   | UK

You can use * to mean “all columns” — useful in exploration, but avoid it in production code because it makes queries fragile against schema changes.

SELECT * FROM users;

Filtering with WHERE

WHERE keeps only the rows that satisfy a condition.

SELECT name, age
FROM users
WHERE country = 'UK';

Result:

name   | age
-------+----
Ada    | 36
Dennis | 62

The condition can use any of the standard comparison operators:

Operator	Meaning
=	equal
<>	not equal (also !=)
<, <=	less than (or equal)
>, >=	greater than (or equal)

Strings are quoted with single quotes ('UK'), not double quotes. Numbers are unquoted. SQL is case-insensitive for keywords (SELECT = select) but values are case-sensitive ('uk' is not 'UK').

Combining conditions: AND, OR, NOT

You combine conditions with AND, OR, and NOT. Use parentheses to make precedence explicit — your future self will thank you.

-- Adults in the US
SELECT name, age
FROM users
WHERE country = 'US' AND age >= 18;

-- UK users OR users over 50
SELECT name, country, age
FROM users
WHERE country = 'UK' OR age > 50;

-- Everyone who is NOT in the US
SELECT name, country
FROM users
WHERE NOT country = 'US';

Without parentheses, AND binds tighter than OR. So:

WHERE country = 'UK' OR country = 'US' AND age > 30
-- means: country = 'UK' OR (country = 'US' AND age > 30)

If you want the other meaning, parenthesise:

WHERE (country = 'UK' OR country = 'US') AND age > 30

IN, BETWEEN, LIKE

Three operators show up constantly and make queries much easier to read.

IN

IN matches against a list of values — much cleaner than a chain of ORs.

SELECT name, country
FROM users
WHERE country IN ('UK', 'US');

BETWEEN

BETWEEN a AND b is inclusive on both ends.

SELECT name, age
FROM users
WHERE age BETWEEN 30 AND 50;

Returns Ada (36) and Grace (41).

LIKE

LIKE does simple string pattern matching. % matches any sequence of characters; _ matches exactly one character.

-- emails ending in @example.com
SELECT name, email
FROM users
WHERE email LIKE '%@example.com';

-- names starting with 'M'
SELECT name
FROM users
WHERE name LIKE 'M%';

LIKE is case-sensitive in some databases (Postgres) and case-insensitive in others (MySQL with the default collation). When you need a case-insensitive match in Postgres, use ILIKE.

NULL and IS NULL

NULL represents “unknown.” It is not equal to anything — not even to itself. This trips up every beginner.

-- This returns ZERO rows, even for rows where country is NULL
SELECT * FROM users WHERE country = NULL;

-- This is the correct way
SELECT * FROM users WHERE country IS NULL;
SELECT * FROM users WHERE country IS NOT NULL;

If you ever filter on a column and get fewer rows than you expected, suspect NULL.

Sorting with ORDER BY

ORDER BY controls the order of the rows in the result. Default is ascending (ASC); use DESC for descending.

SELECT name, age
FROM users
ORDER BY age DESC;

Result:

name     | age
---------+----
Dennis   | 62
Linus    | 55
Grace    | 41
Ada      | 36
Margaret | 29

You can sort by multiple columns. Earlier columns take priority; later ones break ties.

SELECT name, country, age
FROM users
ORDER BY country ASC, age DESC;

A small but important detail: without ORDER BY, the row order in a result is not guaranteed. Don’t rely on it.

LIMIT (and OFFSET)

LIMIT caps the number of rows returned. OFFSET skips the first N rows. They are the building blocks of pagination.

-- Three oldest users
SELECT name, age
FROM users
ORDER BY age DESC
LIMIT 3;

-- Page 2 of users, 2 per page
SELECT name
FROM users
ORDER BY id
LIMIT 2 OFFSET 2;

Note: SQL Server and Oracle have historically used different syntax (TOP, FETCH NEXT). Postgres, MySQL, and SQLite all support LIMIT.

DISTINCT

DISTINCT removes duplicate rows from the result.

-- Which countries are represented?
SELECT DISTINCT country
FROM users;

Result:

country
-------
UK
FI
US

DISTINCT applies to the entire selected row, not just one column:

-- Distinct (country, status) combinations across all orders
SELECT DISTINCT u.country, o.status
FROM users u
JOIN orders o ON o.user_id = u.id;

(We’ll cover JOIN in the next post — the syntax above is a preview.)

Putting it together

A more realistic query, using most of what we’ve covered:

SELECT name, email, age
FROM users
WHERE country IN ('UK', 'US')
  AND age BETWEEN 25 AND 60
  AND email LIKE '%@example.com'
ORDER BY age DESC
LIMIT 10;

Read it top to bottom and it almost sounds like English: “Give me the name, email, and age of users in the UK or US, aged 25 to 60, whose email is on example.com — newest first, max ten.”

That’s the whole point. Once your queries read like requirements, they become easy to write, easy to review, and easy to change.

A note on style

A few conventions experienced engineers follow without thinking:

• Keywords in uppercase, identifiers in lowercase. SELECT name FROM users reads better than select name from users or SELECT NAME FROM USERS. Many teams disagree; pick one and be consistent.
• One clause per line. SELECT … / FROM … / WHERE … stacked vertically scales to large queries. Single-line queries become unreadable past a certain size.
• Trailing commas in the SELECT list are not allowed in standard SQL. Different from JavaScript and Python — keep the comma off the last column.

Common beginner mistakes

• Using = with NULL instead of IS NULL.
• Forgetting that WHERE filters rows; you can’t refer to alias columns from the SELECT list inside WHERE (use the original expression, or filter in a wrapper query).
• Relying on result order without ORDER BY.
• Using SELECT * in application code, then breaking when someone adds a column.
• Confusing ' (string literals) with " (identifiers in some databases). Always use single quotes for values.

Recap

You now know:

• SELECT column_list FROM table is the skeleton of every query
• WHERE filters rows; AND, OR, NOT combine conditions
• IN, BETWEEN, LIKE shorten common filters
• NULL requires IS NULL / IS NOT NULL
• ORDER BY sorts, LIMIT/OFFSET paginates, DISTINCT deduplicates

That is enough to answer the vast majority of questions you’ll have about a single table.

Next steps

The next leap is querying across multiple tables with JOIN, aggregating with GROUP BY, and writing the kinds of reports real businesses care about.

Until then, practice. Every query you write on your own is worth ten that you only read.

Related reading: What Is SQL?, What Is Python (a great companion for analysing query results).

Questions or feedback? Email codeloomdevv@gmail.com.