SQL and MySQL course · Module 2: Querying data (SELECT)
Choosing columns - only what you need
In this lesson5
Readers at the Library entrance always ask the same thing: which books do we have and how much does a copy cost. I want to hang a simple price list by the door. The ksiazki shelf, however, has seven fields, and for the price list I need only two: the title and the price.
At the Gate location you met the asterisk *. It is convenient, but it brings every field - including the ones you do not need.
SELECT * - the whole shelf
Let us start with what you already know. The asterisk after SELECT means: "give me every field of this shelf".
SELECT * FROM ksiazki;The Library returns all fields: id, tytul, autor_id, kategoria_id, rok_wydania, cena, dostepna. Here are the first three scrolls of the answer:
| id | tytul | autor_id | kategoria_id | rok_wydania | cena | dostepna |
|---|---|---|---|---|---|---|
| 1 | Iliada | 1 | 1 | 1990 | 39.90 | 1 |
| 2 | Odyseja | 1 | 1 | 1990 | 42.00 | 1 |
| 3 | Państwo | 2 | 2 | 2001 | 55.50 | 0 |
| … | … | … | … | … | … | … |
For a price list that is too much - like asking the scribe for an entire cabinet when you need a single card. A reader does not care about autor_id or kategoria_id.
Selected columns - thrift and order
When you name the fields separated by commas instead of the asterisk, you get exactly what you want:
SELECT tytul, cena FROM ksiazki;This means: "Show me the title and price of every book - nothing more". The same first three scrolls now look like this:
| tytul | cena |
|---|---|
| Iliada | 39.90 |
| Odyseja | 42.00 |
| Państwo | 55.50 |
| … | … |
Notice what changed and what did not. There are still seventeen rows - every book on the shelf has its row in the answer. Only the columns are fewer: of seven, two remain. The column list after SELECT does not remove scrolls by itself; it only chooses fields.
Why choose specific columns?
- Readability - the result holds only the relevant fields.
- Speed - the Library does not haul unnecessary data.
- Control - you decide the order of the fields in the answer.
The order of columns
The order of columns in the result follows the order on the SELECT list, not the order of fields on the shelf. Let us swap the title and the price:
SELECT cena, tytul FROM ksiazki;Here the price appears before the title:
| cena | tytul |
|---|---|
| 39.90 | Iliada |
| 42.00 | Odyseja |
| 55.50 | Państwo |
| … | … |
The data is the same; only the layout of the answer has changed. You can name as many fields as you like - two, three, however many - as long as they are separated by commas:
SELECT tytul, cena, rok_wydania FROM ksiazki;The three fields stand in the order of the list:
| tytul | cena | rok_wydania |
|---|---|---|
| Iliada | 39.90 | 1990 |
| Odyseja | 42.00 | 1990 |
| Państwo | 55.50 | 2001 |
| … | … | … |
Common mistakes
A column list looks innocent, but it is easy to slip up. Here are the three most common mistakes:
- A missing comma.
SELECT tytul cena FROM ksiazki;raises no error. MySQL takes the wordcenaas a new name for thetytulfield and shows one column instead of two. This mechanism is called an alias - you will meet it in the next lesson. - A comma before FROM. In
SELECT tytul, cena, FROM ksiazki;a comma follows the last field. MySQL waits for another name and reports a syntax error. - Polish letters. The field is called
tytul, nottytuł. For MySQL "ł" and "l" are different characters, so it will not find a field namedtytułand will report an error.
My advice: in reports and price lists always name the specific fields. The asterisk also returns fields that someone adds to the shelf a year from now, and your price list suddenly grows columns nobody ordered.
Leave the asterisk for a quick scouting glance over an unknown shelf. In daily work choose your fields deliberately, and the answer will have exactly the columns the reader asks for.
Spotted a mistake in this lesson?
Check yourself
Answer the questions from this lesson. Pick an answer to see right away whether it is correct.
1. How does the result of "SELECT * FROM ksiazki;" differ from the result of "SELECT tytul, cena FROM ksiazki;"?
2. What determines the order of columns in the answer to the query "SELECT cena, tytul FROM ksiazki;"?
3. When is it better in daily work to name specific columns instead of the asterisk *?
Hands-on tasks in the game
- Code editor
Write a query that retrieves from the ksiazki table only two columns: tytul and cena.
- Horizontal ordering
Arrange the elements in the correct order to build a query that retrieves the title and price of books (left to right):
- Code editor
Write a query that retrieves from the ksiazki table three columns: tytul, cena and rok_wydania.