SQL SELECT Statement: Syntax, Examples, and Query Order

A table holds more rows and more columns than you want to look at, and one statement gets you the part you asked for. SELECT names the columns you want, the table they come from, and the conditions a row has to meet. The database reads the table and hands back a result set: rows and columns, like a table, but built for this one question and stored nowhere.

Every query below runs against three tables:

CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    first_name  VARCHAR(30),
    last_name   VARCHAR(30),
    email       VARCHAR(60),
    city        VARCHAR(30),
    country     VARCHAR(30)
);

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    first_name  VARCHAR(30),
    last_name   VARCHAR(30),
    department  VARCHAR(30),
    salary      INT,
    hire_date   DATE
);

CREATE TABLE orders (
    order_id     INT PRIMARY KEY,
    customer_id  INT REFERENCES customers(customer_id),
    order_date   DATE,
    total_amount DECIMAL(8,2)
);

INSERT INTO customers VALUES
    (1, 'John',  'Doe',   '[email protected]',  'New York', 'USA'),
    (2, 'Jane',  'Smith', '[email protected]',  'London',   'UK'),
    (3, 'Ana',   'Ruiz',  '[email protected]',   'Madrid',   'Spain'),
    (4, 'Piotr', 'Nowak', '[email protected]', 'London',   'UK');

INSERT INTO employees VALUES
    (1, 'Ada',     'Lovelace', 'Engineering', 82000, '2021-03-15'),
    (2, 'Grace',   'Hopper',   'Engineering', 95000, '2019-11-01'),
    (3, 'Alan',    'Turing',   'Engineering', 68000, '2022-06-20'),
    (4, 'Ken',     'Thompson', 'Engineering', 75000, '2023-04-03'),
    (5, 'Edsger',  'Dijkstra', 'Support',     58000, '2020-02-10'),
    (6, 'Barbara', 'Liskov',   'Support',     60000, '2023-01-05');

INSERT INTO orders VALUES
    (101, 1, '2023-12-28', 120.00),
    (102, 2, '2024-01-15',  89.50),
    (103, 2, '2024-02-02', 240.00),
    (104, 4, '2024-02-20',  45.00);

What the SQL SELECT statement does

The shortest useful query is two lines, and it already does the main job: two of the six columns, all four rows.

SELECT first_name, last_name
FROM customers;

Everything else is a clause you bolt onto that statement, and each one appears in its own section below:

• WHERE keeps only the rows that match a condition
• DISTINCT collapses repeated rows into one
• ORDER BY sorts the result
• GROUP BY turns many rows into one row per group
• HAVING filters those groups
• JOIN brings columns from a second table
• a subquery uses the result of one query inside another

SELECT syntax overview

The clauses have a fixed order in the text of the statement. Write them out of order and the database rejects the query:

SELECT [DISTINCT] column_list
FROM table_name
JOIN other_table ON condition
WHERE row_filter
GROUP BY grouping_columns
HAVING group_filter
ORDER BY sort_columns
LIMIT row_count OFFSET skip_count;

Only the first two lines are required. The rest are optional, and a real query usually uses three or four of them.

Logical query processing order

The order you write the clauses in is not the order the database works through them. It evaluates them like this:

1. FROM
2. JOIN
3. WHERE
4. GROUP BY
5. HAVING
6. SELECT
7. DISTINCT
8. ORDER BY
9. LIMIT / TOP / FETCH

Two rules that beginners trip over follow from that list. A name you invent in SELECT cannot be used in WHERE, because WHERE has finished by the time the SELECT list is evaluated; PostgreSQL, MySQL and SQL Server all reject it, and the SQL aliases article has the table of which clauses each engine does allow. And a condition on a group, such as a count, belongs in HAVING rather than WHERE, because no group exists yet when WHERE runs.

SELECT specific columns vs SELECT *

Name the columns you want, separated by commas, and the result has those columns in that order:

SELECT first_name, last_name, email
FROM customers;

The star stands for every column of the table, in the order the table declares them:

SELECT *
FROM customers;

Use the star while you are exploring a table you don't know. In code that ships, name the columns. A query written with the star silently starts returning any column somebody adds later, including one you would rather not send to a browser. It also moves the bytes of every column across the network, whether the application reads them or not.

WHERE and DISTINCT

A WHERE condition is tested against one row at a time, and the rows for which it comes out true are the rows you get:

SELECT first_name, last_name, salary
FROM employees
WHERE department = 'Engineering'
  AND salary > 70000;

