mindixx
mindixx

Reference · module 1

SELECT

Everything this module explains, on one page.

SELECT and FROM

Ask a table for the columns you need.

  • A database keeps data in tables: rows are records, columns are their fields. SELECT title FROM books; asks the table books for the title column of every row.
  • List several columns with commas: SELECT title, year FROM books;. The result is a new table with exactly those columns, in the order you named them.
  • SELECT * FROM books; returns every column. It is handy for a first look at a table, but real code names its columns, so a new column cannot silently change the result.

The mistake you are about to make

SELECT title year FROM books;

SELECT title, year FROM books;

A space between columns feels natural, like between words. Without the comma SQL reads year as a new name for title: you get one column labelled year.

Calculations and names

Calculate new columns, name them, and handle text values.

  • A column can be calculated: SELECT name, price * 2 FROM products; returns each price doubled. The table itself does not change; only the result shows the new values.
  • AS names a result column: SELECT price * 2 AS double_price FROM products;. Without AS the column is labelled with the expression itself, which is hard to read and hard to use.
  • Text values go in single quotes: 'Oslo'. Double quotes mean a column or table name in standard SQL, and || joins text: SELECT first || ' ' || last FROM people;.

The mistake you are about to make

SELECT first + ' ' + last FROM people;

SELECT first || ' ' || last FROM people;

In many languages + joins text. In SQL + always adds numbers: SQLite tries to turn the names into numbers and returns 0 instead of a full name.

DISTINCT and LIMIT

Remove repeats, take only a few rows, and leave notes in a query.

  • SELECT DISTINCT city FROM customers; returns each city once, however many customers live there. DISTINCT applies to the whole row of chosen columns, not to one column.
  • LIMIT 5 at the end returns at most five rows: SELECT name FROM customers LIMIT 5;. Without ORDER BY, which five you get is up to the database.
  • Two dashes start a comment: everything after -- on that line is ignored. Comments explain why a query is written this way; the query itself already says what it does.

The mistake you are about to make

SELECT DISTINCT city, name FROM customers;

SELECT DISTINCT city FROM customers;

DISTINCT sits next to city, so it seems to remove repeated cities only. The whole row must be unique: two customers from the same city bring the city back twice.

Back to the map