sqlstreak

The SQL SELECT statement

SELECT is how you read data. Almost every query you will ever write starts with it, so it's worth understanding exactly what each part does. Every example on this page runs in your browser on a small animal-shelter database: press Run and edit freely.

Pick the columns you need

After SELECT, list the columns you want, separated by commas. After FROM, name the table. Columns come back in the order you list them. SELECT * returns every column, which is handy for exploring but best avoided in application code: it fetches more data than you need and breaks quietly when the table changes.

SELECT name, species, age
FROM pets;

Calculate and rename with AS

A column can be an expression: arithmetic, a function call, or text joined with ||. Give it a readable name with AS. The alias only exists in the result; the table isn't changed.

SELECT name, age * 12 AS age_in_months, weight_kg * 1000 AS weight_grams
FROM pets;

Remove duplicates with DISTINCT

SELECT DISTINCT returns each unique row once. It applies to the whole row you select, so SELECT DISTINCT species, adopted gives every unique combination of the two.

SELECT DISTINCT species
FROM pets;

Turn values into labels with CASE

CASE WHEN … THEN … ELSE … END works like an if-statement inside a query. It's the standard way to bucket numbers or relabel codes for a report.

SELECT name, age,
  CASE WHEN age >= 8 THEN 'senior'
       WHEN age >= 3 THEN 'adult'
       ELSE 'young' END AS life_stage
FROM pets;

Common mistakes

  • Forgetting the comma between columns: SELECT name species silently renames name to species.

  • Expecting a particular row order without ORDER BY. Databases may return rows in any order.

  • Using double quotes for text. In standard SQL, 'single quotes' are text and "double quotes" are column names.

Questions

Is SELECT * bad?+

It's fine for exploring a table. In saved queries and application code, list the columns you need: it's faster, clearer, and doesn't break when someone adds a column.

Does the order of columns in SELECT matter?+

Only for the output. The result's columns appear in the order you list them; it has no effect on which rows are returned.

Reading is step one. Now make it stick.

Practise this in a five-minute lesson with instant feedback. No account needed.

Practise it now