Alan is in Engineering too, and his salary of 68000 fails the second condition, so his row is gone. The operators you can write there are =, <>, >, <, BETWEEN, IN, LIKE and IS NULL; the SQL WHERE clause and SQL LIKE operator articles work through each of them.

DISTINCT throws away repeated rows after the columns have been picked. Six employees work in two departments, so two rows come back:

SELECT DISTINCT department
FROM employees
ORDER BY department;

With several columns, it is the whole combination that has to repeat before a row is dropped. Jane and Piotr are both in London, so London appears once:

SELECT DISTINCT city, country
FROM customers
ORDER BY city;

More cases are in SQL SELECT DISTINCT.

Aliases, expressions, and CASE

AS renames a column in the result. The stored column keeps its name; only the heading of the result changes:

SELECT first_name || ' ' || last_name AS full_name,
       salary AS annual_salary
FROM employees;

The || operator joins two strings in standard SQL. MySQL is the exception: there || means OR, unless the PIPES_AS_CONCAT SQL mode is switched on, so on MySQL write CONCAT(first_name, ' ', last_name) instead.

An expression in the SELECT list is computed once per row, and it needs an alias if you want a readable heading:

SELECT first_name, salary, salary + 5000 AS salary_after_raise
FROM employees;

CASE sorts each row into a category by testing conditions in order and stopping at the first one that holds:

SELECT first_name, salary,
       CASE
         WHEN salary < 60000 THEN 'Junior'
         WHEN salary < 80000 THEN 'Mid'
         ELSE 'Senior'
       END AS salary_band
FROM employees;

Barbara earns exactly 60000, and salary < 60000 is false for her, so she falls through to the second branch. Boundaries like that are worth checking on paper before you trust a CASE.

ORDER BY, LIMIT, TOP, and pagination

Without ORDER BY, no engine promises the rows in any particular order. Name the sort columns and the direction, ASC for ascending or DESC for descending, and later columns break the ties the earlier ones leave:

SELECT first_name, department, hire_date
FROM employees
ORDER BY department ASC, hire_date DESC;

To keep only the first few rows, PostgreSQL, MySQL and SQLite use LIMIT, and SQL Server uses TOP in front of the column list:

-- PostgreSQL, MySQL, SQLite
SELECT first_name, salary
FROM employees
ORDER BY salary DESC
LIMIT 3;

-- SQL Server
SELECT TOP (3) first_name, salary
FROM employees
ORDER BY salary DESC;

Both statements return the three best-paid employees:

OFFSET skips rows before the limit starts counting, which is how a page of results is fetched. Skip two rows and take two, and you have page 2 of a two-row page:

SELECT employee_id, first_name
FROM employees
ORDER BY employee_id
LIMIT 2 OFFSET 2;

The sort has to be stable for paging to work, so sort on something unique such as the primary key. Page through rows sorted only by department and the same employee can arrive on two pages while another never shows up. SQL ORDER BY and SQL TOP, LIMIT, FETCH FIRST, and ROWNUM cover the engine spellings in full.

GROUP BY, HAVING, and aggregate functions

GROUP BY collapses the rows that share a value into a single row, and an aggregate function such as COUNT, AVG, MIN, MAX or SUM reports one number per group:

SELECT department,
       COUNT(*) AS employee_count,
       AVG(salary) AS average_salary,
       MAX(salary) AS max_salary
FROM employees
GROUP BY department;

Six rows went in and two came out, which is the shift that makes grouping hard to read at first: the result is no longer about people, it is about departments. A column that is neither grouped nor wrapped in an aggregate has no single value for the group, so adding a bare first_name to that list gets the query rejected.

HAVING filters the grouped rows, the way WHERE filters the raw ones:

SELECT department, COUNT(*) AS employee_count
FROM employees
GROUP BY department
HAVING COUNT(*) > 3;

Support has two employees and its group is dropped. SQL GROUP BY explained, SQL HAVING clause and SQL COUNT, AVG, and SUM functions go further.

SELECT with JOINs and subqueries

A join reads two tables in one query and pairs their rows on a condition, usually a foreign key matching a primary key. Table aliases such as o and c keep the column list short:

SELECT o.order_id,
       c.first_name,
       c.last_name,
       o.order_date,
       o.total_amount
FROM orders o
INNER JOIN customers c
  ON o.customer_id = c.customer_id
WHERE o.order_date >= '2024-01-01'
ORDER BY o.order_date DESC;

Jane appears twice because she placed two orders. John's only order is from December 2023, so the date filter drops it. Ana has no orders at all, so INNER JOIN leaves her out. The SQL joins article shows the outer joins that keep her.

