WHERE clause in SQL

Published : 06 July 2024
Updated : 06 July 2024

122 Views

WHERE clause is a powerful tool for filtering and retrieving specific data from database tables. The WHERE clause in SQL allows you to specify conditions that determine which rows from table should be retrieved or affected by a query.

Syntax

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

SELECT Clause is used to retrieve value of specifies columns from the database table.
FROM Clause is used to specifies the tables from which to retrieve data.
WHERE clause is used to apply conditions or filters.
You can use comparison operators (=, !=, <, >, <=, >=) and logical operators (AND, OR, NOT) in WHERE clause.
You can use Wildcards ( % for zero or more characters and _ for a single character) for flexible pattern matching in WHERE clause
You can handle null value in where clause by using IS NULL or IS NOT NULL.
You can use multiple operations in WHERE clause.
Consider indexing columns frequently used in WHERE clauses to enhance query performance, especially in large datasets.
Try to avoid unnecessary complexity in WHERE clauses to maintain query efficiency and readability.

Note :- WHERE clause is not only used in SELECT statements, it is also used in UPDATE, DELETE etc

Let's assume we have 2 tables Users (Id, Name , Mobile ,DOB) and UserAddress (Id, UserId, AddressType, Address, Country, City and PostalCode)

Example : Fetching User by User Id

To fetch user by user id you have to apply = operation check in WHERE clause. And if you have user id list and want to write SQL query to fetch all users then you should use IN operation to fetch user details.

SQL query to fetch user details of user id 5.

SELECT Id, Name, Mobile, DOB 
FROM Users
WHERE Id=5;

Let's assume you have a list of user ids (3,5,7,8,9) and want to write SQL query to fetch all list users in one go. SQL query of such case

SELECT Id, Name, Mobile, DOB 
FROM Users
WHERE Id IN (3,5,7,8,9);

Example : Getting Users which age is between 30 to 50

Here in table we don't have Age column but we have DOB ( date of birth) of user so we can drive Age. after subtracting DOB with current date we will get Age of user . Here i am using TIMESTAMPDIFF(YEAR, DOB, CURDATE()) AS Age to get user Age .

Here TIMESTAMPDIFF function is used to calculate the difference between two timestamps or dates. and it returns the difference in terms of a specified unit (such as YEAR, MONTH, DAY, etc.)

SELECT  Id , Name ,TIMESTAMPDIFF(YEAR, DOB, CURDATE()) AS Age 
FROM Users 
WHERE TIMESTAMPDIFF(YEAR, DOB, CURDATE()) BETWEEN 30 AND 50;

Example : Getting Users which Mobile number start with 98

For such type of requirement we have to use Wildcards ( % for zero or more characters and _ for a single character) for flexible pattern matching in WHERE clause

SQL query to fetch users which mobile number start with 98.

SELECT  Id, Name 
FROM Users 
WHERE Mobile like "98%";

Example : Handling NULL Values

If you want to handle null values in SQL query in efficient way this section is useful for you. WHERE clauses can handle NULL values explicitly using IS NULL or IS NOT NULL conditions to filter rows based on the presence or absence of NULL values in columns.

SQL query to fetch users whose DOB is null or not present.

SELECT  Id , Name 
FROM Users 
WHERE DOB IS NULL;

SQL query to fetch users whose DOB is not null or present in table.

SELECT  Id , Name , DOB
FROM Users 
WHERE DOB IS NOT NULL;

Example : Multiple condition or filter in WHERE clause

Where clause allows you to apply multiple conditions. You can apply as many condition as you want.

SQL query to fetch users which age is between 30 to 50 and a permanent user of India country and name start with ra.

SELECT u.Id, u.Name , u.DOB , ua.AddressType , ua.Country 
FROM Users as u 
JOIN UserAddress as ua on u.Id = ua.UserId
WHERE TIMESTAMPDIFF(YEAR, u.DOB, CURDATE()) BETWEEN 30 AND 50  AND 
    AddressType = "Permanent" AND Country = "india" AND u.Name like "ra%";