WHERE Clause in SQL

SQL (Structured Query Language) is used to work with data stored in relational databases. The WHERE clause in SQL helps you filter records based on specific conditions. It allows you to retrieve only the rows that match the criteria you set. This makes your queries more accurate and useful. In this article, you will learn what the WHERE clause is, how it works, and how to use it effectively when writing SQL queries.

Table of Contents:

• What is the WHERE Clause in SQL?
• Different Types of Operators in the SQL WHERE Clause
• Comparison Operators in SQL
  • Logical Operators in SQL
  • BETWEEN Operator in SQL
  • IN Operator in SQL
  • LIKE Operator In SQL
  • IS NULL / IS NOT NULL in SQL
• Advanced Methods to Use the WHERE Clause in SQL
  • Using Nested Conditions in the WHERE Clause in SQL
  • Using the WHERE Clause with the UPDATE Statement in SQL
  • Using the WHERE Clause with the DELETE Statement in SQL
• Best Practices for Using the WHERE Clause in SQL
• Limitations of the WHERE Clause in SQL
• Conclusion

What is the WHERE Clause in SQL?

The WHERE clause in SQL is used in queries to retrieve records based on conditions. This WHERE clause can be used together with SQL statements like SELECT, UPDATE, and DELETE. You can use these statements along with the WHERE clause for the rows you want to filter the records. 

Syntax:

SELECT column1, column2, ...
FROM table_name
WHERE condition;

Fetching Records Based on Condition Using WHERE Clause in SQL

Let’s create a Planets table and insert values to perform the WHERE clause.

CREATE TABLE Planets (
    PlanetID INT PRIMARY KEY,
    Name VARCHAR(20),
    Type VARCHAR(20), 
    DistanceFromSun FLOAT
);
INSERT INTO Planets (PlanetID, Name, Type, DistanceFromSun) VALUES
(1, 'Planet P', 'Land', 5721.92),
(2, 'Planet A', 'Water', 1468.2),
(3, 'Planet M', 'Water', 494.69),
(4, 'Planet O', 'Land', 4759.3);
SELECT * FROM Planets;

Output:

This is what the table looks like after creation. 

1. Master SQL: Empower Your Data Skills Today!
   Unlock the power of databases with hands-on SQL training and become a data-driven professional.
   Explore Program
   

1. Selecting Only Specific Records from the Table in SQL

To fetch only the specific records, specify the variable or row from the table. 

Example:

SELECT * 
FROM Planets
WHERE Type = 'Land';

Output:

Explanation: Here, the WHERE clause fetched the records by applying the condition Type = ‘Land.’ This fetched the records of Planets where their type is land. 

2. Retrieving Records Based on Numeric Value in SQL 

To retrieve the records by specifying a numeric condition from a table.  

Example:

SELECT * 
FROM Planets
WHERE DistanceFromSun > 1000;

Output:

Explanation: Here, the WHERE clause fetched the records of the planets that have a distance of more than 1000 from the sun using this condition: WHERE DistanceFromSun > 1000.

3. Using Multiple Conditions to Fetch the Records in SQL

WHERE clause in SQL with multiple conditions can be used to fetch the records by applying multiple conditions in a same row. 

Example:

SELECT * 
FROM Planets
WHERE Type = 'Water' AND DistanceFromSun > 1000;

Output:

Explanation: Here, the WHERE clause uses two conditions: one is fetching the type of the planet, and the other is fetching the planet that is more than 1000 distance from the sun. 

Different Types of Operators in the SQL WHERE Clause

The WHERE clause operators have many operators to perform various operations.

Let’s create a People table to perform these operations. 

CREATE TABLE People (
    ID INT PRIMARY KEY,
    Name VARCHAR(50) NOT NULL,
    Age INT
);
INSERT INTO People (ID, Name, Age) VALUES (1, 'Amit', 25);
INSERT INTO People (ID, Name, Age) VALUES (2, 'Sita', 30);
INSERT INTO People (ID, Name, Age) VALUES (3, 'Rahul', 18);
INSERT INTO People (ID, Name, Age) VALUES (4, 'Priya', 16);
INSERT INTO People (ID, Name, Age) VALUES (5, 'Anil', 40);
SELECT * FROM People;

