Query Primer

8 min read•Learning SQL

Query Primer

The SELECT statement is the workhorse of SQL. You'll use it constantly to retrieve data from your database. This chapter covers the fundamentals of querying data, from simple column selection to sorting and limiting results.

The SELECT Statement

Every query starts with SELECT. At its most basic:

SQL

SELECT column1, column2, column3
FROM table_name;

This returns the specified columns for all rows in the table.

Selecting All Columns

The asterisk (*) selects all columns:

SQL

SELECT *
FROM customers;

customer_id	first_name	last_name	email	join_date
1	Alice	Chen	alice.chen@email.com	2024-01-15
2	Bob	Martinez	bob.m@email.com	2024-01-16
3	Carol	Taylor	carol.t@email.com	2024-01-17

Warning

Avoid SELECT * in production code. It retrieves unnecessary data, breaks if columns are added/removed, and makes your intent unclear. Always specify the columns you need.

Selecting Specific Columns

Specify exactly what you need:

SQL

SELECT first_name, last_name, email
FROM customers;

first_name	last_name	email
Alice	Chen	alice.chen@email.com
Bob	Martinez	bob.m@email.com
Carol	Taylor	carol.t@email.com

Column Aliases

Use AS to rename columns in the output:

SQL

SELECT
    first_name AS "First Name",
    last_name AS "Last Name",
    email AS "Email Address"
FROM customers;

First Name	Last Name	Email Address
Alice	Chen	alice.chen@email.com
Bob	Martinez	bob.m@email.com
Carol	Taylor	carol.t@email.com

Note

Use double quotes around aliases with spaces or special characters. Without spaces, quotes are optional: first_name AS fname works fine.

Aliases for Calculations

Aliases are especially useful for computed columns:

SQL

SELECT
    title,
    price,
    price * 0.9 AS discounted_price,
    stock_quantity * price AS inventory_value
FROM books;

title	price	discounted_price	inventory_value
1984	15.99	14.391	719.55
Animal Farm	12.99	11.691	389.70
Pride and Prejudice	14.99	13.491	374.75

Removing Duplicates with DISTINCT

When you need unique values only, use DISTINCT:

SQL

-- All author nationalities (may have duplicates)
SELECT nationality
FROM authors;
 
-- Unique nationalities only
SELECT DISTINCT nationality
FROM authors;

nationality
British
Colombian
Japanese

DISTINCT can also apply to combinations of columns:

SQL

-- Unique combinations of status and year
SELECT DISTINCT status, YEAR(order_date) AS order_year
FROM orders;

Literals and Expressions

You can include literal values and expressions in your SELECT:

SQL

SELECT
    title,
    'USD' AS currency,
    price,
    price * 1.1 AS price_with_tax,
    CONCAT(title, ' by ', author_id) AS description
FROM books;

Common Expressions

SQL

-- Arithmetic
SELECT price, price + 5 AS price_plus_five FROM books;
SELECT quantity * unit_price AS line_total FROM order_items;
 
-- String concatenation (varies by database)
SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM customers;  -- MySQL
SELECT first_name || ' ' || last_name AS full_name FROM customers;      -- PostgreSQL
 
-- NULL handling
SELECT COALESCE(phone, 'No phone') AS phone FROM customers;

Sorting Results with ORDER BY

The ORDER BY clause sorts your results:

SQL

SELECT title, price
FROM books
ORDER BY price;

title	price
Animal Farm	12.99
Pride and Prejudice	14.99
1984	15.99
Norwegian Wood	16.99
Kafka on the Shore	17.99
One Hundred Years of Solitude	18.99

Ascending and Descending

SQL

-- Ascending (default, lowest first)
SELECT title, price FROM books ORDER BY price ASC;
 
-- Descending (highest first)
SELECT title, price FROM books ORDER BY price DESC;

Sorting by Multiple Columns

SQL

-- Sort by author, then by publication year within each author
SELECT title, author_id, publication_year
FROM books
ORDER BY author_id, publication_year DESC;

title	author_id	publication_year
1984	1	1949
Animal Farm	1	1945
Pride and Prejudice	2	1813
One Hundred Years of Solitude	3	1967
Kafka on the Shore	4	2002
Norwegian Wood	4	1987

Sorting by Column Position

You can reference columns by their position (1-based):

