Logo
×

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.
  1. 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)
SQL WHERE Statement
SQL WHERE Statement
SQL WHERE Statement

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)
SQL WHERE Statement

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)
SQL WHERE Statement

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)
SQL WHERE Statement

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.

  1. 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)
SQL WHERE Statement

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)
SQL WHERE Statement

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)
SQL WHERE Statement

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)
SQL WHERE Statement

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)
SQL WHERE Statement

 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)
SQL WHERE Statement

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.

Related Topics

DELETE VS DROP in SQL

This page contains all the information about the Delete command and Drop command concept and the difference between the DELETE and DROP commands in SQL. What is the DELETE command in...

3 minutes read.

How to Update Table in SQL?

How to Update Table in SQL Introduction UPDATE query is used to update a record in a table. UPDATE is a DML command, which operates on the data of the table and not...

4 minutes read.

How to use INNER JOIN in SQL

In this article, we will learn about the INNER JOIN concept and how to use it in SQL with the WHERE clause. What is INNER JOIN in SQL? Inner Join is a...

6 minutes read.

SQL SET Operator

In this tutorial, we will understand the operator who falls under the SET operator in SQL with the help of different examples. The SQL SET Operator is used to merge the...

5 minutes read.

SQL NOT Operator

SQL NOT is a Boolean operator used with the WHERE clause. NOT operator shows the records if the expression is false. When we use the NOT operator, we fetch only...

7 minutes read.

How to Create Temporary Table in SQL?

How to Create Temporary Table in SQL  Introduction to Temporary Tables Temporary table is a table which is used to store temporary data that can be used further in the same client...

3 minutes read.

SQL Count

Structured Query Language Count() Function is used with Structured Query Language SELECT Statement. SQL Count() function returns the number of items that match the specified criteria in the SELECT statement. Count()...

2 minutes read.

Difference between Delete, Drop and Truncate in SQL

What is the Delete command in SQL? In DML (Data Manipulation Language), we use the delete command that allows us to delete the some entries and modify the databases in SQL....

4 minutes read.

Difference between SQL and NoSQL

SQL vs. NoSQL | Difference between SQL and NoSQL Choosing a database is the most fundamental decision that needs to be decided before starting a task. Relational and non-relational databases are...

3 minutes read.

WEB SQL

What is Web SQL? In SQL we can create databases, read the data in the databases, insert the records into the databases, and delete the records from the databases. For storing...

4 minutes read.

SQL Comparision Operator

The Comparison Operator compares different data of the Structured Query Language table and checks whether the data are the same, less than, greater than, less than, or greater than equal....

14 minutes read.

Commit and Rollback in SQL

In Structured Query Language, we have Data Definition Language (DDL) and Data Manipulation Language (DML) commands the same way we have Transaction Control Language (TCL) commands in the Structured Query...

4 minutes read.

How to use the BETWEEN operator in SQL

In this entire SQL article, we will understand and learn about the BETWEEN operator concept and how to use it in SQL. What is the BETWEEN operator in SQL? The Between operator...

4 minutes read.

Trigger in SQL

In this article, we will learn about the concept of trigger in SQL and its implementation with the help of an example. A Trigger in Structured Query Language is a set...

4 minutes read.

How to use COUNT in SQL?

How to use COUNT in SQL Introduction COUNT( ) is an aggregate function in SQL.This function counts the number of records in a table if the condition is not specified.If the condition...

4 minutes read.

SQL GROUP BY CLAUSE

The GROUP BY clause is used to arrange similar records into the groups in the Structured Query Language queries. The records are arranged with the help of functions which is...

4 minutes read.

SQL SELECT AND Operator

This SQL tutorial explains and helps us understand how to use the AND Operator in the SELECT query with examples. The AND Operator is used to fetch the table’s records if...

2 minutes read.

SQL INSERT Table

In this tutorial, we will help you to understand and learn how to add records to the table in SQL with the help of examples. SQL INSERT query is used to...

5 minutes read.

SQL SELECT Database

In this tutorial, we will help you to understand and learn how to select the database in SQL with the help of examples. The database user or the administrator wants to...

3 minutes read.

SQL WHERE Multiple Conditions

In this topic, we will learn how to add multiple conditions using the WHERE clause. First, let's understand the concept of WHERE clause. WHERE clause is used to specify a condition while...

5 minutes read.