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

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.

SQL SELECT LIKE Operator

The SQL SELECT LIKE Operator tutorial helps us understand how to use the LIKE operator in the SELECT query with examples. The SQL SELECT LIKE Operator retrieves the records from the...

4 minutes read.

Pattern Matching in SQL

The LIKE in the Structured Query Language is a Logical Operator. The SQL LIKE is used within the WHERE clause in the SQL. This Like Operator is used with the...

5 minutes read.

How to use LIKE in SQL

In this SQL article, we will learn and understand how to use LIKE to the columns in the SQL tables. What is Like? Like is an operator in the SQL. It is...

7 minutes read.

Types of SQL Commands

The Structured Query Language is used to deal with structured data. The data which are stored in the form of tables are structured data. These SQL commands store records or...

10 minutes read.

SQL INSERT INTO Statement

SQL INSERT INTO statement adds data to the newly created tables or existing tables. We can add single records or multiple records in a table by using this query. There are...

3 minutes read.

SQL TABLE

SQL TABLE Structured Query Language (SQL) is a relational database (RDBMS) where data is stored in the form of tables, that is, in rows and columns. These tables are known as...

3 minutes read.

SQL Data Control Language

Data Control Language decides to whom should (which user) permit access privileges. GRANT and REVOKE are the commands of DCL. GRANT: It gives privileges to user. REVOKE: It takes back privileges from granted...

1 minute read.

How to drop a column in SQL?

How to drop a column in SQL Introduction To delete a column from an already created table, one needs to use the ALTER command along with the DROP COLUMN clause. Syntax: ALTER TABLE tablename...

4 minutes read.

SQL Topological Sorting

It is for Directed Acyclic Graph, which linearly describes the ordering of graphs. It is not possible for all graphs, it is possible for only Directed acyclic graphs. Applications of Topological...

3 minutes read.

Truncate function in SQL

The TRUNCATE is a numeric function in SQL which truncates the number according to the particular decimal points. Syntax of TRUNCATE Function SELECT TRUNCATE(X, D) AS Alias_Name; In the TRUNCATE syntax, X...

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.

SQL UNION ALL Operator

This page has all the information about UNION ALL operator, which is used to join all the values from the SELECT queries. Similar records were not considered in the result in...

3 minutes read.

Where vs Having

Difference Between Where and Having The WHERE and HAVING clauses in SQL are used to filter records stored in tables in the databases. The dissimilarities between the WHERE and HAVING clause...

3 minutes read.

SQL COPY Table

In this tutorial, we will learn how to copy tables in SQL with the help of examples. Using the SELECT INTO statement in the SQL we can copy one table into...

4 minutes read.

SQL Syntax

In this tutorial, we will help you understand about the different SQL Syntax concepts with the help of an example. Every Structured Query Language starts with Keywords like select, insert, update,...

5 minutes read.

SQL WHERE Clause

The WHERE clause is a search filter that returns only true records. The WHERE clause chooses the selected records based on the given conditions. The WHERE clause is used with...

4 minutes read.

SQL SELECT SUM

The SQL Sum() function is an aggregate function in SQL that returns the total values of an expression. The expression may be numerical, or it may be an expression. Syntax: SELECT SUM(columnname)...

3 minutes read.

SQL JOIN

As the name says, joins mean to merge something or to combine something. But in the case of SQL, joins mean to merge or combine two different tables. The Join clause...

12 minutes read.

How to remove duplicates in SQL

Introduction There are some specific rules that needs to be followed while creating the database objects. To improve the performance of a database, a primary key, clustered and non-clustered indexes, and...

9 minutes read.