Open the info page of your class WhatsApp group. The member list shows only a small photo and a name. But each member has more data than that: a phone number, a profile note, the time they were last online. And the member list does not change just because you looked at it.
In a database, you read data with the SELECT command. The members' full data is like a table. You name the columns you want (like the photo and the name) and the table they come from. The database answers with a table, called the query's result, like that list with only photos and names. The original table does not change, however often you read it.
SELECT and FROM
The students table now holds 5 students: Dewi, Rizky and Maya from the last lesson, plus Andi and Budi. The query below fetches all of its columns:
SELECT * FROM students;
| id | name | grade | city |
|---|---|---|---|
| 1 | Dewi | 90 | London |
| 2 | Rizky | 85 | Manchester |
| 3 | Maya | 92 | Leeds |
| 4 | Andi | 90 | London |
| 5 | Budi | 88 | Manchester |
If you need only the names and cities, write the column names after SELECT, separated by commas:
SELECT name, city FROM students;
| name | city |
|---|---|
| Dewi | London |
| Rizky | Manchester |
| Maya | Leeds |
| Andi | London |
| Budi | Manchester |
SELECTis followed by the list of columns to fetch, separated by commas. The columns in the result are in the order you write them.*means every column.FROM studentsnames the table the data comes from.- There are still 5 rows. SELECT chooses columns. Choosing rows is the job of
WHERE, which the Filtering Data lesson explains.
Aliases with AS
AS gives a column a new heading in the query's result. That new heading is called an alias:
SELECT name AS full_name, city AS town FROM students;
| full_name | town |
|---|---|
| Dewi | London |
| Rizky | Manchester |
| Maya | Leeds |
| Andi | London |
| Budi | Manchester |
name AS full_namemeans "fetch thenamecolumn, and give it the headingfull_namein the result".- Only the heading in this query's result changes. In the
studentstable the column is still calledname. That is whySELECT full_name FROM students;is answered withno such column: full_name. - An alias with a space in it goes inside double quotes:
name AS "student name".
Before you go on, guess. In SELECT name city FROM students;, the comma between name and city is missing. What is the result?
The result is not an error. It is one column with the heading city that holds the students' names. The reason: the word AS can be left out, so name full_name means the same as name AS full_name. Without the comma, SELECT name city FROM students; is read as "fetch the name column, and give it the heading city".
DISTINCT: drop repeated rows
The query below fetches only the city column:
SELECT city FROM students;
The result has 5 rows: London, Manchester, Leeds, London, Manchester. London and Manchester appear twice. Add DISTINCT to remove the repeated rows:
SELECT DISTINCT city FROM students;
| city |
|---|
| London |
| Manchester |
| Leeds |
Of the 5 rows, 3 remain. DISTINCT goes right after SELECT, and it compares the whole result row, not just one column. For example, SELECT DISTINCT name, city still gives 5 rows, because no two students have both the same name and the same city.
⚠️ Commas, aliases, and SELECT * in an app
- A comma after the last column.
SELECT name, city, FROM students;is answered withnear "FROM": syntax error. - A missing comma between columns. There is no error, but the second column name is read as an alias for the first column. If the result has one column too few, check its headings.
- Using an alias as a column name. An alias exists only in the result of the query that creates it. In another query, the answer is
no such column: full_name. SELECT *in app code.SELECT *is fine for a quick look at a table. In app code, name only the columns you use, so that a secret column likepasswordis not fetched too.
TL;DR: SELECT column1, column2 FROM table; reads the columns you name from every row of a table, without changing that table. * means every column. AS gives a column a new heading in the query's result (name AS full_name). That new heading is called an alias. The column's name in the table does not change. The word AS can be left out. So if you forget the comma between two columns, the second one is read as an alias for the first, with no error. DISTINCT, written right after SELECT, removes result rows that are exactly the same: in a table of 5 students from 3 cities, SELECT DISTINCT city gives 3 rows.
⚡ Pick the columns
Write:
SELECT name, city FROM students;
Meaning: This query reads: fetch the name and city columns from the students table. After SELECT you write the columns you want, separated by commas. * means every column. The students table now holds 5 students, and the result still has 5 rows: SELECT chooses columns, not rows. The table itself does not change.
⚡ A new heading with AS
Write:
SELECT name AS full_name, city AS town FROM students;
Meaning: name AS full_name means: fetch the name column, and give it the heading full_name in the query's result. That new heading is called an alias. Only the heading in this query's result changes. In the students table, the column is still called name.
⚡ The forgotten comma
Guess first: in SELECT name city FROM students;, the comma between the two columns is missing. What is the result?
Not an error. The result is one column with the heading city that holds the students' names. The word AS can be left out, so city is read as an alias for the name column.
Meaning: If a query's result has one column too few, check the column headings: a comma may be missing.
⚡ Drop the repeats
Write:
SELECT DISTINCT city FROM students;
Meaning: DISTINCT, written right after SELECT, removes result rows that are exactly the same. The 5 students in this table come from 3 cities, so the result has 3 rows: London, Manchester, Leeds.