DataslopeSELECT Basics
Choose columns from a SQLite table, read every row, reorder output, and remove duplicate values with DISTINCT.
SELECT is the SQL word you will use most often. It asks a database to
read data and return an answer. On this page, you will use SELECT to
choose which columns you want from a table.
A helpful first idea: a SELECT query does not change the stored
table. It creates a temporary result table for you to look at.
SELECT chooses columns
Imagine a table like a spreadsheet. It has rows going down and columns
going across. SELECT names the columns you want to see, and FROM
names the table that holds them.
You asked for title and author, so the result has exactly those two
columns. By default, SQLite returns those columns for every row in
the table.
SELECT star means every column
The asterisk, *, is shorthand for "all columns." It is useful when
you are exploring a table for the first time.
SELECT * is great for a quick peek. In real work, naming only the
columns you need is usually clearer.
If you write SELECT title, price FROM books;, which columns appear in the result?
Every column in the table.
Only title and price.
Only rows where the title has a price.
A count of books.
Choose the order of output columns
The result columns appear in the order you write them. You do not have to follow the table's original column order.
This query does not reorder the stored table. It only changes the shape of the result you receive.
Pick one column
You can select just one column when that is all you need.
Remove duplicates with DISTINCT
Sometimes you do not want every row. You want the list of different
values that appear in a column. DISTINCT removes duplicate result
rows.
There are five books, but only three genres. DISTINCT is perfect for
questions like "what categories exist?"
DISTINCT works on the whole selected row. If you select two columns,
SQLite keeps each unique combination of those two columns.
Check your understanding
What does SELECT * FROM books; return?
Only the first column.
Every column from books.
Only unique rows.
No rows, only column names.
Does a SELECT query change the stored table?
Yes, it deletes columns you did not select.
Yes, it permanently rearranges the columns.
No, it reads data and returns a temporary result.
Only when you use DISTINCT.
A table has genres Fiction, Technology, Fiction, and Travel. What does SELECT DISTINCT genre return?
Four rows, including both Fiction values.
Three rows, one for each different genre.
Only Fiction, because it appears most often.
Nothing, because one value repeats.