Reading Data — SQL

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…

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

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

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

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.

What you will learn