SQL Tutorial

SQL Tutorial SQL Introduction SQL Syntax SQL Data Types SQL OPERATORS SQL COMMANDS SQL Queries

SQL Database

SQL Create Database SQL DROP Database SQL SELECT Database

SQL Table

SQL TABLE SQL CREATE TABLE SQL COPY TABLE SQL ALTER TABLE SQL DELETE SQL TRUNCATE TABLE SQL DROP TABLE SQL UPDATE TABLE SQL INSERT TABLE

SQL SELECT

SQL SELECT Statement SQL SELECT WHERE Clause SQL SELECT IN Operator SQL BETWEEN Operator SQL SELECT BETWEEN Operator SQL SELECT AND Operator SQL SELECT OR Operator SQL SELECT LIKE Operator SQL SELECT DISTINCT SQL SELECT SUM SQL SELECT MAX SQL SELECT MIN SQL SELECT AVG

SQL Clause

SQL WHERE Clause SQL GROUP BY CLAUSE SQL ORDER BY Clause SQL HAVING Clause

SQL INSERT

SQL INSERT Statement SQL INSERT INTO Statement SQL INSERT INTO SELECT

SQL JOIN

SQL JOIN SQL Inner Join SQL Left Join SQL Right Join SQL Full Join SQL CROSS Join

SQL OPERATOR

SQL Comparison SQL LOGICAL Operator SQL Cast Operator SQL Arithmetic

Difference

SQL vs NOSQL WHERE vs HAVING DELETE vs DROP GROUP BY vs ORDER BY DROP vs TRUNCATE SQL IN vs SQL EXISTS

MISC

SQL SubQuery SQL CASE Commit and Rollback in SQL Pattern Matching in SQL DDL Commands in SQL DML Commands in SQL Types of SQL Commands SQL COUNT SQL Primary Key SQL FOREIGN KEY SET Operators in SQL Check Constraint in SQL SQL EXCEPT SQL VIEW SQL WHERE Statement SQL CRUD Operation Where Condition in SQL TCL Commands in SQL Types of SQL JOINS SQL Nth Highest Salary SQL NOT OPERATOR SQL UNION ALL SQL INTERSECT SQL Data Definition Language SQL Data Manipulation Language SQL Data Control Language SQL CONSTRAINTS SQL Aggregate Operators SQL KEYS Codd’s Rules in SQL What is SQL Injection? Trigger In SQL SQL WHERE Multiple Conditions Truncate function in SQL SQL Formatter WEB SQL SQL Auto Increment Save Point in SQL space() function in SQL SQL Aggregate Functions SQL Topological Sorting SQL Injection SQL Cloning Tables SQL Aliases SQL Handling Duplicate

How To

How to use the BETWEEN operator in SQL How To Use INNER JOIN In SQL How to use LIKE in SQL How to use HAVING Clause in SQL How to use GROUP BY Clause in SQL How To Remove Duplicates In SQL How To Delete A Row In SQL How to add column in table in SQL ? How to drop a column in SQL? How to create a database in SQL? How to use COUNT in SQL? How to Create Temporary Table in SQL? How to Add Foreign Key in SQL? How to Add Comments in SQL? How To Use Group By Clause In SQL How To Use Having Clause In SQL How To Delete Column In Table How To Compare Date In SQL

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.



ADVERTISEMENT
ADVERTISEMENT