Chapter 2 · Reading Data
SELECT: Columns, Expressions, Aliases and DISTINCT
- Page 5 of 22
- 16 min read
SELECT is the statement you will write more than any other. Every dataset you pull for a model, every number on a dashboard and every row an AI agent reads starts as a SELECT. This page covers the part that shapes each row of the result: which columns you take, which new columns you calculate, what you call them, how you glue text together, and how to remove duplicate rows.
Filtering rows (WHERE) and sorting them (ORDER BY) have their own pages next. Here every query still ends with an ORDER BY, so the results always come back in the same order.
What you will learn
SELECT *and choosing columns- Arithmetic and expressions, including the integer-division trap and what NULL does to a calculation
- Naming result columns with
AS - Joining text:
||in SQLite and PostgreSQL,CONCAT()in MySQL DISTINCTon one and several columns, and whySELECT *is fine for exploring but bad in code
SELECT *: every column
The star means "all columns, in the table's order". It is the quickest way to see what a table holds:
SELECT * FROM categories ORDER BY category_id;+-------------+-------------+
| category_id | name |
+-------------+-------------+
| 1 | Books |
| 2 | Electronics |
| 3 | Courses |
| 4 | Accessories |
| 5 | Furniture |
+-------------+-------------+On a big table, add LIMIT so you look at a few rows instead of pulling millions:
SELECT * FROM products ORDER BY product_id LIMIT 3;+------------+---------------------------+-------------+-------+-------+
| product_id | name | category_id | price | stock |
+------------+---------------------------+-------------+-------+-------+
| 1 | Python Crash Course | 1 | 1200 | 40 |
| 2 | Hands-On Machine Learning | 1 | 2500 | 15 |
| 3 | Mechanical Keyboard | 2 | 4500 | 12 |
+------------+---------------------------+-------------+-------+-------+Choosing columns
Name the columns you want, separated by commas. They come back in the order you list them, not the table's order:
SELECT price, name
FROM products
ORDER BY product_id
LIMIT 5;+-------+---------------------------+
| price | name |
+-------+---------------------------+
| 1200 | Python Crash Course |
| 2500 | Hands-On Machine Learning |
| 4500 | Mechanical Keyboard |
| 900 | Wireless Mouse |
| 18500 | 27-inch Monitor |
+-------+---------------------------+Picking columns is the SQL version of picking features: a model that predicts whether a product sells out needs price and stock, not the product's name.
Expressions and arithmetic
A column in the result does not have to exist in the table. Any expression works: arithmetic with + - * / and % (remainder), constants, and functions. Here is the money tied up in each product's stock:
SELECT product_id, name, price, stock, price * stock AS stock_value
FROM products
ORDER BY product_id;+------------+-----------------------------+-------+-------+-------------+
| product_id | name | price | stock | stock_value |
+------------+-----------------------------+-------+-------+-------------+
| 1 | Python Crash Course | 1200 | 40 | 48000 |
| 2 | Hands-On Machine Learning | 2500 | 15 | 37500 |
| 3 | Mechanical Keyboard | 4500 | 12 | 54000 |
| 4 | Wireless Mouse | 900 | 60 | 54000 |
| 5 | 27-inch Monitor | 18500 | 5 | 92500 |
| 6 | SQL Masterclass | 3000 | NULL | NULL |
| 7 | AI Engineering Bootcamp | 15000 | NULL | NULL |
| 8 | Laptop Stand | 1500 | 25 | 37500 |
| 9 | USB-C Hub | 2200 | 0 | 0 |
| 10 | Noise-Cancelling Headphones | 7500 | 8 | 60000 |
| 11 | Gift Card | 1000 | NULL | NULL |
+------------+-----------------------------+-------+-------+-------------+Look at products 6, 7 and 11. Their stock is NULL (courses and gift cards are not counted), so price * stock is NULL too: any arithmetic with NULL gives NULL. That is usually what you want — "unknown times 3000 is unknown" — but it means a feature column can fill up with NULLs that pandas will later read as NaN. The page Text, Number and Date Functions, CAST and COALESCE shows how to replace them.
The integer-division trap
In SQLite, PostgreSQL and SQL Server, dividing two whole numbers gives a whole number, with the remainder thrown away. Make one side a decimal to get a decimal answer:
SELECT name,
price,
price / 1000 AS thousands_wrong,
price / 1000.0 AS thousands,
ROUND(price * 1.15, 2) AS price_with_vat
FROM products
ORDER BY product_id
LIMIT 4;+---------------------------+-------+-----------------+-----------+----------------+
| name | price | thousands_wrong | thousands | price_with_vat |
+---------------------------+-------+-----------------+-----------+----------------+
| Python Crash Course | 1200 | 1 | 1.2 | 1380.0 |
| Hands-On Machine Learning | 2500 | 2 | 2.5 | 2875.0 |
| Mechanical Keyboard | 4500 | 4 | 4.5 | 5175.0 |
| Wireless Mouse | 900 | 0 | 0.9 | 1035.0 |
+---------------------------+-------+-----------------+-----------+----------------+The Wireless Mouse costs 0 thousand taka by the first formula. MySQL is the exception: its / always returns a decimal (900 / 1000 is 0.9000), so the same query gives different numbers on different databases — one more reason to write 1000.0 on purpose. ROUND(x, 2) keeps money to two decimals; SQLite prints whole-number decimals as 1380.0, PostgreSQL as 1380.00.
Expressions work on any column, so revenue per order line is simply quantity × unit price:
SELECT order_id, product_id, quantity, unit_price,
quantity * unit_price AS line_total
FROM order_items
WHERE order_id IN (7, 9)
ORDER BY order_id, product_id;+----------+------------+----------+------------+------------+
| order_id | product_id | quantity | unit_price | line_total |
+----------+------------+----------+------------+------------+
| 7 | 4 | 2 | 900 | 1800 |
| 7 | 9 | 1 | 2200 | 2200 |
| 9 | 3 | 1 | 4050 | 4050 |
| 9 | 4 | 1 | 900 | 900 |
+----------+------------+----------+------------+------------+You can even SELECT without a table to use SQL as a calculator — handy for checking an expression. The usual precedence applies (* and / before + and -):
SELECT 2 + 3 * 4 AS no_brackets, (2 + 3) * 4 AS with_brackets, 17 % 5 AS remainder;+-------------+---------------+-----------+
| no_brackets | with_brackets | remainder |
+-------------+---------------+-----------+
| 14 | 20 | 2 |
+-------------+---------------+-----------+Naming columns with AS
Without a name, a computed column is labelled with its own expression, like price * stock — awkward in a report and worse in code. AS gives it an alias. The word AS is optional (price * stock stock_value works) but write it: it makes the alias obvious. An alias with spaces or capitals needs double quotes:
SELECT name AS product, price * stock AS "Stock value (taka)"
FROM products
WHERE stock > 0
ORDER BY product_id
LIMIT 3;+---------------------------+--------------------+
| product | Stock value (taka) |
+---------------------------+--------------------+
| Python Crash Course | 48000 |
| Hands-On Machine Learning | 37500 |
| Mechanical Keyboard | 54000 |
+---------------------------+--------------------+For anything that code will read, prefer snake_case aliases: they become the column names of your DataFrame, and df["stock_value"] is easier to type than df["Stock value (taka)"]:
import pandas as pd
from sqlhelp import con
df = pd.read_sql("""
SELECT product_id, price, stock, price * stock AS stock_value
FROM products
ORDER BY product_id
""", con)
print(list(df.columns))
print(df["stock_value"].sum())['product_id', 'price', 'stock', 'stock_value']
383500.0pandas skipped the three NULLs (NaN) when summing, which is why the total is a float. Remember that an alias is born in the SELECT step, so you can sort by it but not filter on it with WHERE (see How SQL Works: Statements, Command Types and Query Order).
Joining text together
Standard SQL joins strings with ||. SQLite and PostgreSQL follow it; numbers are converted to text automatically:
SELECT 'Order #' || order_id || ' (' || status || ')' AS label
FROM orders
WHERE customer_id = 2
ORDER BY order_id;+----------------------+
| label |
+----------------------+
| Order #2 (delivered) |
| Order #6 (delivered) |
| Order #12 (shipped) |
+----------------------+Two things go wrong across databases. First, NULL swallows the whole string: customer 5 has no city, so name || ' (' || city || ')' is NULL. Second, MySQL reads || as logical OR (a deprecated feature, unless the server runs in PIPES_AS_CONCAT mode) and you must use CONCAT() instead. See all three databases on the same two customers:
SELECT name || ' (' || city || ')' AS piped,
CONCAT(name, ' (', city, ')') AS concat_fn
FROM customers
WHERE customer_id IN (1, 5)
ORDER BY customer_id;+----------------------+----------------------+
| piped | concat_fn |
+----------------------+----------------------+
| Nadia Rahman (Dhaka) | Nadia Rahman (Dhaka) |
| NULL | Sadia Chowdhury () |
+----------------------+----------------------+-- PostgreSQL
SELECT name || ' (' || city || ')' AS piped,
CONCAT(name, ' (', city, ')') AS concat_fn
FROM customers
WHERE customer_id IN (1, 5)
ORDER BY customer_id;+----------------------+----------------------+
| piped | concat_fn |
+----------------------+----------------------+
| Nadia Rahman (Dhaka) | Nadia Rahman (Dhaka) |
| NULL | Sadia Chowdhury () |
+----------------------+----------------------+-- MySQL
SELECT name || ' (' || city || ')' AS piped,
CONCAT(name, ' (', city, ')') AS concat_fn
FROM customers
WHERE customer_id IN (1, 5)
ORDER BY customer_id;+-------+----------------------+
| piped | concat_fn |
+-------+----------------------+
| 0 | Nadia Rahman (Dhaka) |
| NULL | NULL |
+-------+----------------------+| Database | || | CONCAT() with a NULL argument |
|---|---|---|
| SQLite | Joins text; NULL makes it NULL | Treats NULL as empty text (needs SQLite 3.44+) |
| PostgreSQL | Joins text; NULL makes it NULL | Treats NULL as empty text |
| MySQL | Logical OR: 0 or NULL, not text | Returns NULL (use CONCAT_WS, which skips NULLs) |
| SQL Server | Traditionally +; only SQL Server 2025 and Azure SQL also accept || | Treats NULL as empty text |
In AI work, concatenation is how you build the text you send to an embedding model or put into a prompt: one string per product or per support ticket, made from several columns. A NULL in one column must not silently erase the whole text — check for it.
DISTINCT: remove duplicate rows
DISTINCT keeps one copy of each different result row. Which cities do customers come from?
SELECT DISTINCT city FROM customers ORDER BY city;+------------+
| city |
+------------+
| NULL |
| Chattogram |
| Dhaka |
| Khulna |
| Rajshahi |
| Sylhet |
+------------+Eight customers, six distinct values. DISTINCT treats all NULLs as one value, so the missing city appears once. (SQLite and MySQL sort NULL first; PostgreSQL sorts it last — Sorting and Paging: ORDER BY, LIMIT and OFFSET covers this.)
With several columns, DISTINCT looks at the whole row, i.e. the combination. Which customer–status pairs exist among the 14 orders?
SELECT DISTINCT customer_id, status
FROM orders
ORDER BY customer_id, status;+-------------+-----------+
| customer_id | status |
+-------------+-----------+
| 1 | delivered |
| 2 | delivered |
| 2 | shipped |
| 3 | delivered |
| 4 | cancelled |
| 5 | delivered |
| 6 | delivered |
| 6 | pending |
| 7 | delivered |
+-------------+-----------+Customer 2 appears twice because they have both delivered and shipped orders; customer 1's three delivered orders collapse into one row. To count distinct values, put DISTINCT inside COUNT (aggregates get their own page, Aggregates, GROUP BY and HAVING):
SELECT COUNT(*) AS orders, COUNT(DISTINCT customer_id) AS customers_who_ordered
FROM orders;+--------+-----------------------+
| orders | customers_who_ordered |
+--------+-----------------------+
| 14 | 7 |
+--------+-----------------------+Seven of the eight customers have ordered; you will find the missing one with a LEFT JOIN in LEFT, RIGHT, FULL, CROSS and Self Joins. DISTINCT is not free — the database must sort or hash every row to find duplicates — and it is never the fix for duplicates you did not expect. If a query returns repeated rows, find out why (usually a join, see Joining Many Tables, and the Duplicate-Row Trap) instead of hiding them.
SELECT * is for exploring, not for code
SELECT * is perfect at the prompt, when you want to see a table. Inside a program it is a time bomb, because the program silently depends on the table having exactly these columns in exactly this order. Watch what happens when a colleague adds a column:
from sqlhelp import con
def product_cards():
rows = con.execute("SELECT * FROM products WHERE product_id <= 2 ORDER BY product_id").fetchall()
for product_id, name, category_id, price, stock in rows:
print(f"{product_id}: {name} costs {price}")
product_cards()
con.execute("ALTER TABLE products ADD COLUMN description TEXT") # a new column appears
con.commit()
try:
product_cards()
except ValueError as error:
print("ValueError:", error)1: Python Crash Course costs 1200
2: Hands-On Machine Learning costs 2500
ValueError: too many values to unpack (expected 5)The same function, unchanged, now crashes. Naming the columns makes it immune to new columns:
def product_cards():
rows = con.execute(
"SELECT product_id, name, price FROM products WHERE product_id <= 2 ORDER BY product_id"
).fetchall()
for product_id, name, price in rows:
print(f"{product_id}: {name} costs {price}")
product_cards()1: Python Crash Course costs 1200
2: Hands-On Machine Learning costs 2500Other reasons to name columns in code: SELECT * drags along columns you do not need — in an AI app that can be a 1536-number embedding or a long document body per row, multiplying the data sent over the network; it hides what the code really uses; and after a join it can return two columns with the same name. Rule of thumb: SELECT * at the prompt, named columns in files.
Common mistakes
- A missing comma.
SELECT name priceis valid SQL: it meansname AS price. No error, just a column calledpricefull of names:SELECT name price FROM products ORDER BY product_id LIMIT 3;Fix:+---------------------------+ | price | +---------------------------+ | Python Crash Course | | Hands-On Machine Learning | | Mechanical Keyboard | +---------------------------+SELECT name, price. When a result looks strange, count the commas first. - Integer division.
price / 1000is 0 for the mouse in SQLite and PostgreSQL. Fix: divide by1000.0, as shown above. ||on MySQL, or NULL inside||. On MySQL useCONCAT(); everywhere, decide what a missing value should become before joining strings (COALESCE(city, 'unknown')— see Text, Number and Date Functions, CAST and COALESCE):SELECT name || ' (' || COALESCE(city, 'unknown') || ')' AS label FROM customers WHERE customer_id IN (1, 5) ORDER BY customer_id;+---------------------------+ | label | +---------------------------+ | Nadia Rahman (Dhaka) | | Sadia Chowdhury (unknown) | +---------------------------+- Thinking
DISTINCTapplies to one column.SELECT DISTINCT city, nameremoves rows only when both city and name repeat, so it returns all eight customers.DISTINCTalways applies to the whole selected row.
Try it yourself
- Easy: Show
name,priceand the price including 15% VAT, rounded to 2 decimals and calledprice_with_vat, for the products withproduct_id1 to 4 (useORDER BY product_id LIMIT 4). - Medium: List the different payment methods used, alphabetically. Then list the different
(method, paid_on)pairs for payments made in January or February 2026 (WHERE paid_on < '2026-03-01'). - Hard: Build one line of text per product for an embedding model, like
Laptop Stand | price 1500 taka | stock 25, for products 5 to 8. Courses have NULL stock: showstock n/afor them (useCOALESCE). Write it for SQLite, then for PostgreSQL, whereCOALESCEneeds both values to be text (stock::text).
Answers
1. Multiply by 1.15 and round:
SELECT name, price, ROUND(price * 1.15, 2) AS price_with_vat
FROM products
ORDER BY product_id
LIMIT 4;+---------------------------+-------+----------------+
| name | price | price_with_vat |
+---------------------------+-------+----------------+
| Python Crash Course | 1200 | 1380.0 |
| Hands-On Machine Learning | 2500 | 2875.0 |
| Mechanical Keyboard | 4500 | 5175.0 |
| Wireless Mouse | 900 | 1035.0 |
+---------------------------+-------+----------------+2. DISTINCT on one column, then on a pair:
SELECT DISTINCT method FROM payments ORDER BY method;
SELECT DISTINCT method, paid_on
FROM payments
WHERE paid_on < '2026-03-01'
ORDER BY method, paid_on;+--------+
| method |
+--------+
| bkash |
| card |
| cash |
+--------+
+--------+------------+
| method | paid_on |
+--------+------------+
| bkash | 2026-01-12 |
| bkash | 2026-02-03 |
| card | 2026-01-20 |
| cash | 2026-02-16 |
+--------+------------+3. In SQLite, || converts numbers to text, and COALESCE may mix a number and text:
SELECT product_id,
name || ' | price ' || price || ' taka | stock ' || COALESCE(stock, 'n/a') AS embedding_text
FROM products
WHERE product_id BETWEEN 5 AND 8
ORDER BY product_id;+------------+--------------------------------------------------------+
| product_id | embedding_text |
+------------+--------------------------------------------------------+
| 5 | 27-inch Monitor | price 18500 taka | stock 5 |
| 6 | SQL Masterclass | price 3000 taka | stock n/a |
| 7 | AI Engineering Bootcamp | price 15000 taka | stock n/a |
| 8 | Laptop Stand | price 1500 taka | stock 25 |
+------------+--------------------------------------------------------+PostgreSQL is stricter about types, so cast stock to text first:
-- PostgreSQL
SELECT product_id,
name || ' | price ' || price || ' taka | stock ' || COALESCE(stock::text, 'n/a') AS embedding_text
FROM products
WHERE product_id BETWEEN 5 AND 8
ORDER BY product_id;+------------+--------------------------------------------------------+
| product_id | embedding_text |
+------------+--------------------------------------------------------+
| 5 | 27-inch Monitor | price 18500 taka | stock 5 |
| 6 | SQL Masterclass | price 3000 taka | stock n/a |
| 7 | AI Engineering Bootcamp | price 15000 taka | stock n/a |
| 8 | Laptop Stand | price 1500 taka | stock 25 |
+------------+--------------------------------------------------------+Summary
SELECT *returns every column; list columns by name to choose and order them.- Any expression can be a result column. Arithmetic with NULL gives NULL; whole-number division drops the remainder everywhere except MySQL, so divide by
1000.0. ASnames a column; usesnake_casefor anything code will read, double quotes only for display names.- Join text with
||in SQLite and PostgreSQL and withCONCAT()in MySQL; NULL turns||(and MySQL'sCONCAT) into NULL. DISTINCTremoves duplicate rows — across all selected columns — and treats NULLs as equal;COUNT(DISTINCT col)counts different values.SELECT *at the prompt, named columns in code.
Next: Sorting and Paging: ORDER BY, LIMIT and OFFSET explains the ORDER BY and LIMIT you have been using on every query: sorting by several columns and by expressions, where NULLs go, top-N lists, and paging through results.