Databases & SQL · Class 12 · Querying with SELECT

MySQLSELECT⏱️ 7 min read

Selecting Columns

This is the command you will write more than all the others put together, and the only one in the course that changes nothing. A select asks a question and gets a resultset back; the table is exactly as it was afterwards.

1The syntax

select <col1>, <col2>, … from <table name>;

Two parts. select says which columns you want; from says which table to take them out of.

Every example in this chapter runs against the full students table — twelve rows, six columns:

MySQL command line client

mysql> select * from students;

+------+--------+-------+---------+------------+----------+

+------+--------+-------+---------+------------+----------+

| 1099 | Veena | 10 | 90.5 | 2010-11-20 | Singtam |

| 1187 | Anjali | 12 | 91 | 2008-03-04 | Gangtok |

| 1250 | Sohan | 12 | 66 | 2008-12-01 | NULL |

| 1402 | Lhamu | 10 | 95.2 | 2010-09-30 | Namchi |

| 2035 | Ravi | 12 | 83.75 | 2008-08-13 | Gangtok |

| 2044 | Karma | 11 | 78.5 | 2009-07-19 | Singtam |

| 2199 | Diana | 11 | 88.25 | 2009-05-16 | Kolkata |

| 2500 | Bhim | 12 | NULL | 2008-06-22 | Singtam |

| 3033 | Tom | 8 | 64.25 | 2013-02-25 | Kolkata |

| 3120 | Pema | 10 | NULL | 2010-03-28 | Namchi |

| 3301 | Farhan | 9 | 72.8 | 2012-03-09 | Siliguri |

| 3612 | Nima | 9 | 59.4 | 2012-11-11 | NULL |

+------+--------+-------+---------+------------+----------+

12 rows in set (0.00 sec)

2One column

MySQL command line client

mysql> select name from students;

+--------+

| name |

+--------+

| Veena |

| Anjali |

| Sohan |

| Lhamu |

| Ravi |

| Karma |

| Diana |

| Bhim |

| Tom |

| Pema |

| Farhan |

| Nima |

+--------+

12 rows in set (0.00 sec)

One column came back, and still twelve rows. That is the point to hold on to: choosing columns does not reduce the number of rows. Every student is still represented; you are just looking at a narrower slice of each one.

3Several columns

Separate the names with commas:

MySQL command line client

mysql> select id, name, city from students;

+------+--------+----------+

| id | name | city |

+------+--------+----------+

| 1099 | Veena | Singtam |

| 1187 | Anjali | Gangtok |

| 1250 | Sohan | NULL |

| 1402 | Lhamu | Namchi |

| 2035 | Ravi | Gangtok |

| 2044 | Karma | Singtam |

| 2199 | Diana | Kolkata |

| 2500 | Bhim | Singtam |

| 3033 | Tom | Kolkata |

| 3120 | Pema | Namchi |

| 3301 | Farhan | Siliguri |

| 3612 | Nima | NULL |

+------+--------+----------+

12 rows in set (0.00 sec)

You choose the order

The resultset shows the columns in the order you named them, not the order they sit in the table. select city, name from students; would put the city first. The table itself is unaffected either way.

4All the columns, with *

You could name all six columns every time. It is exact, and it is tedious. * means every column:

the long way

select id, name, grade, percent, dob, city from students;

the same thing

select * from students;

With * the columns come out in the table's own order, because you have not given one.

Name the columns when you only need a few

select * is perfect while you are exploring a table. In a real query it is worth naming what you want: the resultset is narrower and easier to read, and it will not silently change shape if somebody adds a column to the table next month.

5Two mistakes

A misspelt column name:

MySQL command line client

mysql> select nam from students;

ERROR 1054 (42S22): Unknown column 'nam' in 'field list'

field list is MySQL's name for the part between select and from, so the error is telling you both what is wrong and where to look.

And a misspelt table name:

MySQL command line client

mysql> select * from student;

ERROR 1146 (42S02): Table 'lambdalab.student' doesn't exist

Two different error numbers for two different mistakes — 1054 for a column, 1146 for a table. Reading which one you got saves you looking in the wrong place.