In MySQL, WHERE clause is used with SELECT, SET, DELETE and UPDATE statements to specify constraints on rows. It is used to extract only those records that fulfill a specified criterion.
Selection Based on Condition
Consider the following table:
| id | name | age | gender | salary |
|---|---|---|---|---|
| 1 | Mark | 25 | M | 20000 |
| 2 | John | 27 | M | 25000 |
| 3 | Mary | 29 | F | 28000 |
| 4 | Rachel | 26 | F | 23000 |
| 5 | Peter | 28 | M | 26000 |
If we want to select only those records whose age is greater than 25:
import mysql.connector mydb = mysql.connector.connect( host="localhost", user="yourusername", password="yourpassword", database="mydatabase" ) mycursor = mydb.cursor() sql = "SELECT * FROM employees WHERE age > 25" mycursor.execute(sql) myresult = mycursor.fetchall() for x in myresult: print(x)
The output will be:
(2, 'John', 27, 'M', 25000) (3, 'Mary', 29, 'F', 28000) (4, 'Rachel', 26, 'F', 23000) (5, 'Peter', 28, 'M', 26000)
SQL Wildcard with WHERE Clause
To achieve different search constraints on records, MySQL provides the use of SQL wildcards. Wildcards are special characters that can represent one or more than one character in a string search.
Consider the following table:
| id | name |
|---|---|
| 1 | John |
| 2 | Kelly |
| 3 | Ken |
| 4 | Peter Chan |
If we want to select only those records where the name starts with ‘Ke’, then we write the following query:
import mysql.connector mydb = mysql.connector.connect( host="localhost", user="yourusername", password="yourpassword", database="mydatabase" ) mycursor = mydb.cursor() sql = "SELECT * FROM customers WHERE name LIKE 'Ke%'" mycursor.execute(sql) myresult = mycursor.fetchall() for x in myresult: print(x)
The output will be:
(2, 'Kelly') (3, 'Ken')
Another example:
import mysql.connector mydb = mysql.connector.connect( host="localhost", user="yourusername", password="yourpassword", database="mydatabase" ) mycursor = mydb.cursor() sql = "SELECT * FROM customers WHERE name LIKE '%n%'" mycursor.execute(sql) myresult = mycursor.fetchall() for x in myresult: print(x)
In the above example, we’re selecting only those records whose name contains the character ‘n’ and it can occur at the start or end of the name or anywhere in between.
The output will be:
(1, 'John') (2, 'Kelly') (3, 'Ken') (4, 'Peter Chan')
AND, OR and NOT
The AND keyword is used to combine two or more conditions while retrieving data from a single table in MySQL. In general, we can say that the AND operator only retrieves information if ALL the conditions given in the query with the AND keyword are met.
If we want to select only those records where age is greater than 25 and salary is greater than 26000:
import mysql.connector mydb = mysql.connector.connect( host="localhost", user="yourusername", password="yourpassword", database="mydatabase" ) mycursor = mydb.cursor() sql = "SELECT * FROM employees WHERE age > 25 AND salary > 26000" mycursor.execute(sql) myresult = mycursor.fetchall() for x in myresult: print(x)
The output will be:
(3, 'Mary', 29, 'F', 28000)
The OR keyword is used to combine two or more conditions while retrieving data from a single table in MySQL. In general, we can say that the OR operator retrieves information if ANY of the conditions given in the query with the OR keyword are met.
If we want to select only those records where age is greater than 25 OR salary is greater than 26000:
import mysql.connector mydb = mysql.connector.connect( host="localhost", user="yourusername", password="yourpassword", database="mydatabase" ) mycursor = mydb.cursor() sql = "SELECT * FROM employees WHERE age > 25 OR salary > 26000" mycursor.execute(sql) myresult = mycursor.fetchall() for x in myresult: print(x)
The output will be:
(2, 'John', 27, 'M', 25000) (3, 'Mary', 29, 'F', 28000) (4, 'Rachel', 26, 'F', 23000) (5, 'Peter', 28, 'M', 26000)
The NOT keyword is used to exclude rows that do not match a specific criterion.
If we want to select only those records whose age is NOT greater than 25:
import mysql.connector mydb = mysql.connector.connect( host="localhost", user="yourusername", password="yourpassword", database="mydatabase" ) mycursor = mydb.cursor() sql = "SELECT * FROM employees WHERE NOT age > 25" mycursor.execute(sql) myresult = mycursor.fetchall() for x in myresult: print(x)
The output will be:
(1, 'Mark', 25, 'M', 20000)
IN Operator
The IN operator is used to specify multiple possible values for a column in a MySQL query.
Consider the following table:
| id | name |
|---|---|
| 1 | John |
| 2 | Kelly |
| 3 | Ken |
| 4 | Peter Chan |
If we want to select only those records where name is John or Kelly:
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="yourusername",
password="yourpassword",
database="mydatabase"
)
mycursor = mydb.cursor()
sql = "SELECT * FROM customers WHERE name IN ('John', 'Kelly')"
mycursor.execute(sql)
myresult = mycursor.fetchall()
for x in myresult:
print(x)
The output will be:
(1, 'John') (2, 'Kelly')
NOT IN Operator
The NOT IN operator is used to specify multiple possible values for a column in a MySQL query but select only records where the specified value does not match.
If we want to select only those records where name is not John or Kelly:
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="yourusername",
password="yourpassword",
database="mydatabase"
)
mycursor = mydb.cursor()
sql = "SELECT * FROM customers WHERE name NOT IN ('John', 'Kelly')"
mycursor.execute(sql)
myresult = mycursor.fetchall()
for x in myresult:
print(x)
The output will be:
(3, 'Ken') (4, 'Peter Chan')
BETWEEN Operator
The BETWEEN operator is used to select values within a range.
Consider the following table:
| id | name | age | gender | salary |
|---|---|---|---|---|
| 1 | Mark | 25 | M | 20000 |
| 2 | John | 27 | M | 25000 |
| 3 | Mary | 29 | F | 28000 |
| 4 | Rachel | 26 | F | 23000 |
| 5 | Peter | 28 | M | 26000 |
If we want to select only those records where salary is between 24000 and 27000:
import mysql.connector mydb = mysql.connector.connect( host="localhost", user="yourusername", password="yourpassword", database="mydatabase" ) mycursor = mydb.cursor() sql = "SELECT * FROM employees WHERE salary BETWEEN 24000 AND 27000" mycursor.execute(sql) myresult = mycursor.fetchall() for x in myresult: print(x)
The output will be:
(2, 'John', 27, 'M', 25000) (5, 'Peter', 28, 'M', 26000)
LIKE Operator
The LIKE operator is used to search for a specific pattern in a column. The % wildcard is used to represent any string of any length, and the _ (underscore) is used to represent any single character in the pattern.
Consider the following table:
| id | name |
|---|---|
| 1 | John |
| 2 | Kelly |
| 3 | Ken |
| 4 | Peter Chan |
If we want to select only those records where name contains the string ‘ke’:
import mysql.connector mydb = mysql.connector.connect( host="localhost", user="yourusername", password="yourpassword", database="mydatabase" ) mycursor = mydb.cursor() sql = "SELECT * FROM customers WHERE name LIKE '%ke%'" mycursor.execute(sql) myresult = mycursor.fetchall() for x in myresult: print(x)
The output will be:
(2, 'Kelly') (3, 'Ken')
Conclusion
In this tutorial, we have learned how to select records from a MySQL database using the WHERE clause in Python. We have covered using basic operators, wildcards and various other methods for filtering data from the database.
