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

# Logical Operators in SQL

Logical operators in SQL are used to combine or negate conditions in `WHERE`, `HAVING`, and `JOIN` clauses. They allow you to create complex conditions by combining multiple expressions. The most commonly used logical operators are `AND`, `OR`, and `NOT`.

## Key Points About Logical Operators

1. **Combining Conditions**: Logical operators are used to combine multiple conditions in a query.
2. **Common Operators**: The most commonly used logical operators are `AND`, `OR`, and `NOT`.
3. **Order of Evaluation**: Conditions are evaluated in the following order: `NOT`, `AND`, `OR`. Use parentheses to change the order of evaluation.
4. **AND**: Returns `TRUE` if all conditions are true.
5. **OR**: Returns `TRUE` if at least one condition is true.
6. **NOT**: Negates a condition, returning `TRUE` if the condition is false.
7. **EXISTS**: Used to test for the existence of rows in a subquery.
8. **IN**: Used to compare a value against a list or subquery.
9. **LIKE**: Used for pattern matching with wildcards (`%` and `_`).
10. **BETWEEN**: Used to filter values within a range (inclusive).
11. **Use Cases**: Logical operators are used in `WHERE`, `HAVING`, and `JOIN` clauses to filter and combine data.

## Common Logical Operators

### AND Operator

The `AND` operator returns `TRUE` if all the conditions separated by `AND` are true.

```sql theme={"system"}
SELECT column1, column2, ...
FROM table_name
WHERE condition1 AND condition2;
```

Retrieve employees from Tamil Nadu with a salary greater 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  |

### OR Operator

The `OR` operator returns `TRUE` if at least one of the conditions separated by `OR` is true.

```sql theme={"system"}
SELECT column1, column2, ...
FROM table_name
WHERE condition1 OR condition2;
```

Retrieve employees from Chennai or Madurai.

```sql theme={"system"}
SELECT Name, City, Salary
FROM Employees
WHERE City = 'Chennai' OR City = 'Madurai';
```

| Name  | City    | Salary |
| ----- | ------- | ------ |
| Anand | Chennai | 50000  |
| Raj   | Madurai | 70000  |

### NOT Operator

The `NOT` operator negates a condition, returning `TRUE` if the condition is false.

```sql theme={"system"}
SELECT column1, column2, ...
FROM table_name
WHERE NOT condition;
```

Retrieve employees who are not from Tamil Nadu.

```sql theme={"system"}
SELECT Name, City, Salary
FROM Employees
WHERE NOT State = 'Tamil Nadu';
```

| Name    | City     | Salary |
| ------- | -------- | ------ |
| Kavitha | Karaikal | 55000  |

### EXISTS Operator

The `EXISTS` operator checks if a subquery returns any rows. It returns `TRUE` if the subquery returns at least one row, otherwise `FALSE`.

```sql theme={"system"}
SELECT column1, column2, ...
FROM table_name
WHERE EXISTS (subquery);
```

Check if there are any employees in the HR department.

```sql theme={"system"}
SELECT Name, City
FROM Employees
WHERE EXISTS (
    SELECT 1
    FROM Departments
    WHERE Departments.DepartmentID = Employees.DepartmentID
    AND Departments.DepartmentName = 'HR'
);
```

| Name  | City    |
| ----- | ------- |
| Anand | Chennai |

### IN Operator

The `IN` operator checks if a value matches any value in a list or subquery.

```sql theme={"system"}
SELECT column1, column2, ...
FROM table_name
WHERE column_name IN (value1, value2, ...);
```

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 |

### LIKE Operator

The `LIKE` operator is used for pattern matching with wildcards:

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

```sql theme={"system"}
SELECT column1, column2, ...
FROM table_name
WHERE column_name LIKE pattern;
```

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   |

### BETWEEN Operator

The `BETWEEN` operator checks if a value lies within a specified range (inclusive).

```sql theme={"system"}
SELECT column1, column2, ...
FROM table_name
WHERE column_name BETWEEN value1 AND value2;
```

Retrieve employees with a salary between ₹50,000 and ₹60,000.

```sql theme={"system"}
SELECT Name, Salary
FROM Employees
WHERE Salary BETWEEN 50000 AND 60000;
```

| Name    | Salary |
| ------- | ------ |
| Anand   | 50000  |
| Bala    | 60000  |
| Kavitha | 55000  |

## Combining Logical Operators

You can combine multiple logical operators to create complex conditions. Use parentheses to control the order of evaluation.

Retrieve employees from Tamil Nadu with a salary greater than ₹50,000 or employees from Puducherry.

```sql theme={"system"}
SELECT Name, City, Salary
FROM Employees
WHERE (State = 'Tamil Nadu' AND Salary > 50000) OR State = 'Puducherry';
```

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

Suppose you have a table named `Students` that stores student details.

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         |

Retrieve students from Tamil Nadu with a percentage greater than 90 or students from Karnataka.

```sql theme={"system"}
SELECT Name, City, Percentage
FROM Students
WHERE (State = 'Tamil Nadu' AND Percentage > 90) OR State = 'Karnataka';
```

| Name  | City      | Percentage |
| ----- | --------- | ---------- |
| Ram   | Chennai   | 92         |
| David | Bangalore | 95         |

Check if there are any orders placed by customers from Tamil Nadu.

```sql theme={"system"}
SELECT CustomerName, City
FROM Orders
WHERE EXISTS (
    SELECT 1
    FROM Customers
    WHERE Customers.CustomerID = Orders.CustomerID
    AND Customers.State = 'Tamil Nadu'
);
```

Retrieve students who scored above 90% in Mathematics or Science.

```sql theme={"system"}
SELECT Name, Percentage
FROM Students
WHERE CourseID IN (101, 102) AND Percentage > 90;
```

Retrieve customers whose names contain the substring "an".

```sql theme={"system"}
SELECT CustomerName, City
FROM Customers
WHERE CustomerName LIKE '%an%';
```

Retrieve orders placed between January 1, 2023, and December 31, 2023.

```sql theme={"system"}
SELECT OrderID, OrderDate, Amount
FROM Orders
WHERE OrderDate BETWEEN '2023-01-01' AND '2023-12-31';
```

## Key Takeaways

1. Logical operators (`AND`, `OR`, `NOT`) are used to combine or negate conditions in SQL queries.
2. Use parentheses to control the order of evaluation when combining multiple logical operators.
3. `AND` returns `TRUE` if all conditions are true, `OR` returns `TRUE` if at least one condition is true, and `NOT` negates a condition.
4. **EXISTS**: Checks if a subquery returns any rows.
5. **IN**: Compares a value against a list or subquery.
6. **LIKE**: Performs pattern matching using wildcards (`%` and `_`).
7. **BETWEEN**: Filters values within a specified range (inclusive).
8. These operators are essential for writing flexible and efficient queries in SQL.
9. Logical operators are essential for creating complex conditions in `WHERE`, `HAVING`, and `JOIN` clauses.