MySQL | WHERE
Start your free 7-days trial now!
The WHERE clause allows us to add conditions to our query. Only records that meet our condition will be retrieved.
MySQL supports the following conditions:
Condition |
|---|
|
|
|
|
|
|
|
|
|
The formal SQL standard defines = and <> as the operators for equal and not equal. Therefore it is not recommended to use == to represent equality, and != to represent inequality.
Syntax
SELECT column_name(s)FROM table_nameWHERE condition;
Examples
Consider the following table about students' extracurricular activities:
student_id | club | date_entered |
|---|---|---|
1 | Football | 2016-02-13 |
2 | Boxing | 2016-05-25 |
3 | Apple | 2018-08-17 |
4 | Fishing | 2017-01-01 |
5 | NULL | NULL |
The above sample table can be created using the code here.
Basic usage
To retrieve students who were part of a club before 2017-01-01:
SELECT * FROM extracurricularWHERE date_entered < '2017-01-01';
+------------+----------+--------------+| student_id | club | date_entered |+------------+----------+--------------+| 1 | Football | 2016-02-13 || 2 | Boxing | 2016-05-25 |+------------+----------+--------------+
Only students with id 1 and 2 were part of a club before 1 January 2017.
To retrieve students who are part of the Fishing club:
SELECT * FROM extracurricularWHERE club = 'Fishing';
+------------+---------+--------------+| student_id | club | date_entered |+------------+---------+--------------+| 4 | Fishing | 2017-01-01 |+------------+---------+--------------+
Filter missing values
We can use the IS NOT NULL operator to filter out NULL records:
SELECT * FROM extracurricular WHERE club IS NOT NULL;
+------------+----------+--------------+| student_id | club | date_entered |+------------+----------+--------------+| 1 | Football | 2016-02-13 || 2 | Boxing | 2016-05-25 || 3 | Chess | 2018-08-17 || 4 | Fishing | 2017-01-01 |+------------+----------+--------------+
We can see the record for student_id=5 has been filtered out.
