SQL WHERE Clause

The SQL WHERE clause allows to filtering of records in queries. Whether you’re retrieving data, updating records, or deleting entries from a database, the WHERE clause plays an important role in defining which rows will be affected by the query. Without it, SQL queries would return all rows in a table, making it difficult to target specific data.

In this article, we will learn the WHERE clause in detail—from basic concepts to advanced ones. We’ll cover practical examples, discuss common operators, provide optimization tips, and address real-world use cases.

What is the SQL WHERE Clause ?

The SQL WHERE clause is used to specify a condition while fetching or modifying data in a database. It filters the rows that are affected by the SELECT, UPDATE, DELETE, or INSERT operations. The condition can range from simple comparisons to complex expressions, enabling precise targeting of the data.

Syntax

SELECT column1,column2 FROM table_name WHERE column_name operator value;

Parameter explanation

1. column1,column2: fields in the table

2. table_name: name of table

3. column_name: name of field used for filtering the data

4. operator: operation to be considered for filtering

5. value: exact value or pattern to get related data in the result

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(2),
  mob int(10)
);
-- Insert some sample data into the Customers table
INSERT INTO Emp1 (EmpID, Name,Country, Age, mob)
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');

Output

EmpID	Name	Country	Age	mob
1	Shubham	India	23	738479734
2	Aman	Australia	21	436789555
3	Naveen	Sri lanka	24	34873847
4	Aditya	Australia	21	328440934
5	Nishant	Spain	22	73248679

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

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 2: Where Clause with BETWEEN Operator

It is used to fetch filtered data in a given range inclusive of two values.

Syntax

SELECT column1,column2 FROM table_name 
WHERE column_name BETWEEN value1 AND value2;

Parameter Explanation

1. BETWEEN: operator name

2. value1 AND value2: exact value from value1 to value2 to get related data in result set.

To fetch records of Employees where Age is between 22 and 24 (inclusive).

Query

SELECT * FROM Emp1 WHERE Age BETWEEN 22 AND 24;

Output

Example 3: Where Clause with LIKE Operator

It is used to fetch filtered data by searching for a particular pattern in the where clause.

Syntax

SELECT column1,column2 FROM
table_name WHERE column_name LIKE pattern;

Parameters Explanation

1. LIKE: operator name

2. pattern: exact value extracted from the pattern to get related data in the result set.

Note

The character(s) in the pattern is case-insensitive.

To fetch records of Employees where Name starts with the letter S.

Query

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

The '%'(wildcard) signifies the later characters here which can be of any length and value.

Output

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

Query

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

Output

Example 4: 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.

Syntax

SELECT column1,column2 FROM table_name WHERE column_name IN (value1,value2,..);

Parameters Explanation

1. IN: operator name

2. value1,value2,..: exact value matching the values given and get related data in the result set.

To fetch 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

Operator	Description
>	Greater Than
>=	Greater than or Equal to
<	Less than
<=	Less than or Equal to
=	Equal to
<>	Not Equal to
BETWEEN	In an inclusive Range
LIKE	Search for a pattern
IN	To specify multiple possible values for a column

Conclusion

The WHERE clause is use for filtering and refining SQL queries. Whether you’re working with basic conditions, using logical operators, or performing advanced queries with subqueries and EXISTS, mastering the WHERE clause is essential for every SQL user. Understanding how to efficiently filter data, avoid common pitfalls, and optimize your queries will ensure you’re able to write clean, fast, and accurate SQL queries.