> ## Documentation Index
> Fetch the complete documentation index at: https://rajanand.org/llms.txt
> Use this file to discover all available pages before exploring further.

# 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

```sql theme={"system"}
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:

```sql theme={"system"}
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         |

```sql theme={"system"}
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:

```sql theme={"system"}
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:

```sql theme={"system"}
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:

```sql theme={"system"}
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":

```sql theme={"system"}
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:

```sql theme={"system"}
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.