Back to all posts

SQL SELECT Statement: Syntax, Examples, and Query Order

For someone writing their first SQL queries; every clause below is shown with the rows it returns.

On this page

A table holds more rows and columns than you want to look at, and SELECT asks the database for the part you need: which columns, from which table, and which rows. The answer comes back as a result set, rows and columns built for this one question and stored nowhere.

SELECT column1, column2   -- the columns you want
FROM table_name           -- the table they come from
WHERE condition           -- optional: the rows to keep
ORDER BY column1;         -- optional: the order of the rows

Only SELECT and FROM are required. Every query below ran on PostgreSQL 17.9, MySQL 9.1.0 and SQLite 3.50.4, and the results are copied from those runs.

What the SQL SELECT statement does

A database keeps its data in tables. Like a spreadsheet, a table has named columns, and each row is one record with a value in every column. The examples use two tables:

CREATE TABLE students (
    id    INT PRIMARY KEY,
    name  VARCHAR(30),
    age   INT,
    grade CHAR(1)
);

CREATE TABLE enrollments (
    student_id INT REFERENCES students(id),
    course     VARCHAR(30)
);

INSERT INTO students VALUES
    (1, 'John',  20, 'A'),
    (2, 'Sara',  19, 'B'),
    (3, 'Mike',  22, 'C'),
    (4, 'Jenny', 21, 'A'),
    (5, 'Tom',   23, NULL);

INSERT INTO enrollments VALUES
    (1, 'Math'), (1, 'History'), (2, 'Math'), (4, 'Physics');

The shortest useful query names the columns and the table:

SELECT name, grade
FROM students;
namegrade
JohnA
SaraB
MikeC
JennyA
TomNULL

Tom has no grade yet. NULL means no value at all, not an empty string or a zero, and it behaves unlike any other value, as the sections below show.

SELECT picks the columns, a WHERE condition picks the rows, and the result set is where the two meet:

The students table with the name and grade columns shaded by SELECT and the rows of John and Jenny outlined by WHERE grade = 'A'; the result set holds only John A and Jenny A

SELECT syntax overview

A full SELECT has more clauses, and they must be written in this order:

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;

Everything after FROM is optional.

Logical query processing order

The database doesn't work through the clauses in the order you write them. It starts from the tables, filters and groups the rows, and only then computes the column list:

Left, the order you write the clauses: SELECT, FROM, JOIN, WHERE, GROUP BY, HAVING, ORDER BY, LIMIT. Right, the order the database works through them: FROM, JOIN, WHERE, GROUP BY, HAVING, SELECT, DISTINCT, ORDER BY, LIMIT

Microsoft documents this logical processing order: the engine may execute a query differently, but the result is the one these steps produce.

So a column alias made in SELECT can't be used in WHERE:

SELECT name, age + 1 AS age_next_year
FROM students
WHERE age_next_year > 22;

PostgreSQL stops with column "age_next_year" does not exist, and MySQL with Unknown column 'age_next_year' in 'where clause'. The PostgreSQL manual allows the alias in ORDER BY and GROUP BY only. SQLite accepts it and returns Mike and Tom. To run it everywhere, repeat the expression, WHERE age + 1 > 22, or compute the alias in a subquery and filter outside it.

SELECT specific columns vs SELECT *

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

SELECT *
FROM students;
idnameagegrade
1John20A
2Sara19B
3Mike22C
4Jenny21A
5Tom23NULL

Use the star to explore a table. In code that ships, name the columns: the star also returns any column added later, including one you would rather not send to a browser.

Practice this: Lesson 2 of the SQL tutorial runs SELECT, AS and DISTINCT on a shop database in your browser, with exercises.

WHERE and DISTINCT

A WHERE condition is tested against each row, and you get the rows for which it is true. Text values go in single quotes; PostgreSQL reads "A" as a column name and fails.

SELECT name, grade
FROM students
WHERE grade = 'A';
namegrade
JohnA
JennyA

Combine conditions with AND and OR: WHERE age > 20 AND grade = 'A' keeps only Jenny.

NULL is where WHERE surprises. Ask for every student whose grade is not A:

SELECT name, grade
FROM students
WHERE grade <> 'A';
namegrade
SaraB
MikeC

Tom is missing. A comparison with NULL gives unknown rather than true, and WHERE keeps only true rows. Write grade IS NULL to find Tom, or WHERE grade <> 'A' OR grade IS NULL to keep him. SQL WHERE clause and SQL NULL values cover the rest.

DISTINCT drops repeated rows. SELECT DISTINCT grade FROM students returns each grade once, with NULL as a value of its own. PostgreSQL returned NULL, B, C, A, and MySQL and SQLite A, B, C, NULL, so add ORDER BY when the order matters. More cases are in SQL SELECT DISTINCT.

Aliases, expressions, and CASE

AS names a column of the result, such as the expression age + 1 AS age_next_year above; the stored columns keep their names. CASE puts each row into a category. It tests its conditions in order and stops at the first one that holds:

SELECT name, grade,
       CASE
         WHEN grade = 'A' THEN 'Top'
         WHEN grade IS NULL THEN 'Not graded'
         ELSE 'Passed'
       END AS result
FROM students;
namegraderesult
JohnATop
SaraBPassed
MikeCPassed
JennyATop
TomNULLNot graded

Without the WHEN grade IS NULL branch, Tom fails the first test, falls through to ELSE, and is reported as Passed. SQL aliases lists where each engine accepts an alias.

ORDER BY, LIMIT, TOP, and pagination

Without ORDER BY, rows come back in "an unspecified order", as the PostgreSQL manual puts it. Name the sort columns, with ASC (the default) or DESC:

