SQL WHERE Statement
SQL WHERE Statement Introduction
- WHERE clause is used to include a condition while fetching data from tables.
- When you have to specify a condition that has to be obeyed while data is pulled from tables, in that case where clause is used.
- Where clause is used to filter the records that you retrieve from the select statement so that you get a smaller subset of data.
- Where clause is also used to perform join operations.
- Where clause returns only those records from the table which meets the condition specified with it.
- This clause is not only used with SELECT Query, but can also be used with UPDATE and DELETE Queries.
- Using where clause with SELECT query
- Where clause with SELECT query to retrieve all the columns from a table.
- To retrieve all records from a table, asterisk(*) symbol is used.
Syntax:
SELECT *FROM TABLE_NAME WHERE condition;
Example:
First, we will create a database with name “bookdb”. Then in that database we will create a table “book” and insert records into the table. We will consider the same database and also the same table for subsequent examples.
Now, we will apply where clause with SELECT query to retrieve all the columns from the table where the price of book is equal to 200.
mysql> CREATE DATABASE bookdb;
Query OK, 1 row affected (0.07 sec)
mysql> USE bookdb;
Database changed
mysql> CREATE TABLE book(Book_ID INT NOT NULL AUTO_INCREMENT, Book_Name VARCHAR(100) NOT NULL, Book_Author VARCHAR(40) NOT NULL, Book_Price INT NOT NULL, PRIMARY KEY(Book_ID));
Query OK, 0 rows affected (0.24 sec)
mysql> INSERT INTO book(Book_Name,Book_Author,Book_Price) VALUES ("Learn MySQL","Abdul S", 180);
Query OK, 1 row affected (0.07 sec)
mysql> INSERT INTO book(Book_Name,Book_Author,Book_Price) VALUES ("MySQL Explained","Andrew Comeau", 150);
Query OK, 1 row affected (0.04 sec)
mysql> INSERT INTO book(Book_Name,Book_Author,Book_Price) VALUES ("MySQL Cookbook","Paul DuBois", 250);
Query OK, 1 row affected (0.08 sec)
mysql> INSERT INTO book(Book_Name,Book_Author,Book_Price) VALUES ("murach's MySQL","Joel Murach", 200);
Query OK, 1 row affected (0.07 sec)
mysql> INSERT INTO book(Book_Name,Book_Author,Book_Price) VALUES ("Head First SQL","Lynn Beighley", 300);
Query OK, 1 row affected (0.07 sec)
mysql> SELECT *FROM book;
+---------+-----------------------------+--------------------+------------+
| Book_ID | Book_Name | Book_Author | Book_Price |
+---------+-----------------------------+--------------------+------------+
| 1 | Learn MySQL | Abdul S | 180 |
| 2 | MySQL Explained | Andrew Comeau | 150 |
| 3 | Database Management Systems | Raghu Ramakrishnan | 250 |
| 4 | murach's MySQL | Joel Murach | 200 |
+---------+-----------------------------+--------------------+------------+
4 rows in set (0.00 sec)
mysql> SELECT *FROM book WHERE Book_Price=200;
+---------+----------------+-------------+------------+
| Book_ID | Book_Name | Book_Author | Book_Price |
+---------+----------------+-------------+------------+
| 4 | murach's MySQL | Joel Murach | 200 |
+---------+----------------+-------------+------------+
1 row in set (0.00 sec)
There is only one record with Book_ID =4, whose price is equal to 200. Hence all the columns of that particular record are displayed.
- Where clause with SELECT query to retrieve one or more than one specific columns from a table.
- To retrieve specific columns from a table, names of all the columns which are to be retrieved should be specified in the query itself.
- The specific column names which are to be retrieved will be separated by comma.
Syntax:
SELECT COLUMN_NAME1,….,COLUMN_NAMEn FROM TABLE_NAME WHERE condition;
Example:
We will apply where clause with SELECT query to retrieve specific columns (Book_ID, Book_Price) from the book table where price of the book is equal to 200.
mysql> SELECT * FROM book; +---------+-----------------------------+--------------------+------------+ | Book_ID | Book_Name | Book_Author | Book_Price | +---------+-----------------------------+--------------------+------------+ | 1 | Learn MySQL | Abdul S | 180 | | 2 | MySQL Explained | Andrew Comeau | 150 | | 3 | Database Management Systems | Raghu Ramakrishnan | 250 | | 4 | murach's MySQL | Joel Murach | 200 | +---------+-----------------------------+--------------------+------------+ 4 rows in set (0.00 sec) mysql> SELECT Book_ID,Book_Price FROM book where Book_Price=200; +---------+------------+ | Book_ID | Book_Price | +---------+------------+ | 4 | 200 | +---------+------------+ 1 row in set (0.00 sec)
There is only one record with Book_ID = 4, whose price is equal to 200. Hence, Book_ID and Book_Price of that particular record is displayed.
- Using where clause with UPDATE query
Syntax:
UPDATE TABLE_NAME SET column_name = newvalue WHERE column_name1 = value1;
Example:
We will apply where clause with UPDATE query on book table to update the Book_Name and Book_Author of a particular record by specifying the Book_ID as a condition in where clause.
mysql> SELECT *FROM book; +---------+-----------------+---------------+------------+ | Book_ID | Book_Name | Book_Author | Book_Price | +---------+-----------------+---------------+------------+ | 1 | Learn MySQL | Abdul S | 180 | | 2 | MySQL Explained | Andrew Comeau | 150 | | 3 | MySQL Cookbook | Paul DuBois | 250 | | 4 | murach's MySQL | Joel Murach | 200 | | 5 | Head First SQL | Lynn Beighley | 300 | +---------+-----------------+---------------+------------+ 5 rows in set (0.00 sec) mysql> UPDATE book SET Book_name="Database Management Systems", Book_Author="Raghu Ramakrishnan" WHERE Book_ID=3; Query OK, 1 row affected (0.19 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> SELECT *FROM book; +---------+-----------------------------+--------------------+------------+ | Book_ID | Book_Name | Book_Author | Book_Price | +---------+-----------------------------+--------------------+------------+ | 1 | Learn MySQL | Abdul S | 180 | | 2 | MySQL Explained | Andrew Comeau | 150 | | 3 | Database Management Systems | Raghu Ramakrishnan | 250 | | 4 | murach's MySQL | Joel Murach | 200 | | 5 | Head First SQL | Lynn Beighley | 300 | +---------+-----------------------------+--------------------+------------+ 5 rows in set (0.00 sec)
There is only one record with Book_ID =3, we have changed the Book_name and Book_Author of that record. Hence, updated table is displayed.
- Using where clause with DELETE query
Syntax:
DELETE FROM TABLE_NAME WHERE column_name1 = value1;
Example:
We will apply where clause with DELETE query on book table to
delete a book with particular Book_ID by specifying the Book_ID as a condition in where clause.
mysql> SELECT *FROM book; +---------+-----------------------------+--------------------+------------+ | Book_ID | Book_Name | Book_Author | Book_Price | +---------+-----------------------------+--------------------+------------+ | 1 | Learn MySQL | Abdul S | 180 | | 2 | MySQL Explained | Andrew Comeau | 150 | | 3 | Database Management Systems | Raghu Ramakrishnan | 250 | | 4 | murach's MySQL | Joel Murach | 200 | | 5 | Head First SQL | Lynn Beighley | 300 | +---------+-----------------------------+--------------------+------------+ 5 rows in set (0.00 sec) mysql> DELETE FROM book WHERE Book_ID=5; Query OK, 1 row affected (0.23 sec) mysql> SELECT *FROM book; +---------+-----------------------------+--------------------+------------+ | Book_ID | Book_Name | Book_Author | Book_Price | +---------+-----------------------------+--------------------+------------+ | 1 | Learn MySQL | Abdul S | 180 | | 2 | MySQL Explained | Andrew Comeau | 150 | | 3 | Database Management Systems | Raghu Ramakrishnan | 250 | | 4 | murach's MySQL | Joel Murach | 200 | +---------+-----------------------------+--------------------+------------+ 4 rows in set (0.00 sec)
There is only one record with Book_ID =5, we have deleted that entire record from the book table. Hence, updated table is displayed.
Operators with WHERE Statement
You can use operators with the where clause. These operators can be used along with the where clause in SELECT, UPDATE and DELETE queries.
- Equal(=)
When equal to (=) operator is used with where clause, it will retrieve those records from the table wherever the value of the column name present in a table is equal to the value of the column name specified in the query.
Example:
We will apply where clause with SELECT query to retrieve those records from the table where the price of book is equal to 200.
mysql> SELECT *FROM book; +---------+-----------------------------+--------------------+------------+ | Book_ID | Book_Name | Book_Author | Book_Price | +---------+-----------------------------+--------------------+------------+ | 1 | Learn MySQL | Abdul S | 180 | | 2 | MySQL Explained | Andrew Comeau | 150 | | 3 | Database Management Systems | Raghu Ramakrishnan | 250 | | 4 | murach's MySQL | Joel Murach | 200 | +---------+-----------------------------+--------------------+------------+ 4 rows in set (0.00 sec) mysql> SELECT *FROM book WHERE Book_Price=200; +---------+----------------+-------------+------------+ | Book_ID | Book_Name | Book_Author | Book_Price | +---------+----------------+-------------+------------+ | 4 | murach's MySQL | Joel Murach | 200 | +---------+----------------+-------------+------------+ 1 row in set (0.00 sec)
There is only one record with Book_ID =4, whose price is equal to 200. Hence all the columns of that particular record are displayed.
- Greater than(>)
When greater than (>) operator is used with Where clause, it will retrieve those records from the table wherever the value of the column name present in a table is greater than the value of the column name specified in the query.
Example:
We will apply where clause with SELECT query to retrieve those records from the table where the price of book is greater than 150.
mysql> SELECT * FROM book; +---------+-----------------------------+--------------------+------------+ | Book_ID | Book_Name | Book_Author | Book_Price | +---------+-----------------------------+--------------------+------------+ | 1 | Learn MySQL | Abdul S | 180 | | 2 | MySQL Explained | Andrew Comeau | 150 | | 3 | Database Management Systems | Raghu Ramakrishnan | 250 | | 4 | murach's MySQL | Joel Murach | 200 | +---------+-----------------------------+--------------------+------------+ 4 rows in set (0.00 sec) mysql> SELECT * FROM book WHERE Book_Price > 150; +---------+-----------------------------+--------------------+------------+ | Book_ID | Book_Name | Book_Author | Book_Price | +---------+-----------------------------+--------------------+------------+ | 1 | Learn MySQL | Abdul S | 180 | | 3 | Database Management Systems | Raghu Ramakrishnan | 250 | | 4 | murach's MySQL | Joel Murach | 200 | +---------+-----------------------------+--------------------+------------+ 3 rows in set (0.00 sec)
There are three record with Book_ID =1, 3 and 4 whose prices are greater than 150. Hence all the columns of those records are displayed.
- Less than(<)
When less than(<) operator is used with where clause, it will retrieve those records from the table wherever the value of the column name present in a table is lesser than the value of the column name specified in the query.
Example:
We will apply where clause with SELECT query to retrieve those records from the table where the price of book is less than 200.
mysql> SELECT * FROM book; +---------+-----------------------------+--------------------+------------+ | Book_ID | Book_Name | Book_Author | Book_Price | +---------+-----------------------------+--------------------+------------+ | 1 | Learn MySQL | Abdul S | 180 | | 2 | MySQL Explained | Andrew Comeau | 150 | | 3 | Database Management Systems | Raghu Ramakrishnan | 250 | | 4 | murach's MySQL | Joel Murach | 200 | +---------+-----------------------------+--------------------+------------+ 4 rows in set (0.00 sec) mysql> SELECT * FROM book WHERE Book_Price < 200; +---------+-----------------+---------------+------------+ | Book_ID | Book_Name | Book_Author | Book_Price | +---------+-----------------+---------------+------------+ | 1 | Learn MySQL | Abdul S | 180 | | 2 | MySQL Explained | Andrew Comeau | 150 | +---------+-----------------+---------------+------------+ 2 rows in set (0.00 sec)
There are two records with Book_ID =1 and 2 whose prices are less than 200. Hence all the columns of those records are displayed.
- Greater than or equal (>=)
When greater than or equal(>=) operator is used with where clause, it will retrieve those records from the table wherever the value of the column name present in a table is greater than or equal to the value of the column name specified in the query.
Example:
We will apply where clause with SELECT query to retrieve those records from the table where the price of book is greater than or equal to 150.
mysql> SELECT * FROM book; +---------+-----------------------------+--------------------+------------+ | Book_ID | Book_Name | Book_Author | Book_Price | +---------+-----------------------------+--------------------+------------+ | 1 | Learn MySQL | Abdul S | 180 | | 2 | MySQL Explained | Andrew Comeau | 150 | | 3 | Database Management Systems | Raghu Ramakrishnan | 250 | | 4 | murach's MySQL | Joel Murach | 200 | +---------+-----------------------------+--------------------+------------+ 4 rows in set (0.00 sec) mysql> SELECT * FROM book WHERE Book_Price >= 150; +---------+-----------------------------+--------------------+------------+ | Book_ID | Book_Name | Book_Author | Book_Price | +---------+-----------------------------+--------------------+------------+ | 1 | Learn MySQL | Abdul S | 180 | | 2 | MySQL Explained | Andrew Comeau | 150 | | 3 | Database Management Systems | Raghu Ramakrishnan | 250 | | 4 | murach's MySQL | Joel Murach | 200 | +---------+-----------------------------+--------------------+------------+ 4 rows in set (0.00 sec)
We can see there are four records with Book_ID =1, 2, 3 and 4 whose prices are greater than or equal to 150. Hence all the columns of those records are displayed.
- Less than or equal (<=)
When less than or equal (<=) operator is used with where clause, it will retrieve those records from the table where ever the value of the column name present in a table is lesser than or equal to the value of the column name specified in the query.
Example:
We will apply where clause with SELECT query to retrieve those records from the table where the price of book is less than or equal to 200.
mysql> SELECT * FROM book; +---------+-----------------------------+--------------------+------------+ | Book_ID | Book_Name | Book_Author | Book_Price | +---------+-----------------------------+--------------------+------------+ | 1 | Learn MySQL | Abdul S | 180 | | 2 | MySQL Explained | Andrew Comeau | 150 | | 3 | Database Management Systems | Raghu Ramakrishnan | 250 | | 4 | murach's MySQL | Joel Murach | 200 | +---------+-----------------------------+--------------------+------------+ 4 rows in set (0.00 sec) mysql> SELECT * FROM book WHERE Book_Price <= 200; +---------+-----------------+---------------+------------+ | Book_ID | Book_Name | Book_Author | Book_Price | +---------+-----------------+---------------+------------+ | 1 | Learn MySQL | Abdul S | 180 | | 2 | MySQL Explained | Andrew Comeau | 150 | | 4 | murach's MySQL | Joel Murach | 200 | +---------+-----------------+---------------+------------+ 3 rows in set (0.00 sec)
There are three records with Book_ID =1, 2 and 4 whose prices are less than or equal to 200. Hence all the columns of those records are displayed.
- Not Equal (<>)
When not equal(<>) operator is used with where clause, it will retrieve those records from the table wherever the value of the column name specified in the query does not matches with the value of the column name present in a table.
Example:
We will apply where clause with SELECT query to retrieve those records from the table where the price of book is not equal to 250.
mysql> SELECT * FROM book; +---------+-----------------------------+--------------------+------------+ | Book_ID | Book_Name | Book_Author | Book_Price | +---------+-----------------------------+--------------------+------------+ | 1 | Learn MySQL | Abdul S | 180 | | 2 | MySQL Explained | Andrew Comeau | 150 | | 3 | Database Management Systems | Raghu Ramakrishnan | 250 | | 4 | murach's MySQL | Joel Murach | 200 | +---------+-----------------------------+--------------------+------------+ 4 rows in set (0.00 sec) mysql> SELECT * FROM book WHERE Book_Price <> 250; +---------+-----------------+---------------+------------+ | Book_ID | Book_Name | Book_Author | Book_Price | +---------+-----------------+---------------+------------+ | 1 | Learn MySQL | Abdul S | 180 | | 2 | MySQL Explained | Andrew Comeau | 150 | | 4 | murach's MySQL | Joel Murach | 200 | +---------+-----------------+---------------+------------+ 3 rows in set (0.00 sec)
There are three records with Book_ID =1, 2 and 4 whose prices are not equal to 250. Hence all the columns of those records are displayed.