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
  1. The simplest query
  2. Aliases
  3. Calculated columns
  4. NULL means "unknown"
  5. Removing duplicates
  6. Limiting rows
  7. The order SQL runs a query in
  8. Check yourself

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:

  1. FROM: pick the tables (and joins)
  2. WHERE: keep matching rows
  3. GROUP BY: put rows into groups
  4. HAVING: keep matching groups
  5. SELECT: calculate the output columns
  6. ORDER BY: sort
  7. LIMIT: 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:

  1. List every product's name and its price with 18% GST, rounded to two decimals.
  2. List every distinct city customers live in. Is NULL in the result?
  3. Show the cheapest two products.

More from Databases and SQL