Databases & SQL · Class 12 · Querying with SELECT

MySQLSELECT⏱️ 9 min read

The WHERE Clause

So far every query has returned all twelve students. Useful databases hold thousands of rows and you almost never want all of them. The where clause is how you say which ones — and it is the single most used piece of SQL there is.

1Two clauses, two directions

This is the idea to fix in your head before the syntax, because everything else in the chapter builds on it.

select

picks columns

Cuts the table into vertical stripes. Narrows each row.

where

picks rows

Cuts the table into horizontal slices. Keeps fewer students.

select <columns> from <table name> where <condition>;
       └── which columns ──┘        └── which rows ──┘

Operate the two cuts separately and watch which of the two numbers moves.

Two cuts, one table

Tick columns to change what select takes. Choose a condition to change what where keeps. Watch which number moves.

select — which columns

where — which rows

mysql> select id, name, percent from students;

id	name	percent
1099	Veena	90.5
1187	Anjali	91
1250	Sohan	66
1402	Lhamu	95.2
2035	Ravi	83.75
2044	Karma	78.5
2199	Diana	88.25
2500	Bhim	NULL
3033	Tom	64.25
3120	Pema	NULL
3301	Farhan	72.8
3612	Nima	59.4

12 rows in set — 3 of 6 columns · 0 rows filtered out

columns — chosen by select

rows — chosen by where

Ticking columns never changes the row count, and changing the condition never changes the column count. That is the whole difference between the two clauses.

2A first condition

MySQL command line client

mysql> select id, name, grade from students where city = 'Gangtok';

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

| id | name | grade |

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

| 1187 | Anjali | 12 |

| 2035 | Ravi | 12 |

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

2 rows in set (0.00 sec)

Two rows instead of twelve. MySQL took each row in turn, asked is this row's city equal to Gangtok?, and kept the row only when the answer was yes. That is the whole mechanism — a question asked once per row.

One equals sign, not two

In Python you would write city == 'Gangtok'. SQL has no assignment operator inside a condition, so a single = is the comparison. Writing == here is a syntax error.

3The six relational operators

Comparisons you can use in a where

Operator	Means	Example
=	equal to	city = 'Gangtok'
<	less than	percent < 70
>	greater than	percent > 78
<=	less than or equal to	grade <= 10
>=	greater than or equal to	dob >= '2009-01-01'
<> or !=	not equal to	grade <> 12

Two spellings of 'not equal'

<> and != do exactly the same thing in MySQL. <> is the older, standard SQL spelling and the one the class notes use; != will be familiar from Python. Either is accepted in an exam.

4Comparing numbers

MySQL command line client

mysql> select * from students where percent > 78;

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

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

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

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

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

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

6 rows in set (0.00 sec)

Six of the twelve. Note who is missing: Bhim and Pema have NULL in percent, and an unknown value is not greater than 78 — it is not less than 78 either. Rows with NULL quietly fall out of every comparison, which is the reason IS NULL exists and gets its own lesson shortly.

5Comparing dates and text

Dates compare exactly as you would hope: earlier is “less than”. Students born in 2009 or later:

MySQL command line client

mysql> select id, name, dob from students where dob >= '2009-01-01';

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

| id | name | dob |

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

| 1099 | Veena | 2010-11-20 |

| 1402 | Lhamu | 2010-09-30 |

| 2044 | Karma | 2009-07-19 |

| 2199 | Diana | 2009-05-16 |

| 3033 | Tom | 2013-02-25 |

| 3120 | Pema | 2010-03-28 |

| 3301 | Farhan | 2012-03-09 |

| 3612 | Nima | 2012-11-11 |

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

8 rows in set (0.00 sec)

The date is quoted, exactly as when inserting one, and in the same 'YYYY-MM-DD' form. This is where the year-first format pays off: because it sorts correctly as text, a plain >= gives the right answer.

6Not equal

MySQL command line client

mysql> select id, name, grade from students where grade <> 12;

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

| id | name | grade |

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

| 1099 | Veena | 10 |

| 1402 | Lhamu | 10 |

| 2044 | Karma | 11 |

| 2199 | Diana | 11 |

| 3033 | Tom | 8 |

| 3120 | Pema | 10 |

| 3301 | Farhan | 9 |

| 3612 | Nima | 9 |

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

8 rows in set (0.00 sec)

Eight rows, and four students are in grade 12 — eight plus four is twelve, so every row is accounted for. That arithmetic is a quick way to sanity-check a condition and its opposite.