SQL - WHERE Clause

In SQL, the WHERE clause is used to filter rows based on specific conditions. Whether you are retrieving, updating, or deleting data, WHERE ensures that only relevant records are affected. Without it, your query applies to every row in the table! The WHERE clause helps you:

• Filter rows that meet certain conditions
• Target specific data using logical, comparison and pattern-based operators
• Control SELECT, UPDATE, DELETE or even INSERT statements

Syntax:

SELECT column1, column2
FROM table_name
WHERE column_name operator value;

Parameters:

• column1, column2: Columns you want to retrieve
• table_name: Table you are querying from
• operator: Comparison logic (e.g., =, <, >, LIKE)
• value: The value or pattern to filter against

Importance of WHERE Clause

The WHERE clause is critical for several reasons:

• Data Accuracy: Filters data to return only relevant rows
• Performance: Reduces the amount of scanned data
• Flexibility: Works with many operators and conditions

Examples of WHERE Clause in SQL

We will create a basic employee table structure in SQL for performing all the where clause operation.

Query:

CREATE TABLE Emp1 (
EmpID INT PRIMARY KEY,
Name VARCHAR(50),
Country VARCHAR(50),
Age INT,
Mob VARCHAR(15)
);

INSERT INTO Emp1 VALUES
(1, 'Shubham', 'India', 23, '738479734'),
(2, 'Aman', 'Australia', 21, '436789555'),
(3, 'Naveen', 'Sri Lanka', 24, '34873847'),
(4, 'Aditya', 'Austria', 21, '328440934'),
(5, 'Nishant', 'Spain', 22, '73248679');

SELECT * FROM Emp1;

Output:

Example 1: Where Clause with Logical Operators

To fetch records of  Employee with age equal to 24. 

Query:

SELECT * FROM Emp1 WHERE Age=24;

Output:

Example 2: WHERE with Comparison Operators

To fetch the EmpID, Name and Country of Employees with Age greater than 21. 

Query:

SELECT EmpID, Name, Country FROM Emp1 WHERE Age > 21;

Output:

Example 3: Where Clause with BETWEEN Operator

The BETWEEN operator is used to filter records within a specified range, and it includes both the start and end values. In this example, we want to find employees whose age is between 22 and 24, including both 22 and 24.

Query:

SELECT * FROM Emp1
WHERE Age BETWEEN 22 AND 24;

Output:

Example 4: Where Clause with LIKE Operator

It is used to fetch filtered data by searching for a particular pattern in the where clause. In this example we want to find records of Employees where Name starts with the letter. The '%'(wildcard) signifies the later characters here which can be of any length and value. 

Query:

SELECT * FROM Emp1 WHERE Name LIKE 'S%';

Output:

To fetch records of Employees where Name contains the pattern 'M'.

Query:

SELECT * FROM Emp1 WHERE Name LIKE '%M%';

Output:

Example 5: Where Clause with IN Operator

It is used to fetch the filtered data same as fetched by '=' operator just the difference is that here we can specify multiple values for which we can get the result set. Here we want to find the Names of Employees where Age is 21 or 23.

Query:

SELECT Name FROM Emp1 WHERE Age IN (21,23);

Output:

List of Operators that Can be Used with WHERE Clause