SQL AND,OR,NOT

When working with databases, it’s common to filter and retrieve only the data you need. SQL (Structured Query Language) allows you to do this using logical operators. Three important logical operators are OR, NOT, and AND. They help you build complex conditions in the WHERE clause of your queries.

In this guide, we’ll explain each operator, how they work, and give examples so you can understand them better.

---

1. SQL AND Operator

The AND operator is used to combine two or more conditions in a query.
A row will be returned only if all the conditions are true.

Syntax:

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

Example:

Suppose we have a table called Employees:

ID	Name	Department	Salary
1	Riya	HR	45000
2	Arjun	IT	60000
3	Neha	IT	50000
4	Priya	HR	30000
5	Suresh	Finance	40000

If we want to find employees in the IT department who have a salary greater than 50,000, we use:

SELECT * 
FROM Employees
WHERE Department = 'IT' AND Salary > 50000;

Result:

ID	Name	Department	Salary
2	Arjun	IT	60000

Key Point:
The AND operator returns a row only if both conditions are true.

---

2. SQL OR Operator

The OR operator is used when you want to retrieve rows that meet at least one of several conditions.

Syntax:

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

Example:

From the same Employees table, if we want to find employees in the HR department or those with a salary above 55,000:

SELECT * 
FROM Employees
WHERE Department = 'HR' OR Salary > 55000;

Result:

ID	Name	Department	Salary
1	Riya	HR	45000
2	Arjun	IT	60000
4	Priya	HR	30000

Key Point:
The OR operator returns a row if at least one condition is true.

---

3. SQL NOT Operator

The NOT operator is used to exclude rows that meet a certain condition.

Syntax:

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

Example:

If we want all employees except those in the HR department:

SELECT * 
FROM Employees
WHERE NOT Department = 'HR';

Result:

ID	Name	Department	Salary
2	Arjun	IT	60000
3	Neha	IT	50000
5	Suresh	Finance	40000

Key Point:
NOT is used to filter out unwanted results.

---

4. Combining AND, OR, and NOT

You can combine these operators to make more advanced queries.
Important: Use parentheses () to control the order of evaluation.

Example:

Find employees who are in the IT department and have a salary above 50,000 or are in HR but not earning less than 40,000.

SELECT *
FROM Employees
WHERE (Department = 'IT' AND Salary > 50000)
   OR (Department = 'HR' AND NOT Salary < 40000);

Result:

ID	Name	Department	Salary
1	Riya	HR	45000
2	Arjun	IT	60000

---

5. Order of Evaluation (Precedence)

When SQL evaluates multiple conditions, it follows this order:

1. NOT
2. AND
3. OR

If you mix them without parentheses, SQL might not give the result you expect. Always use parentheses for clarity.

Example without parentheses:

WHERE Department = 'IT' OR Department = 'HR' AND Salary > 40000

Here, AND is evaluated first, so it’s equivalent to:

WHERE Department = 'IT' OR (Department = 'HR' AND Salary > 40000)

If you want both IT and HR employees with salary above 40,000, you must write:

WHERE (Department = 'IT' OR Department = 'HR') AND Salary > 40000

---

6. Practical Use Cases

• AND: Filtering for very specific results, like customers from a certain city and who spent more than $500.
• OR: Finding records that match one of several criteria, like students from Delhi or Mumbai.
• NOT: Excluding results, like products not in stock.

---

Summary Table

Operator	Meaning	Returns True When…
AND	Both conditions must be true	Both are true
OR	At least one condition is true	One or both are true
NOT	Condition is false	The condition is not true

---

Final Tip:
Logical operators are powerful for filtering data. Always use parentheses to avoid confusion, especially when mixing AND, OR, and NOT.