A subquery is a SELECT written inside another statement. This one returns a single number, the average salary of 73000, and the outer query compares every row against it:

SELECT first_name, last_name, salary
FROM employees
WHERE salary > (
  SELECT AVG(salary)
  FROM employees
);

A subquery in the FROM clause returns a whole table instead of one value, and the outer query treats it as one. It needs a name, here department_stats, so that the rest of the statement can refer to it:

SELECT department, avg_salary
FROM (
  SELECT department, AVG(salary) AS avg_salary
  FROM employees
  GROUP BY department
) AS department_stats
WHERE avg_salary > 60000;

That is also the workaround for the alias rule above: avg_salary is unusable in WHERE in the query that creates it, and perfectly usable in the query that reads it back.

Common mistakes and best practices

The mistake that produces an error rather than a wrong answer is a condition on an aggregate written in WHERE:

SELECT department, COUNT(*)
FROM employees
WHERE COUNT(*) > 3
GROUP BY department;

The database rejects that statement, because WHERE runs before the groups exist and COUNT(*) has no value yet for a single row. Move the condition into HAVING, as the grouping section does.

The quieter mistakes give you a result that looks fine. Paging without ORDER BY returns rows in whatever order the engine finds convenient, which can differ between two runs of the same query. A SELECT * left in application code returns columns that did not exist when the code was written. Filtering rows in HAVING instead of WHERE gives the right answer but makes the database group rows it is about to throw away, so put every condition that a single row can answer in WHERE.

Design and visualize
your database schema

Edit referenced records
in related tables

Query your data
visually too

Reuse the SQL
generated

Free Download

Build SELECT queries visually in DbSchema

The hard part of a real SELECT is rarely the syntax; it is knowing which table holds the column you want and which key joins it to the next table. DbSchema connects through the PostgreSQL JDBC driver, the MySQL JDBC driver or the SQL Server JDBC driver, reverse-engineers the schema, and draws the tables and their foreign keys as a diagram you can read while you write.

Open the SQL Editor from the Editors menu, paste any query from this page, and Execute Query brings the rows back in a grid under the statement. The Query Builder writes the statement for you instead: pick the tables, tick the columns, set the filters and the grouping, and it generates the SELECT with the joins the foreign keys imply. Copy that SQL into your application, or click Save in the result pane to write the whole result set to a file.

To read the rows of two related tables together, right-click a table header in the diagram and choose Open in Relational Data Editor. The Relational Data Editor shows that table beside its child tables, and clicking a row in the parent refilters every child pane to the rows the foreign key matches.

The diagram and the SQL Editor are in the free Community Edition. The Query Builder, the Relational Data Editor and saving the model to a file are Pro. Running a SELECT from any of them only reads the database and changes nothing in it. A Query Builder you keep is stored in the design model file, so it reopens with the diagram next time you load it.

Download DbSchema at https://dbschema.com/download.html, connect it to a database of your own, and rewrite these queries against real tables in the SQL Editor. The Pro edition covers all of it, the Query Builder and the Relational Data Editor included. From here, SQL WHERE clause goes deeper into filtering, SQL joins explained into reading several tables at once, and SQL ORDER BY into sorting.

FAQ

What is the difference between WHERE and HAVING?

WHERE is tested against one row at a time and runs before any grouping, so it can only read the values in that row. HAVING is tested against a group produced by GROUP BY, which is why COUNT(*) and AVG(salary) are legal there and illegal in WHERE.

Can I use an alias in a WHERE clause?

No, on PostgreSQL, MySQL and SQL Server alike, because the SELECT list that defines the alias is evaluated after WHERE. Repeat the expression in the WHERE clause, or wrap the query in a subquery and filter on the alias outside it.

What does SELECT * mean?

The star returns every column of every table named in the FROM clause, in the order those tables declare their columns. It is a fast way to look at an unfamiliar table and a poor thing to leave in application code, because the set of columns it returns changes whenever the table does.

How do I select data from multiple tables?

Write a JOIN with an ON condition that says which column of the first table matches which column of the second, as the orders and customers example does. INNER JOIN returns only the rows that match on both sides, while LEFT JOIN also keeps the unmatched rows of the first table.

What is the default sort direction in ORDER BY?

The default is ascending, so ORDER BY salary and ORDER BY salary ASC mean the same thing. Writing DESC after a column name reverses the order for that column only, which is what lets one ORDER BY sort ascending on the first column and descending on the second.