Lesson 3 of 8 · 5 min
Reading data with SELECT
Choose columns, rename them with aliases, calculate new values, remove duplicates, and understand the order SQL runs a query in.
On this page
SELECT is the command you will write most. It never changes data; it only reads.
The simplest query
SELECT * FROM products;
* means "every column". It is fine for exploring, but in application code list the columns you need. It is faster, and your code won't break when someone adds a column.
SELECT name, price FROM products;
Aliases
AS renames a column in the result, or gives a table a short name:
SELECT name AS product, price AS price_inr
FROM products AS p;
Calculated columns
You can do arithmetic and call functions in the select list:
SELECT
name,
price,
price * 0.9 AS sale_price,
ROUND(price * 1.18, 2) AS price_with_gst
FROM products;
Some useful functions that work in most databases:
| Function | Does |
|---|---|
UPPER(name), LOWER(name) |
Change case |
LENGTH(name) |
Number of characters |
ROUND(x, 2) |
Round to 2 decimal places |
COALESCE(city, 'Unknown') |
First value that isn't null |
CURRENT_DATE |
Today's date |
Joining text differs: first || ' ' || last in PostgreSQL and SQLite, CONCAT(first, ' ', last) in MySQL (PostgreSQL supports CONCAT too).
NULL means "unknown"
Rahul has no city, so his city is NULL. NULL is not zero and not an empty string; it means "no value". Any arithmetic with NULL gives NULL, and you can't compare it with =:
SELECT name FROM customers WHERE city = NULL; -- wrong: returns nothing
SELECT name FROM customers WHERE city IS NULL; -- right
COALESCE replaces it for display:
SELECT name, COALESCE(city, 'Not given') AS city FROM customers;
Removing duplicates
SELECT DISTINCT category FROM products;
Returns each category once: Accessories, Displays, Video.
Limiting rows
SELECT name, price FROM products
ORDER BY price DESC
LIMIT 3;
The three most expensive products. (SQL Server uses SELECT TOP 3 instead of LIMIT.)
The order SQL runs a query in
You write the clauses in this order:
SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY ... LIMIT ...
But the database runs them in this order:
FROM: pick the tables (and joins)WHERE: keep matching rowsGROUP BY: put rows into groupsHAVING: keep matching groupsSELECT: calculate the output columnsORDER BY: sortLIMIT: cut the list
That explains a common surprise: you can't use a SELECT alias inside WHERE, because WHERE runs before SELECT has created it. You can use it in ORDER BY, which runs after.
Check yourself
Using the shop database:
- List every product's name and its price with 18% GST, rounded to two decimals.
- List every distinct city customers live in. Is
NULLin the result? - Show the cheapest two products.
More from Databases and SQL
- Lesson 5 · 6 minJoining tablesINNER JOIN, LEFT JOIN, joining three or more tables, self joins, and the mistakes that quietly multiply or drop rows.
- Lesson 6 · 5 minGrouping and aggregatesCOUNT, SUM, AVG, MIN and MAX, GROUP BY and HAVING, subqueries, common table expressions, and a first look at window functions.
- Lesson 7 · 5 minChanging data and transactionsINSERT, UPDATE and DELETE without accidents, upserts, transactions and the ACID guarantees, and how to keep SQL injection out of your application.