WHERE Clause in SQL

What is the WHERE Clause?

The WHERE clause in SQL is used to filter records based on specified conditions. It allows you to retrieve only those rows that meet the criteria defined in the WHERE clause. This is essential for extracting specific data from a table or multiple tables.

Key Points

1. Filtering Data: The WHERE clause is used to filter rows based on one or more conditions.
2. Condition Types: Conditions can include comparisons (e.g., =, >, <), logical operators (e.g., AND, OR, NOT), and pattern matching (e.g., LIKE).
3. Performance: Proper use of the WHERE clause can improve query performance by reducing the number of rows processed.
4. The WHERE clause can be used with SELECT, UPDATE, DELETE, and other SQL statements.

Syntax

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

• column1, column2, ...: The columns you want to retrieve.
• table_name: The table from which to retrieve data.
• condition: The criteria used to filter rows.

Examples

Suppose you have a table named Employees that stores employee details.

Table: Employees

EmployeeID	Name	City	State	Salary
1	Anand	Chennai	Tamil Nadu	50000
2	Bala	Coimbatore	Tamil Nadu	60000
3	Kavitha	Karaikal	Puducherry	55000
4	Raj	Madurai	Tamil Nadu	70000
5	Kumar	Trichy	Tamil Nadu	45000

To retrieve employees who earn more than ₹50,000:

SELECT Name, City, Salary
FROM Employees
WHERE Salary > 50000;

Name	City	Salary
Bala	Coimbatore	60000
Kavitha	Karaikal	55000
Raj	Madurai	70000

Suppose you have a table named Students that stores student details, and you want to retrieve students from Tamil Nadu who scored more than 90%.

Table: Students

StudentID	Name	City	State	Percentage
1	Ram	Chennai	Tamil Nadu	92
2	Karthik	Coimbatore	Tamil Nadu	88
3	David	Bangalore	Karnataka	95
4	Kannan	Karaikal	Puducherry	91
5	Siva	Madurai	Tamil Nadu	89

SELECT Name, City, Percentage
FROM Students
WHERE State = 'Tamil Nadu' AND Percentage > 90;

Name	City	Percentage
Ram	Chennai	92

Using Logical Operators in the WHERE Clause

You can combine multiple conditions using logical operators like AND, OR, and NOT.

To retrieve employees from Tamil Nadu who earn more than ₹50,000:

SELECT Name, City, Salary
FROM Employees
WHERE State = 'Tamil Nadu' AND Salary > 50000;

Name	City	Salary
Bala	Coimbatore	60000
Raj	Madurai	70000

To retrieve employees who are either from Chennai or earn more than ₹60,000:

SELECT Name, City, Salary
FROM Employees
WHERE City = 'Chennai' OR Salary > 60000;

Name	City	Salary
Anand	Chennai	50000
Raj	Madurai	70000

Using Comparison Operators

Comparison operators like =, >, <, >=, <=, and != are commonly used in the WHERE clause.

To retrieve employees who earn between ₹50,000 and ₹60,000:

SELECT Name, City, Salary
FROM Employees
WHERE Salary >= 50000 AND Salary <= 60000;

Name	City	Salary
Anand	Chennai	50000
Bala	Coimbatore	60000
Kavitha	Karaikal	55000

Using the LIKE Operator for Pattern Matching

The LIKE operator is used to filter rows based on patterns. It is often used with wildcard characters:

• %: Matches zero or more characters.
• _: Matches exactly one character.

To retrieve employees whose names start with the letter “K”:

SELECT Name, City
FROM Employees
WHERE Name LIKE 'K%';

Name	City
Kavitha	Karaikal
Kumar	Trichy

Using the IN Operator

The IN operator allows you to specify multiple values in a WHERE clause.

To retrieve employees from Chennai or Madurai:

SELECT Name, City
FROM Employees
WHERE City IN ('Chennai', 'Madurai');

Name	City
Anand	Chennai
Raj	Madurai

Key Takeaways

1. The WHERE clause is used to filter rows based on specified conditions.
2. It supports comparison operators (=, >, <, etc.), logical operators (AND, OR, NOT), and pattern matching (LIKE).
3. The IN operator allows filtering based on multiple values.
4. Proper use of the WHERE clause improves query performance by reducing the number of rows processed.
5. The WHERE clause is essential for extracting specific data from tables.