SQL SELECT and WHERE: choose and filter rows

Who this is for: learners starting with relational databases. You should be comfortable reading a table with rows and columns; no framework or database administration experience is required.

What you will learn: choose columns with SELECT, filter rows with WHERE, combine conditions with AND, and control display order with ORDER BY.

The examples use standard SQL features available in MySQL and PostgreSQL. They are written for those database families but have not been tested against every version. No row-limit syntax is used because that differs across database systems.

Start with the shared sample data

This lesson uses the same three customers and three orders as the SQL joins lesson. Run this setup in a test database if you want to try the queries. The example creates only these two tables; don’t run it in a database that already contains tables with these names.

CREATE TABLE customers (
    customer_id INTEGER PRIMARY KEY,
    customer_name VARCHAR(50) NOT NULL
);

CREATE TABLE orders (
    order_id INTEGER PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    amount DECIMAL(8, 2) NOT NULL
);

INSERT INTO customers (customer_id, customer_name) VALUES
    (1, 'Asha'),
    (2, 'Ben'),
    (3, 'Chloe');

INSERT INTO orders (order_id, customer_id, amount) VALUES
    (101, 1, 120.00),
    (102, 1, 80.00),
    (103, 3, 200.00);

The customers are Asha, Ben, and Chloe. Orders 101 and 102 belong to Asha; order 103 belongs to Chloe. Ben has no order. The customer_id in orders identifies the customer for each order.

Choose columns with SELECT

A query starts with the columns to return and the table to read. This query asks for just the customer names:

SELECT customer_name
FROM customers
ORDER BY customer_id;

Use a comma-separated list when you need more than one column:

SELECT order_id, amount
FROM orders
ORDER BY order_id;

SELECT * returns every column. It can be convenient while exploring a table, but naming the columns you need makes a query easier to review and less dependent on changes to the table structure.

Filter rows with WHERE

WHERE keeps rows that satisfy a condition. For example, return orders with an amount of at least 100:

SELECT order_id, customer_id, amount
FROM orders
WHERE amount >= 100
ORDER BY order_id;

Common comparison operators include =, <> (not equal), >, >=, <, and <=. Text values are written in single quotes, for example customer_name = 'Asha'.

Combine conditions with AND

AND requires both conditions to be true. To find Asha’s orders that are at least 100:

SELECT order_id, amount
FROM orders
WHERE customer_id = 1
  AND amount >= 100
ORDER BY order_id;

Use OR when either condition may be true. When a condition mixes AND and OR, use parentheses to make the intended grouping explicit. Filtering NULL values needs IS NULL or IS NOT NULL, not = NULL; that topic deserves its own lesson.

Sort rows with ORDER BY

Without ORDER BY, a database does not promise a particular row order. Add one or more columns to make the displayed order clear. Use DESC for descending order or ASC for ascending order (the default):

SELECT order_id, amount
FROM orders
ORDER BY amount DESC, order_id ASC;

This sorts by amount from highest to lowest, and uses the order ID from lowest to highest to break any ties.

Try it yourself

Write a query that returns order_id and amount for orders that are either below 100 or belong to customer 3. Sort by order ID. Before reading the answer, predict how many rows will appear.

SELECT order_id, amount
FROM orders
WHERE amount < 100
   OR customer_id = 3
ORDER BY order_id;

Key idea

SELECT chooses the columns, FROM names the table, WHERE filters rows, and ORDER BY sorts the result. These clauses read data; they do not change the stored rows.