MySQL AND, OR and NOT Operators

The AND, OR, and NOT operators are used with the WHERE clause in SQL to combine and refine conditions when filtering records. These operators provide flexibility for constructing more complex queries.

Key Features of Each Operator

AND Operator
- Filters records where all conditions are true.
- Displays results only if every condition is met.
Syntax:
```
SELECT column1, column2, ...
FROM table_name
WHERE condition1 AND condition2;
```
Example: Retrieve customers from the "Customers" table who are in Germany and whose city is Berlin:
```
SELECT CustomerName, City, Country
FROM Customers
WHERE Country = 'Germany' AND City = 'Berlin';
```

OR Operator

Filters records where at least one condition is true.
Displays results if any condition is met.

Syntax:

SELECT column1, column2, ...
FROM table_name
WHERE condition1 OR condition2;

Example: Retrieve customers who are located in either Germany or Mexico:

SELECT CustomerName, City, Country
FROM Customers
WHERE Country = 'Germany' OR Country = 'Mexico';

NOT Operator
- Filters records where a condition is not true.
- Excludes results matching the specified condition.
Syntax:
```
SELECT column1, column2, ...
FROM table_name
WHERE NOT condition;
```
Example: Retrieve customers who are not located in Germany:
```
SELECT CustomerName, City, Country
FROM Customers
WHERE NOT Country = 'Germany';
```

Combining AND, OR, and NOT Operators

You can combine these operators to create more complex conditions. Parentheses () are used to define the precedence and group conditions.

Syntax for Combining Operators

SELECT column1, column2, ...
FROM table_name
WHERE (condition1 AND condition2) OR NOT condition3;

Example

Retrieve customers where:

The country is "Germany" and the city is either "Berlin" or "Stuttgart":

SELECT CustomerName, City, Country
FROM Customers
WHERE Country = 'Germany' AND (City = 'Berlin' OR City = 'Stuttgart');

Result:

CustomerName	City	Country
Alfreds Futterkiste	Berlin	Germany
(Example Customer)	Stuttgart	Germany

Logical Operator Precedence

NOT is evaluated first.
AND is evaluated before OR.
Parentheses override the default precedence.

For example, the query:

WHERE NOT Country = 'Germany' AND (City = 'Berlin' OR City = 'Stuttgart')

First, the NOT condition is evaluated.
Then, the AND condition is processed.
Finally, the OR condition is resolved.

Practical Tips

Use Parentheses for Clarity: Parentheses help in structuring complex queries and avoiding logical errors.
Test Conditions Separately: When combining conditions, test each condition individually to ensure correctness.
Index Key Columns: Using indexes on columns frequently queried in WHERE conditions can improve performance.

By effectively combining AND, OR, and NOT, you can perform advanced filtering and retrieve precise data from your MySQL database.