SQL Tables, Rows and Your First SELECT

Most of the code you have written so far probably kept its data in variables: a list, an array, an object. Close the program and the data is gone. A database is the opposite deal — the data outlives the program, and the program asks for the slice of it that it needs right now. SQL is the language that asking is written in, and SELECT is the one word you will type more than any other.

This whole lesson runs in your browser. The playground here is real SQLite, compiled to WebAssembly, and it gives every run a brand-new, empty database. That single fact shapes every example on this page: there are no tables waiting for you, so each example creates its own table, puts a few rows in it, and then queries them — all in one script. Copy any block below into the SQL playground and press Run, and you will get exactly the output shown underneath it.

What a table actually is

A table is a grid with named columns and any number of rows.

• A column has a name and holds one kind of fact — a name, a price, a date. Every row has a slot for every column.
• A row is one thing the table is about: one tool, one member, one payment. Rows have no order of their own; if you want them in an order, you ask for one (that is the next lesson but one).

The tables in this lesson belong to an invented community tool library, where neighbours borrow a drill instead of buying one. Its tools table has four columns — an id, a name, the shelf the tool lives on, and the refundable deposit in pence, stored as a whole number.

Here is that table being built and then read back:

CREATE TABLE tools (
  id      INTEGER PRIMARY KEY,
  name    TEXT,
  shelf   TEXT,
  deposit INTEGER
);

INSERT INTO tools (name, shelf, deposit) VALUES
  ('hedge trimmer',  'garden', 950),
  ('cordless drill', 'power',  1200),
  ('wheelbarrow',    'garden', 500),
  ('tile cutter',    'power',  1500),
  ('hand saw',       'hand',   350);

SELECT * FROM tools;

id | name           | shelf  | deposit
---+----------------+--------+--------
 1 | hedge trimmer  | garden |     950
 2 | cordless drill | power  |    1200
 3 | wheelbarrow    | garden |     500
 4 | tile cutter    | power  |    1500
 5 | hand saw       | hand   |     350
(5 rows)

Three statements ran, in the order they were written, each ended by a semicolon. Only the third one returned any rows, so only the third one printed a table. The line under the result — (5 rows) — is the playground telling you how many rows came back; it is not part of the data.

Notice that id was never mentioned in the INSERT, yet every row has one. INTEGER PRIMARY KEY in SQLite means "this column identifies the row", and when you leave it out SQLite fills in the next whole number for you. Lesson 7 comes back to what a primary key promises.

SELECT: choosing columns

SELECT * FROM tools means "every column, every row". The * is convenient while you are exploring and a poor habit afterwards, because it hands you columns you did not ask for and quietly changes what your query returns the day someone adds a column. Naming the columns is better, and it also lets you decide their order — the order in the query wins, not the order in the table:

CREATE TABLE tools (id INTEGER PRIMARY KEY, name TEXT, shelf TEXT, deposit INTEGER);
INSERT INTO tools (name, shelf, deposit) VALUES
  ('hedge trimmer', 'garden', 950),
  ('cordless drill', 'power', 1200),
  ('wheelbarrow', 'garden', 500);

SELECT shelf, name FROM tools;

shelf  | name
-------+---------------
garden | hedge trimmer
power  | cordless drill
garden | wheelbarrow
(3 rows)

From here on the examples use that shorter one-line CREATE TABLE, so the setup does not crowd out the query being explained. Both spellings mean the same thing.

Read a SELECT out of order and it makes more sense than reading it left to right. FROM tools says where the rows come from; shelf, name says which parts of each row you want back. The database works in that order too, whatever the words look like.

Columns you compute

A SELECT list is not limited to columns that exist. Anything that works out to a value can go there — a sum, a piece of text stuck onto another, a function call — and the result is a column like any other.

CREATE TABLE tools (id INTEGER PRIMARY KEY, name TEXT, shelf TEXT, deposit INTEGER);
INSERT INTO tools (name, shelf, deposit) VALUES
  ('hedge trimmer', 'garden', 950),
  ('cordless drill', 'power', 1200),
  ('hand saw', 'hand', 350);

SELECT name, deposit, deposit / 100.0 AS deposit_pounds, upper(shelf) AS shelf_label
FROM tools;

name           | deposit | deposit_pounds | shelf_label
---------------+---------+----------------+------------
hedge trimmer  |     950 |            9.5 | GARDEN
cordless drill |    1200 |             12 | POWER
hand saw       |     350 |            3.5 | HAND
(3 rows)

