SQL AND, OR, and NOT Operators

---

SQL queries allow you to filter data based on specific conditions, but sometimes you need to combine multiple conditions to get the exact data you're looking for. This is where logical operators like AND, OR, and NOT come into play. These operators allow you to build more complex and powerful queries by combining conditions in various ways.

---

What Are SQL Logical Operators?

Logical operators in SQL are used in the WHERE clause to combine multiple conditions. By using these operators, you can control how different conditions work together to filter records.

• AND: Returns records if all specified conditions are true.
• OR: Returns records if at least one of the specified conditions is true.
• NOT: Excludes records that meet the specified condition.

These operators are commonly used when you want to refine your queries and apply more than one condition to your data retrieval process.

---

Using the AND Operator

The AND operator is used when you want to ensure that multiple conditions are true. All conditions must be satisfied for a record to be included in the result.

Syntax:

SELECT column1, column2
FROM table_name
WHERE condition1 AND condition2;

Example:

Let’s say you have a table called employees with the following columns: id, first_name, last_name, salary, and city. You want to find employees who are from "New York" and have a salary greater than 60,000.

SELECT first_name, last_name, salary, city
FROM employees
WHERE city = 'New York' AND salary > 60000;

Result:

first_name	last_name	salary	city
Jane	Smith	65000	New York
Alice	Johnson	70000	New York

In this query, both conditions must be true for a record to be returned: the employee must be from "New York" and have a salary greater than 60,000.

---

Using the OR Operator

The OR operator is used when you want to return records where at least one of the conditions is true. If either condition is satisfied, the record will be included in the result.

Syntax:

SELECT column1, column2
FROM table_name
WHERE condition1 OR condition2;

Example:

Let’s retrieve employees who either live in "New York" or have a salary greater than 60,000:

SELECT first_name, last_name, salary, city
FROM employees
WHERE city = 'New York' OR salary > 60000;

Result:

first_name	last_name	salary	city
John	Doe	50000	Boston
Jane	Smith	65000	New York
Alice	Johnson	70000	New York

Here, the query returns records where either the city is "New York" or the salary is greater than 60,000. If either condition is met, the record is included in the result.

---

Using the NOT Operator

The NOT operator is used to exclude records that meet a specified condition. It is a negation operator, meaning it will return results that do not meet the condition.

Syntax:

SELECT column1, column2
FROM table_name
WHERE NOT condition;

Example:

Let’s retrieve employees who do not live in "New York":

SELECT first_name, last_name, salary, city
FROM employees
WHERE NOT city = 'New York';

Result:

first_name	last_name	salary	city
John	Doe	50000	Boston
Mark	Lee	45000	Chicago

This query returns all employees who are not from "New York". The NOT operator negates the condition city = 'New York', so only employees whose city is not "New York" are included.

---

Combining AND, OR, and NOT

You can combine multiple logical operators in a single query to create more complex conditions. Parentheses () can be used to group conditions and control the order in which they are evaluated, much like in regular arithmetic.

Example:

Let’s say you want to find employees who are either from "New York" or have a salary greater than 60,000, but exclude those with a salary under 40,000.

SELECT first_name, last_name, salary, city
FROM employees
WHERE (city = 'New York' OR salary > 60000)
AND NOT salary < 40000;

Result:

first_name	last_name	salary	city
Jane	Smith	65000	New York
Alice	Johnson	70000	New York

In this query:

• Employees who are from "New York" or have a salary greater than 60,000 are included.
• However, the NOT salary < 40000 condition excludes any employees earning less than 40,000.

The parentheses ensure that the OR condition is evaluated first, followed by the AND operator.

---

---