Basic

Asking a question

I wanted one column and I typed SELECT and got the whole table back. The whole lesson is that SELECT is a question, and the more precisely you ask, the less it hands you.

What you will see

name|id Ada|1 Grace|2 Alan|3 sqlite>

About 2 min of reading

Try It

Fill a table with a few rows, then ask for it in three different shapes and watch the answer change.

Add two more rows so the table is not a single line. Each INSERT is one row, values in column order.

INSERT INTO users (id, name) VALUES (2, "Grace"); INSERT INTO users (id, name) VALUES (3, "Alan");

Before you run it, say out loud what it will print. Then open the answer.

What it prints

sqlite>

Ask for everything with the star. You get both columns for all three rows.

SELECT * FROM users;

Before you run it, say out loud what it will print. Then open the answer.

What it prints

id|name 1|Ada 2|Grace 3|Alan sqlite>

Ask for one column only. The answer is a single line of names, no ids.

SELECT name FROM users;

Before you run it, say out loud what it will print. Then open the answer.

What it prints

name Ada Grace Alan sqlite>

Ask for the columns in your own order. The header flips and so does each row.

SELECT name, id FROM users;

Before you run it, say out loud what it will print. Then open the answer.

What it prints

name|id Ada|1 Grace|2 Alan|3 sqlite>

Data Flow

SELECT names the columns you want, FROM names the table, and the database reads the rows and returns just those columns.

1 You write SELECT with column names

↓

2 FROM points at a table

↓

3 The database reads the rows

↓

4 Only your columns come back

Why this works

SELECT is a question

The word SELECT does not mean select some text. It means: here is what I want to see. The FROM names where to look. Between them you list the columns you want. The database reads the rows and hands back only the columns you asked for, in the order you asked.

The star is a shortcut

* means every column. It saves typing but it also means the answer changes shape if the table grows. When you are writing something you will read later, name the columns. When you are exploring a table you have never seen, the star is the fastest way to look.

Examples

Everything, the lazy ask

SELECT * FROM users;

The star means every column. This is fine when a table is small and you are exploring. It is lazy when you only care about one field, because it drags the rest across too.

Name what you want

SELECT name FROM users;

Now the database returns one column. Order matters to you: the columns come back in the order you wrote them, not the order they were declared. Put name first and it is first.

A different order

SELECT name, id FROM users;

Same two columns, swapped. The header reads name|id. You are not changing the table, you are choosing how the answer is arranged for you.

Words this lesson uses

Each one is defined here on this site. Open the entry for the full definition and the other words it points at.

← The file that is not a file

Previous Lesson

The file that is not a file

Next Lesson

Only the rows that match

Only the rows that match →