This is what the table looks like after creation. 

Comparison Operators in SQL 

The Comparison Operator has many operators like equal to, not equal to, greater than, less than, greater than or equal to, and less than or equal to. Let’s learn more about these operators.

1. Equal To (=)

The Equal To operator is used to check whether the two values are equal. 

Example:

SELECT * FROM People WHERE Age = 18;

Output:

Explanation: Here, the EQUAL TO (=) operator fetched the records where the age is 18. 

2. Not Equal to (!= or <>)

The Not Equal to (!= or <>) operator is used to check if the two values are not equal. 

Example: 

SELECT * FROM People WHERE Age != 25;

Output:

Explanation: Here, the Not Equal to (!= or <>) operator fetched the values that are not equal to the age 25. 

3. Greater Than (>)

The Greater Than (>) in the comparison operator is used to check if the left-side values are greater than the right-side values. 

Example:

SELECT * FROM People WHERE Age > 30;

Output:

Explanation: Here, the Greater Than (>) operator evaluated all the values in the table and filtered the people who are greater than the age of 18. 

4. Less Than (<)

The Less Than (<) in the comparison operator will check if the values on the left are less than the values on the right. 

Example:

SELECT * FROM People WHERE Age < 20;

Output:

Explanation: Here, the values are Less Than the given value, 20. 

5. Greater Than or Equal To (>=)

The Greater Than or Equal To (>=) operator will evaluate whether the given value is greater than or equal to the conditioned value. 

Example:

SELECT * FROM People WHERE Age >= 40;

Output:

Explanation: Here, the Greater Than or Equal To operator evaluated the ages in the table and found the age of the person who is greater than or equal to 40. 

6. Less Than or Equal To (<=) 

The Less Than or Equal To (<=)operator is used to check whether the value in the table is less than or equal to the given value.

Example:

SELECT * FROM People WHERE Age <= 25;

Output:

Explanation: Here, the Less Than or Equal To (<=)  operator verified and fetched the details of the people whose age is less than or equal to 25. 

Logical Operators in SQL

The logical operator in SQL has three types of conditions: AND, OR, and NOT. 

AND Operator

The AND operator will check all the conditions in the table, and it will give output as true only if all the values are true. 

Example:

SELECT * FROM People
WHERE Age > 18 AND Age < 35;

Output:

Explanation: Here, the AND operator fetched the record of people whose age is greater than 18 and less than 35. 

OR Operator

The OR operator will verify all the values, and if even one value is true, the query will return true.

Example:

SELECT * FROM People
WHERE Age < 18 OR Age > 35;

Output:

Explanation: Here, the OR operator fetched the records of people whose age is less than 18 and greater than 35. 

NOT Operator

The NOT operator will negate the condition. Which means that if the value is true, then the NOT will return false.  

Example:

SELECT * FROM People
WHERE NOT Age < 20;

Output:

Explanation: Here, the NOT operator fetched the records of people whose age is not less than 20. 

BETWEEN Operator in SQL

Using WHERE clause with between in SQL will be used to fetch the values by specifying the range of that value.
 

Example:

CREATE TABLE People (
    ID INT PRIMARY KEY,
    Name VARCHAR(50) NOT NULL,
    Age INT
);
INSERT INTO People (ID, Name, Age) VALUES (1, 'Amit', 25);
INSERT INTO People (ID, Name, Age) VALUES (2, 'Sita', 30);
INSERT INTO People (ID, Name, Age) VALUES (3, 'Rahul', 18);
INSERT INTO People (ID, Name, Age) VALUES (4, 'Priya', 16);
INSERT INTO People (ID, Name, Age) VALUES (5, 'Anil', 40);
SELECT * FROM People
WHERE Age BETWEEN 10 AND 30;

