AND / OR / NOT

AND: Retrieves rows that satisfy both conditions.

SELECT column FROM table WHERE condition1 AND condition2;

OR: Retrieves rows that satisfy at least one condition.

SELECT column FROM table WHERE condition1 OR condition2;

NOT: Negates a condition.

SELECT column FROM table WHERE NOT condition;

Use parentheses to explicitly set the order of evaluation.

SELECT column FROM table WHERE condition1 AND (condition2 OR condition3);

The following 'employees' table is used in the examples below.

Retrieves employees in the 'Future Gadget Lab' with a salary of 300,000 or more.

sample_and_or_not.sql

SELECT name, department, salary
FROM employees
WHERE department = 'Future Gadget Lab' AND salary >= 300000;

+---------------+-------------------+--------+
| name          | department        | salary |
+---------------+-------------------+--------+
| Okabe Rintaro | Future Gadget Lab | 320000 |
| Makise Kurisu | Future Gadget Lab | 350000 |
+---------------+-------------------+--------+
2 rows in set

Retrieves employees with a salary of 260,000 or 350,000.

sample_and_or_not.sql

SELECT name, salary
FROM employees
WHERE salary = 260000 OR salary = 350000;

+---------------+--------+
| name          | salary |
+---------------+--------+
| Makise Kurisu | 350000 |
| Shiina Mayuri | 260000 |
+---------------+--------+
2 rows in set

Retrieves employees whose salary is not less than 300,000 (i.e., 300,000 or more).

sample_and_or_not.sql

SELECT name, salary
FROM employees
WHERE NOT salary < 300000;

+---------------+--------+
| name          | salary |
+---------------+--------+
| Okabe Rintaro | 320000 |
| Makise Kurisu | 350000 |
+---------------+--------+
2 rows in set

Use parentheses to control evaluation order. Retrieves employees with a salary of 350,000 or more, OR employees in the 'Future Gadget Lab' with a salary of 300,000 or more.

sample_and_or_not.sql

SELECT name, department, salary
FROM employees
WHERE salary >= 350000 OR (department = 'Future Gadget Lab' AND salary >= 300000);

+---------------+-------------------+--------+
| name          | department        | salary |
+---------------+-------------------+--------+
| Okabe Rintaro | Future Gadget Lab | 320000 |
| Makise Kurisu | Future Gadget Lab | 350000 |
+---------------+-------------------+--------+
2 rows in set

The syntax and operator precedence of AND, OR, and NOT are consistent across all major databases.

SELECT name, department, salary
FROM employees
WHERE salary >= 350000 OR (department = 'Future Gadget Lab' AND salary >= 300000);

The precedence of SQL logical operators is NOT → AND → OR. For example, A OR B AND C is evaluated as A OR (B AND C). To avoid unintended results, always use parentheses for complex conditions.

When listing multiple OR conditions, using the IN clause is more readable. salary = 260000 OR salary = 350000 is equivalent to salary IN (260000, 350000).

AND, OR, and NOT can also be used in HAVING clauses, not just WHERE.