SQL SELECT Statement (How to Query a Database Table)

Retrieving Data — The SELECT Statement

SELECT * FROM BasicTable;

This is the result set — the output returned by the SELECT query. It looks like a table and can be thought of as one, but it is not stored anywhere. It is a temporary virtual table computed at query execution time and discarded when the query is done.

The Result Set — A Temporary Virtual Table

The result set is the fundamental output unit of SQL. Every SELECT query produces a result set — a two-dimensional grid of rows and columns, like a table, but existing only for the duration of the query execution. The result set reflects the state of the data at the moment the query runs. Run the same query a minute later after another INSERT, and the result set will include the new row.

In DBeaver, Beekeeper Studio, and HeidiSQL, the result set appears in the results panel below the SQL editor — a scrollable grid showing exactly what the database returned. In an application, the result set is made available to the application code through the database driver, row by row, for processing or display.

The SELECT Statement — Keyword by Keyword

-- All columns
SELECT *

-- Specific columns only
SELECT Firstname, Lastname

FROM BasicTable

-- Minimal: retrieve all columns from BasicTable
SELECT * FROM BasicTable;

-- Minimal with column list: retrieve two specific columns
SELECT Firstname, Lastname FROM BasicTable;

Selecting Specific Columns

-- Returns only Lastname and Firstname, in that order
SELECT Lastname, Firstname
FROM BasicTable;

Column Aliases — Renaming Output Columns

The AS keyword assigns an alias — a display name — to a column in the result set. The alias appears as the column header in the output and does not affect the underlying table:

SELECT Firstname AS "First Name",
       Lastname  AS "Last Name",
       PhoneNumber AS "Phone"
FROM BasicTable;

When to Use SELECT * and When to Avoid It

SELECT * is convenient for exploration and quick verification — it is the right tool when you want to see all the data in a table during development or debugging. It is acceptable in situations where the specific columns and their order are irrelevant, such as inside an EXISTS subquery.

In production queries — application code, stored procedures, and view definitions — always name the columns explicitly. A table's column set can change: new columns get added with ALTER TABLE, columns get renamed, columns get dropped. A SELECT * in application code silently changes behavior when the table changes. Explicit column names make the query's intent clear and protect against unintended results when the schema evolves. The same principle applies to view definitions — a view built on SELECT * will include new columns added to the base table, which may expose data the view was not intended to expose.

Filtering Rows with WHERE

SELECT Firstname, Lastname, PhoneNumber
FROM BasicTable
WHERE Lastname = 'Gauss';

Equality and Comparison Operators

WHERE conditions support a full set of comparison operators. Each operator compares the column value to the condition value and includes the row if the comparison is TRUE:

-- Equality
WHERE Lastname = 'Gauss'

-- Not equal
WHERE Lastname != 'Gauss'
-- or: WHERE Lastname <> 'Gauss'   (older syntax, both standard)

-- Range comparison
WHERE PhoneNumber > '555-0200'

-- Range between two values (inclusive)
WHERE PhoneNumber BETWEEN '555-0100' AND '555-0400'

-- Pattern matching (% matches any sequence of characters)
WHERE Lastname LIKE 'G%'

-- NULL test — note: = NULL never works; IS NULL is required
WHERE PhoneNumber IS NULL
WHERE PhoneNumber IS NOT NULL

-- Both conditions must be true
WHERE Lastname = 'Gauss' AND PhoneNumber IS NOT NULL

-- Either condition can be true
WHERE Lastname = 'Gauss' OR Lastname = 'Riemann'

WHERE and Index Usage

The WHERE clause is the primary driver of index usage, as covered in lessons 5 through 7. When the WHERE condition references an indexed column with a selective predicate — equality on a high-cardinality column like Lastname — the query optimizer uses the index to locate matching rows directly rather than scanning the entire table. The WHERE clause you write here determines which indexes are useful to create with CREATE INDEX.

Sorting Results with ORDER BY

-- Ascending order (default — A to Z, smallest to largest)
SELECT Firstname, Lastname
FROM BasicTable
ORDER BY Lastname ASC;

-- Descending order (Z to A, largest to smallest)
SELECT Firstname, Lastname
FROM BasicTable
ORDER BY Lastname DESC;

Multi-Column ORDER BY

Multiple sort columns can be specified, each with its own direction. The result is sorted by the first column, then by the second column within ties in the first, and so on:

-- Sort by Lastname ascending, then Firstname ascending within same Lastname
SELECT Firstname, Lastname
FROM BasicTable
ORDER BY Lastname ASC, Firstname ASC;

ORDER BY and Index Order

As covered in lesson 7, when an index exists on the ORDER BY column, the optimizer may read rows in index order to avoid an explicit sort operation. An index on Lastname means ORDER BY Lastname can be satisfied by scanning the index in key order rather than reading all rows and sorting in memory. The CREATE INDEX statements from lessons 6 and 7 directly support the ORDER BY clauses used in SELECT.

Eliminating Duplicates with DISTINCT

-- Without DISTINCT: returns one row per contact, may have duplicate lastnames
SELECT Lastname FROM BasicTable;

-- With DISTINCT: each unique Lastname appears only once
SELECT DISTINCT Lastname FROM BasicTable;

Limiting the Number of Rows Returned

-- Return only the first 2 rows
SELECT Firstname, Lastname
FROM BasicTable
ORDER BY Lastname
LIMIT 2;

-- SQL Server syntax — TOP goes inside the SELECT clause
SELECT TOP 2 Firstname, Lastname
FROM BasicTable
ORDER BY Lastname;

-- Standard SQL syntax — supported in PostgreSQL, SQL Server, Oracle, DB2
SELECT Firstname, Lastname
FROM BasicTable
ORDER BY Lastname
FETCH FIRST 2 ROWS ONLY;

Optional SELECT Clauses — A Preview

GROUP BY and HAVING

GROUP BY collapses multiple rows sharing the same value in a column into a single summary row, enabling aggregate functions — COUNT, SUM, AVG, MIN, MAX. HAVING filters the grouped result, similar to how WHERE filters individual rows. These are covered in detail in module 5.

-- Count of contacts per unique Lastname (contrived example on a small table)
SELECT Lastname, COUNT(*) AS ContactCount
FROM BasicTable
GROUP BY Lastname
HAVING COUNT(*) > 0;

JOIN — Combining Tables

-- Preview: joining Contacts to Orders on ContactID
SELECT c.Lastname, c.Firstname, o.OrderDate
FROM Contacts c
JOIN Orders o ON c.ContactID = o.ContactID
WHERE o.OrderDate >= '2026-01-01';

What Modules 5 Through 7 Cover

Select Insert - Quiz