Output: 

Explanation: Here, the between operator fetched the details of the people whose age is between 10 and 30. 

IN Operator in SQL

The IN Operator in SQL will be used to filter the records that match any value in a table. 

Example: 

CREATE TABLE People (
    ID INT PRIMARY KEY,
    Name VARCHAR(50) NOT NULL,
    Age INT
);
INSERT INTO People (ID, Name, Age) VALUES (1, 'Amit', 25);
INSERT INTO People (ID, Name, Age) VALUES (2, 'Sita', 30);
INSERT INTO People (ID, Name, Age) VALUES (3, 'Rahul', 18);
INSERT INTO People (ID, Name, Age) VALUES (4, 'Priya', 16);
INSERT INTO People (ID, Name, Age) VALUES (5, 'Anil', 40);
SELECT * FROM People
WHERE ID IN (1, 3, 5);

Output: 

Explanation: Here, the IN Operator filtered the records in the table and fetched the records of people whose IDs are 1, 3, and 5. 

LIKE Operator In SQL

The LIKE operator in SQL will find patterns in a table while searching for a value. There are two patterns in the LIKE Operator: % and _. The % symbol is used when there are 0 or more characters, and _ is used to mention a single character in a query. 

Example 1:

CREATE TABLE People (
    ID INT PRIMARY KEY,
    Name VARCHAR(50) NOT NULL,
    Age INT
);
INSERT INTO People (ID, Name, Age) VALUES (1, 'Amit', 25);
INSERT INTO People (ID, Name, Age) VALUES (2, 'Sita', 30);
INSERT INTO People (ID, Name, Age) VALUES (3, 'Rahul', 18);
INSERT INTO People (ID, Name, Age) VALUES (4, 'Priya', 16);
INSERT INTO People (ID, Name, Age) VALUES (5, 'Anil', 40);
SELECT * FROM People
WHERE Name LIKE 'A%';

Output: 

Explanation: Here, the LIKE operator with “%” fetched the records that start with A. 

Example 2:

CREATE TABLE People (
    ID INT PRIMARY KEY,
    Name VARCHAR(50) NOT NULL,
    Age INT
);
INSERT INTO People (ID, Name, Age) VALUES (1, 'Amit', 25);
INSERT INTO People (ID, Name, Age) VALUES (2, 'Sita', 30);
INSERT INTO People (ID, Name, Age) VALUES (3, 'Rahul', 18);
INSERT INTO People (ID, Name, Age) VALUES (4, 'Priya', 16);
INSERT INTO People (ID, Name, Age) VALUES (5, 'Anil', 40);
SELECT * FROM People
WHERE Name LIKE '%l';

Output: 

Explanation: Here, the LIKE operator with “%” fetched the records that end with l. 

IS NULL / IS NOT NULL in SQL

The IS NULL or WHERE IS NOT NULL in SQL will check if there are any NULL values in the table with the help of WHERE clause. 

Example:

CREATE TABLE People (
    ID INT PRIMARY KEY,
    Name VARCHAR(50) NOT NULL,
    Age INT
);
INSERT INTO People (ID, Name, Age) VALUES (1, 'Amit', 25);
INSERT INTO People (ID, Name, Age) VALUES (2, 'Sita', 30);
INSERT INTO People (ID, Name, Age) VALUES (3, 'Rahul', 18);
INSERT INTO People (ID, Name, Age) VALUES (4, 'Priya', 16);
INSERT INTO People (ID, Name, Age) VALUES (5, 'Anil', NULL);
SELECT * FROM People
WHERE Age IS NULL;

Output:

Explanation: Here, the IS NULL/IS NOT NULL fetched the record that has NULL values in the table. 

Advanced Methods to Use the WHERE Clause in SQL

There are some methods in which we can use the WHERE clause in queries to get the results efficiently. 