SELECT name, age
FROM students
ORDER BY age DESC;
nameage
Tom23
Mike22
Jenny21
John20
Sara19

Where NULL lands depends on the engine. ORDER BY grade puts Tom last on PostgreSQL and first on MySQL and SQLite. PostgreSQL and SQLite let you choose with NULLS LAST or NULLS FIRST; MySQL rejects both with error 1064.

To keep only the first rows, each engine has its own spelling:

enginefirst three rows
PostgreSQLLIMIT 3 or FETCH FIRST 3 ROWS ONLY
MySQLLIMIT 3
SQLiteLIMIT 3
SQL ServerSELECT TOP (3) ... or OFFSET 0 ROWS FETCH NEXT 3 ROWS ONLY
Oracle Database 23aiFETCH FIRST 3 ROWS ONLY

Added to the query above, LIMIT 3 returns Tom, Mike and Jenny. MySQL and SQLite reject FETCH FIRST. The SQL Server and Oracle rows come from Microsoft's TOP and ORDER BY pages and Oracle's SELECT reference.

OFFSET skips rows before the limit starts counting, which is how you fetch a page of results. Skip two rows and take two for the second page:

SELECT id, name
FROM students
ORDER BY id
LIMIT 2 OFFSET 2;
idname
3Mike
4Jenny

Page on a unique sort key such as the primary key. Rows with equal sort values can swap places between runs, so a row can appear on two pages and another on none. SQL TOP, LIMIT, FETCH FIRST, and ROWNUM has the details.

GROUP BY, HAVING, and aggregate functions

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

SELECT grade, COUNT(*) AS students, MAX(age) AS oldest
FROM students
GROUP BY grade
ORDER BY grade;
gradestudentsoldest
A221
B119
C122
NULL123

Five rows went in and four came out: the result now describes grades, not students. Tom's NULL forms its own group, which MySQL and SQLite sort first.

Add name to a list grouped only by grade, and PostgreSQL and MySQL, under its default SQL mode, reject the query: a group has no single name. SQLite runs it and takes the name from an arbitrary row of each group.

HAVING filters groups the way WHERE filters rows:

SELECT grade, COUNT(*) AS students
FROM students
GROUP BY grade
HAVING COUNT(*) > 1;
gradestudents
A2

SQL GROUP BY explained and SQL HAVING clause go further.

SELECT with JOINs and subqueries

A join pairs the rows of two tables on a condition, usually a foreign key matching a primary key. The short names s and e stand for the tables:

SELECT s.name, e.course
FROM students s
INNER JOIN enrollments e ON e.student_id = s.id
ORDER BY s.name, e.course;
namecourse
JennyPhysics
JohnHistory
JohnMath
SaraMath

John appears twice, once per course. Mike and Tom have no enrollments, so INNER JOIN leaves them out; SQL joins explained shows the LEFT JOIN that keeps them.

A subquery is a SELECT inside another statement. This one returns the average age, 21, and the outer query compares every row with it:

SELECT name, age
FROM students
WHERE age > (SELECT AVG(age) FROM students);
nameage
Mike22
Tom23

Common mistakes and best practices

The mistake that stops with an error is a condition on an aggregate written in WHERE:

SELECT grade, COUNT(*)
FROM students
WHERE COUNT(*) > 1
GROUP BY grade;
engineerror
PostgreSQLaggregate functions are not allowed in WHERE
MySQLERROR 1111 (HY000): Invalid use of group function
SQLitemisuse of aggregate: COUNT()

WHERE runs before the groups exist. Move the condition into HAVING.

Quieter mistakes return a result that looks fine. WHERE grade = NULL returns no rows on all three engines, Tom included; write IS NULL. And a condition on single rows belongs in WHERE: in HAVING, the database first groups rows that it then throws away.

Practice questions

Write each query on the two tables above, then compare it with the answers:

  1. Select the name and age of the students with grade A, youngest first.
  2. List each course once.
SELECT name, age FROM students WHERE grade = 'A' ORDER BY age;

SELECT DISTINCT course FROM enrollments ORDER BY course;

Build SELECT queries visually in DbSchema

On a real database, the hard part is knowing which table holds a column and which key joins it to the next. DbSchema connects to the database and draws its tables and foreign keys as a diagram.

In DbSchema, open the SQL Editor with Editor → SQL Editor in the toolbar, paste any query from this page, and press Run. The rows show in the result grid under the editor:

The DbSchema SQL Editor with a query that counts the orders of each customer, and the result grid under it

The Query Builder writes the SELECT for you. Right-click a table on the diagram and choose Query Editor, join a second table through the icon beside a key column, untick the columns you don't want, and add a condition with Filter or a count with Aggregate. DbSchema shows the SQL as you click:

The DbSchema Query Builder with customers joined to orders, a filter on status, count(*), the generated SQL at the right and the result grid below

A SELECT run from either one only reads the database. A query kept with Save and Close stays in the DbSchema model, not in the database.

Download DbSchema at https://dbschema.com/download.html, connect it to a database of your own, and run these queries in the SQL Editor, which the free Community Edition covers. The Query Builder and saving the model to a file need the Pro edition.

DbSchema ER diagram designer DbSchema ER diagram designer

Design and visualize
your database schema

Edit referenced records
in related tables

Query your data
visually too

Reuse the SQL
generated

Free Download

FAQ

Is SQL SELECT case-sensitive?

The keywords are not: select and SELECT mean the same. Text comparisons depend on the engine. WHERE name = 'john' finds John on MySQL, whose default collation compares text without regard to case, and returns no rows on PostgreSQL and SQLite.