SQL Logical Operators

What are SQL Logical Operators?

SQL Logical Operators are used to combine multiple conditions in a query WHERE or HAVING clause to refine the data retrieval process. These operators return a Boolean value (TRUE, FALSE, or UNKNOWN) based on the conditions.

Types of Logical Operators in SQL

Operator	Description
AND	Returns TRUE if all conditions are true.
OR	Returns TRUE if any one condition is true.
NOT	Negates a condition, returning the opposite Boolean value (TRUE becomes FALSE and vice versa).
BETWEEN	Checks if a value is within a specified range.
IN	Checks if a value matches any value in a list.
EXISTS	Returns TRUE if a subquery returns one or more rows.
IS NULL	Checks if a value is NULL.

Using SQL Logical Operators

1. AND Operator

The AND operator combines two or more conditions and returns TRUE only if all conditions are true.

Syntax:

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

Example: Retrieve employees from the "IT" department with a salary greater than 5000.

SELECT name, department, salary
FROM employees
WHERE department = 'IT' AND salary > 5000;

2. OR Operator

The OR operator combines two or more conditions and returns TRUE if any one of the conditions is true.

Syntax:

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

Example: Retrieve employees who are either in the "IT" department or earn more than 7000.

SELECT name, department, salary
FROM employees
WHERE department = 'IT' OR salary > 7000;

3. NOT Operator

The NOT operator reverses the result of a condition. If a condition is TRUE, NOT makes it FALSE and vice versa.

Syntax:

SELECT column1, column2
FROM table_name
WHERE NOT condition;

Example: Retrieve employees not in the "Sales" department.

SELECT name, department
FROM employees
WHERE NOT department = 'Sales';

4. Combining AND, OR, and NOT

Logical operators can be combined in a query. Use parentheses to ensure the correct order of evaluation.

Example: Retrieve employees who are either in the "IT" department or earn more than 7000 but exclude employees in the "Sales" department.

SELECT name, department, salary
FROM employees
WHERE (department = 'IT' OR salary > 7000) AND NOT department = 'Sales';

Logical Operators with Examples

1. Using BETWEEN

The BETWEEN operator checks if a value lies between two specified values (inclusive).

Example: Retrieve employees earning a salary between 3000 and 7000.

SELECT name, salary
FROM employees
WHERE salary BETWEEN 3000 AND 7000;

2. Using IN

The IN operator checks if a value matches any value in a specified list.

Example: Retrieve employees from the "IT", "HR", or "Sales" departments.

SELECT name, department
FROM employees
WHERE department IN ('IT', 'HR', 'Sales');

3. Using EXISTS

The EXISTS operator checks if a subquery returns any rows.

Example: Retrieve employees who have placed at least one order.

SELECT name
FROM employees
WHERE EXISTS (
    SELECT 1
    FROM orders
    WHERE employees.id = orders.employee_id
);

4. Using IS NULL

The IS NULL operator checks if a column's value is NULL.

Example: Retrieve employees without a manager assigned.

SELECT name
FROM employees
WHERE manager_id IS NULL;

Order of Precedence for Logical Operators

When multiple logical operators are used, SQL evaluates them in the following order:

1. NOT
2. AND
3. OR

To override the default precedence, use parentheses.

Example:

SELECT name
FROM employees
WHERE NOT (department = 'IT' OR salary > 7000) AND manager_id IS NOT NULL;

Best Practices

1. Use Parentheses: Always use parentheses to group conditions and ensure clarity.
2. Optimize for Readability: Break long conditions into multiple lines for better readability.
3. Avoid Overusing NOT: Replace NOT with equivalent positive conditions where possible for better readability.
   • Instead of NOT salary > 5000: Use salary <= 5000.

Conclusion

Logical operators in SQL are essential for building complex query conditions. By combining conditions with AND, OR, and NOT, you can filter and retrieve data more effectively. Familiarize yourself with these operators to write powerful SQL queries.