​
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

EmployeeIDNameCityStateSalary
1AnandChennaiTamil Nadu50000
2BalaCoimbatoreTamil Nadu60000
3KavithaKaraikalPuducherry55000
4RajMaduraiTamil Nadu70000
5KumarTrichyTamil Nadu45000

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

SELECT Name, City, Salary
FROM Employees
WHERE Salary > 50000;
NameCitySalary
BalaCoimbatore60000
KavithaKaraikal55000
RajMadurai70000

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

StudentIDNameCityStatePercentage
1RamChennaiTamil Nadu92
2KarthikCoimbatoreTamil Nadu88
3DavidBangaloreKarnataka95
4KannanKaraikalPuducherry91
5SivaMaduraiTamil Nadu89
SELECT Name, City, Percentage
FROM Students
WHERE State = 'Tamil Nadu' AND Percentage > 90;
NameCityPercentage
RamChennai92

​
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;
NameCitySalary
BalaCoimbatore60000
RajMadurai70000

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;
NameCitySalary
AnandChennai50000
RajMadurai70000

​
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;
NameCitySalary
AnandChennai50000
BalaCoimbatore60000
KavithaKaraikal55000

​
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%';
NameCity
KavithaKaraikal
KumarTrichy

​
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');
NameCity
AnandChennai
RajMadurai

​
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.