MySQL WHERE Clause

This SQL tutorial provides an introduction to the WHERE clause in MySQL, and provides explanations, examples and exercises. For this lesson’s exercises, use this link.

This tutorial is a part of several posts describing how to use the WHERE clause in MySQL. To read additional posts regarding this subject, please use the following links:

• Introduction – MySQL WHERE clause
• Basic comparison operators
• IN operator
• BETWEEN operator
• LIKE operator
• IS NULL operator
• AND & OR operators

Introduction – MySQL WHERE Clause

The MySQL WHERE Clause is used to restrict the rows returned from a query. While the previous chapter (Basic SELECT Statements) explained how to extract the names of all books from Books table, using the MySQL WHERE clause, you are able to restrict the query to rows that meet a certain condition. For example: extract the books whose price is greater than 27, or the books who were written by Frank Herbert

• The MySQL WHERE clause must be written after the MySQL FROM clause (which in turn must be written after the MySQL SELECT clause), this order cannot be changed.
• Unlike the MySQL SELECT and FROM clauses, which are necessary for creating a valid SQL query, the MySQL WHERE clause is optional. A SQL query can function properly with or without the MySQL WHERE clause.

MySQL WHERE Clause Syntax

The MySQL WHERE clause has the following structure:

WHERE  column_name operator value

SELECT *
FROM books
WHERE bookPrice < 30

• Column_name – the name of the column. The query above retrieves all books whose price is less than 30, and therefore the bookPrice column was selected. If you would like to display all books who were written by Frank Herbert, you need to specify the Author column in the WHERE clause.

SELECT *
FROM books
WHERE Author = 'Frank Herbert'

• Operator – there are two types of operators:
  • Basic comparison operators– represented by operators such as = , < , > , => , =<, <>
  • More advanced comparison operators– represented by operators such as IN, BETWEEN, LIKE, AND and OR.

• Comparison value – 
• Number – for example, a price is greater than a certain number.

SELECT bookID, bookName, bookPrice
FROM books
WHERE bookPrice > 6000

• Date – for example, the publication date is greater than a certain date.

SELECT BookID, BookName, PublicationDate 
FROM books
WHERE PublicationDate > '2000-01-23'

• String – for example, a book’s name equals a certain value.

SELECT bookID, bookName, bookPrice
FROM books
WHERE bookName = 'Dune'

General Guidelines

• The value data type must match the column date type  (it makes no sense to try to display the books whose name is greater than 4503).

SELECT bookID , bookName, bookPrice
FROM books
WHERE bookName > 4503
-- (Invalid SQL statement)

•  If the requested comparison does not match the table values (for example, the highest price in the table is 50 and you would like to display the books whose price is greater than 200,000), no result will be retrieved but no error will be generated either (the table could not retrieve values that, based on its settings, do not exist).

SELECT bookID, bookName, bookPrice
FROM books
WHERE bookPrice > 200000
-- (valid MySQL SELECT statement)
-- As described no error will be generated and no rows will be retrieved either