AS gives a computed column a name. Without it the column heading is whatever the database decides to call the expression, which is rarely something you would choose. The name after AS is for the output only — it is not a new column in the table, and nothing was written back. (The drill's 12 is a decimal number that happens to have nothing after the point, and the playground prints it without one.)

Why deposit / 100.0 rather than deposit / 100? Because both sides of / are whole numbers in the second version, and SQLite then does whole-number division and throws the remainder away:

SELECT 950 / 100 AS whole_number_division, 950 / 100.0 AS with_a_decimal, 7 / 2 AS seven_halved;

whole_number_division | with_a_decimal | seven_halved
----------------------+----------------+-------------
                    9 |            9.5 |            3
(1 row)

That is worth having burnt in early, because it bites in real reports. PostgreSQL behaves the same way with two integers; MySQL is the odd one out and gives you 3.5 for 7 / 2. Writing one side as a decimal — or wrapping it in CAST(... AS REAL) — makes your intent explicit in every dialect.

This example also shows that FROM is optional in SQLite when you are not reading a table. SELECT 2 + 2; is a complete, legal query, which makes the playground a usable calculator when you just want to check what an expression does.

Text, numbers and NULL

Values in a table come in a few flavours. Text goes in single quotes; numbers go bare. Get that wrong and SQLite will surprise you rather than stop you, which is the subject of the mistakes section below.

The third flavour is NULL, which means "no value here". It is not zero and it is not an empty string — it is the absence of an answer, and it behaves unlike anything else in SQL:

CREATE TABLE tools (id INTEGER PRIMARY KEY, name TEXT, shelf TEXT, deposit INTEGER);
INSERT INTO tools (name, shelf, deposit) VALUES
  ('hedge trimmer', 'garden', 950),
  ('donated ladder', 'garden', NULL);

SELECT name, deposit, deposit + 100 AS plus_a_pound, deposit IS NULL AS unknown_deposit
FROM tools;

name           | deposit | plus_a_pound | unknown_deposit
---------------+---------+--------------+----------------
hedge trimmer  |     950 |         1050 |               0
donated ladder | NULL    | NULL         |               1
(2 rows)

The ladder was donated and nobody has set a deposit yet. Adding 100 to an unknown number gives another unknown number, so plus_a_pound is NULL as well — that is how NULL spreads through arithmetic. deposit IS NULL is the right way to test for it, and SQLite prints the true/false answer as 1 or 0 because it has no separate boolean type. The next lesson covers NULL in conditions properly; it is the single most common source of rows mysteriously going missing.

Comments, and what happens with no SELECT at all

Two dashes start a comment that runs to the end of the line, and /* ... */ comments out a block. A script with no query in it at all is not an error here — it just has nothing to print:

-- Build the table, then change our minds about querying it.
CREATE TABLE tools (id INTEGER PRIMARY KEY, name TEXT);
INSERT INTO tools (name) VALUES ('hand saw');
/* SELECT * FROM tools; */

Done. The statements ran, but none of them returned rows. Add a SELECT to see data.

That message is the playground's, not SQLite's. If you ever press Run and see it when you expected rows, the answer is almost always that the SELECT is commented out, or missing, or that you only wrote the CREATE and INSERT half of the script.

A worked example

Nothing new here — just the pieces above in one script, with the output read back carefully:

CREATE TABLE plots (
  id       INTEGER PRIMARY KEY,
  holder   TEXT,
  crop     TEXT,
  area_m2  INTEGER
);

INSERT INTO plots (holder, crop, area_m2) VALUES
  ('Asha',  'garlic',   12),
  ('Bruno', 'potatoes', 30),
  ('Cleo',  'herbs',    7),
  ('Devi',  NULL,       18);

SELECT holder, crop, area_m2, area_m2 * 3 AS beds_of_four, crop IS NULL AS not_planted_yet
FROM plots;

holder | crop     | area_m2 | beds_of_four | not_planted_yet
-------+----------+---------+--------------+----------------
Asha   | garlic   |      12 |           36 |               0
Bruno  | potatoes |      30 |           90 |               0
Cleo   | herbs    |       7 |           21 |               0
Devi   | NULL     |      18 |           54 |               1
(4 rows)

Four rows in, four rows out — a plain SELECT with no conditions never adds or drops rows. area_m2 * 3 was computed per row, so each row got its own answer. Devi's plot has no crop recorded, so crop shows NULL and not_planted_yet shows 1; everyone else gets 0. And notice the alignment: the playground right-aligns numbers and left-aligns text, which makes a wrong type visible at a glance.

Common mistakes

Using double quotes for text

CREATE TABLE tools (id INTEGER PRIMARY KEY, name TEXT);
INSERT INTO tools (name) VALUES ('hand saw');
SELECT name, "hand saw" AS double_quoted, 'hand saw' AS single_quoted FROM tools;

name     | double_quoted | single_quoted
---------+---------------+--------------
hand saw | hand saw      | hand saw
(1 row)

In standard SQL, single quotes make text and double quotes name a column or table. "hand saw" should therefore be read as "the column called hand saw", and in PostgreSQL that query fails outright. SQLite is more forgiving than is good for you: when a double-quoted name matches no column, it silently treats it as text instead. The query runs, you learn nothing, and the same query breaks the day you point it at a real server. Use single quotes for values, always.

Forgetting the semicolon between statements

CREATE TABLE tools (id INTEGER PRIMARY KEY, name TEXT)
INSERT INTO tools (name) VALUES ('hand saw');
SELECT * FROM tools;

Error in the statement on line 1: near "INSERT": syntax error

The semicolon is what ends a statement, so without one SQLite reads the first two lines as a single nonsensical statement and stops. The error names the line the failing statement starts on, which is line 1 here rather than line 2 where you would probably look first — worth knowing, because it is the same in every error you will meet in this playground.

Expecting a declared type to be enforced

CREATE TABLE tools (id INTEGER PRIMARY KEY, name TEXT, deposit INTEGER);
INSERT INTO tools (name, deposit) VALUES ('hand saw', 'three hundred');
SELECT name, deposit, typeof(deposit) AS stored_as FROM tools;

name     | deposit       | stored_as
---------+---------------+----------
hand saw | three hundred | text
(1 row)

deposit was declared INTEGER and a sentence went into it anyway. This is genuine SQLite behaviour, not a quirk of the playground: outside of a table created as STRICT, a column's declared type is a preference rather than a rule. PostgreSQL and MySQL both reject that insert. If you want SQLite to reject it too, add the word STRICT after the closing bracket of CREATE TABLE, and remember when you move to another database that it was checking all along and SQLite was not.

Next steps

You can now build a table, fill it, and ask for chosen and computed columns out of it. Every query so far has returned every row, which is almost never what a real question needs — the next lesson, filtering rows with WHERE, is about cutting the result down to the rows you actually mean, including the traps NULL sets for you. Before that, take any block above into the SQL playground, add a column to the SELECT list, and run it again.