SQL

SELECT title, price, stock_quantity
FROM books
ORDER BY 2 DESC;  -- Sort by price (second column)

Warning

Positional references make code harder to read and maintain. If someone reorders your SELECT columns, the sort breaks silently. Use column names instead.

Sorting by Aliases

You can sort by column aliases:

SQL

SELECT
    title,
    price * stock_quantity AS inventory_value
FROM books
ORDER BY inventory_value DESC;

Limiting Results

Large result sets can overwhelm applications. Use limits to control output size.

MySQL / PostgreSQL / SQLite

SQL

-- Get the 5 most expensive books
SELECT title, price
FROM books
ORDER BY price DESC
LIMIT 5;
 
-- Skip first 5, get next 5 (pagination)
SELECT title, price
FROM books
ORDER BY price DESC
LIMIT 5 OFFSET 5;

SQL Server

SQL

-- SQL Server uses TOP
SELECT TOP 5 title, price
FROM books
ORDER BY price DESC;

Standard SQL (FETCH)

SQL

-- Standard SQL syntax (PostgreSQL, newer MySQL)
SELECT title, price
FROM books
ORDER BY price DESC
FETCH FIRST 5 ROWS ONLY;
 
-- With offset
SELECT title, price
FROM books
ORDER BY price DESC
OFFSET 5 ROWS FETCH NEXT 5 ROWS ONLY;

Pagination Pattern

To display results 10 per page:

SQL

-- Page 1 (items 1-10)
SELECT * FROM books ORDER BY title LIMIT 10 OFFSET 0;
 
-- Page 2 (items 11-20)
SELECT * FROM books ORDER BY title LIMIT 10 OFFSET 10;
 
-- Page 3 (items 21-30)
SELECT * FROM books ORDER BY title LIMIT 10 OFFSET 20;
 
-- General formula: OFFSET = (page_number - 1) * items_per_page

Query Execution Order

SQL statements have a logical execution order that differs from how you write them:

FROM - Identify the table(s)
WHERE - Filter rows (covered next chapter)
GROUP BY - Group rows (covered later)
HAVING - Filter groups (covered later)
SELECT - Choose columns
DISTINCT - Remove duplicates
ORDER BY - Sort results
LIMIT/OFFSET - Restrict output

Note

This is why you can use column aliases in ORDER BY (step 7) but not in WHERE (step 2)—the alias doesn't exist yet during WHERE processing.

Table Aliases

Just as columns can have aliases, so can tables:

SQL

SELECT b.title, b.price
FROM books AS b
ORDER BY b.price;

Table aliases become essential when joining multiple tables:

SQL

SELECT
    b.title,
    a.first_name,
    a.last_name
FROM books AS b, authors AS a
WHERE b.author_id = a.author_id;

NULL Values

NULL represents missing or unknown data. It's not zero, not an empty string—it's the absence of a value.

SQL

-- Find authors without a recorded birth date
SELECT first_name, last_name
FROM authors
WHERE birth_date IS NULL;
 
-- Find authors with a recorded birth date
SELECT first_name, last_name
FROM authors
WHERE birth_date IS NOT NULL;

Warning

NULL comparisons require IS NULL or IS NOT NULL. Using = NULL or <> NULL won't work as expected because NULL isn't equal to anything, including itself.

Comments in SQL

Document your queries with comments:

SQL

-- This is a single-line comment
 
SELECT title, price
FROM books
WHERE price > 15;  -- Only expensive books
 
/*
   This is a multi-line comment.
   Useful for longer explanations
   or temporarily disabling code.
*/
SELECT * FROM orders;

Putting It Together

Let's combine what we've learned:

SQL

-- Find the 3 most recently published books with inventory value
SELECT
    title,
    publication_year,
    price,
    stock_quantity,
    price * stock_quantity AS inventory_value
FROM books
WHERE stock_quantity > 0
ORDER BY publication_year DESC, inventory_value DESC
LIMIT 3;

title	publication_year	price	stock_quantity	inventory_value
Kafka on the Shore	2002	17.99	28	503.72
Norwegian Wood	1987	16.99	35	594.65
One Hundred Years...	1967	18.99	20	379.80

What's Next

Now that you can select and sort data, the next chapter covers filtering with WHERE clauses—selecting only the rows that meet specific criteria.