Let’s create a dataset to perform the operations.

CREATE TABLE Festivals (
    FestivalID INT PRIMARY KEY,
    FestivalName VARCHAR(20) NOT NULL,
    State VARCHAR(20),
    Month VARCHAR(20)
);
INSERT INTO Festivals (FestivalID, FestivalName, State, Month) 
VALUES (1, 'Diwali', 'All States', 'November');
INSERT INTO Festivals (FestivalID, FestivalName, State, Month) 
VALUES (2, 'Pongal', 'Tamil Nadu', 'January');
INSERT INTO Festivals (FestivalID, FestivalName, State, Month) 
VALUES (3, 'Bihu', 'Assam', 'April');
INSERT INTO Festivals (FestivalID, FestivalName, State, Month) 
VALUES (4, 'Holi', 'All States', 'March');
INSERT INTO Festivals (FestivalID, FestivalName, State, Month) 
VALUES (5, 'Onam', 'Kerala', 'August');
SELECT * FROM Festivals

This is how the festival table looks after creation. 

Using Nested Conditions in the WHERE Clause in SQL

Using the Nested conditions or Subqueries in the WHERE Clause to filter records based on values from other queries.

Example:

SELECT * FROM Festivals
WHERE Month = (
    SELECT Month FROM Festivals
    WHERE FestivalName = 'Pongal'
);

Output:

Explanation: Here, the query had a nested condition and fetched the record of places that have Pongal as their festival. 

Using the WHERE Clause with the UPDATE Statement in SQL

The UPDATE Statement will update the record in the table. With the WHERE clause, you can specify the row that has to be updated. 

Example:

UPDATE Festivals
SET State = 'Seasonal'
WHERE Month = 'March';

Output:

Explanation: Here, the festival in the month of March has been updated to Seasonal instead of the festival month with the help of an update statement. 

Using the WHERE Clause with the DELETE Statement in SQL

The DELETE statement in SQL will help to delete any row in a table. The WHERE Clause will help to identify the record, and the DELETE statement will delete that record.

Example:

DELETE FROM Festivals
WHERE FestivalName = 'Bihu';
SELECT * FROM Festivals

Output:

Explanation: Here, the WHERE clause fetched the festival name Bihu, and the DELETE statement deleted that particular record from the table. 

Best Practices for Using the WHERE Clause in SQL

• To increase the query performance, make sure that all the columns in a table are indexed. 
• Ignore using functions directly on the columns that will collapse the indexes of the record. 
• When dealing with NULL values, use IS NULL or IS NOT NULL for better performance since equal to (=), less than (<), or greater than (>) cannot compare as the value is empty.
• When there is a lot of data in a database, using IN will fetch the values efficiently. It is better than the OR condition. 

Limitations of the WHERE Clause in SQL

• String comparisons have case sensitivity, so make sure that while comparing the string, you follow the case sensitivity. 
• While using the LIKE operator (% and _), it has to be properly placed; if it is misplaced, it will cause an unexpected error. 
• The WHERE clause will not allow you to return NULL values unless it is used with IS NOT NULL or IS NULL. 
• If you want to use functions like UPPER() or DATE() in a column, that will reduce the performance as it will prevent the use of indexes. 

Become a Data Pro – Free SQL Course Inside!

Learn how to write powerful queries and manage databases with our beginner-friendly free SQL course.

Explore Program

Conclusion

The WHERE clause is important in SQL, which helps the user to fetch, update, and delete the records based on a given condition. The WHERE clause plays a major role in filtering the data to get the output precisely and making it efficient. The WHERE clause can be used to check equality and comparison, and also to help apply logical conditions, or can be used to match patterns with the LIKE operator. In this article, you have learned that the WHERE clause will improve your database efficiency, which will make you analyze and manage the data easily in the relational database. 

Take your skills to the next level by enrolling in the SQL Training Course today and gaining hands-on experience. Also, prepare for job interviews with SQL interview questions prepared by industry experts.