SQL and MySQL course · Module 2: Querying data (SELECT)

Choosing columns - only what you need

4 min read ·
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:

idtytulautor_idkategoria_idrok_wydaniacenadostepna
1Iliada11199039.901
2Odyseja11199042.001
3Państwo22200155.500
…………………

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:

tytulcena
Iliada39.90
Odyseja42.00
Państwo55.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:

cenatytul
39.90Iliada
42.00Odyseja
55.50Pań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:

tytulcenarok_wydania
Iliada39.901990
Odyseja42.001990
Państwo55.502001
………

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 word cena as a new name for the tytul field 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, not tytuł. For MySQL "ł" and "l" are different characters, so it will not find a field named tytuł 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. 1. How does the result of "SELECT * FROM ksiazki;" differ from the result of "SELECT tytul, cena FROM ksiazki;"?

  2. 2. What determines the order of columns in the answer to the query "SELECT cena, tytul FROM ksiazki;"?

  3. 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.

  1. QuestionThe asterisk vs chosen columns
  2. QuestionColumn order in the result
  3. Coding taskTitle and price of books
  4. Put in orderA query for title and price
  5. QuestionWhen to use the asterisk
  6. Coding taskThree columns of a book

Useful articles