Lesson 07 of 61

5 mins read

SQL Logical Operators: AND, OR, NOT & Operator Precedence

In Plain English

Logical operators combine multiple search conditions in a `WHERE` clause: `AND` requires ALL conditions to be true, `OR` requires AT LEAST ONE condition to be true, and `NOT` reverses a condition. Crucially, `AND` has higher precedence than `OR`, requiring parentheses `()` to enforce correct evaluation logic.

The Mental Model

"`AND` is two keys required to open a safe; `OR` is having a physical key OR an electronic passcode; Parentheses `()` are mathematical brackets ensuring operations calculate in intended order."

Deep Dive: How It Works

Operator Precedence: `NOT` evaluates first, then `AND`, then `OR`.

The Deadly Precedence Trap: `WHERE role = 'Admin' OR role = 'Manager' AND status = 'active'` evaluates as `role = 'Admin' OR (role = 'Manager' AND status = 'active')`, unintentionally exposing inactive admins!

Fixing with Parentheses: `WHERE (role = 'Admin' OR role = 'Manager') AND status = 'active'`.

Syntax Blueprint

SELECT *
FROM table_name
WHERE (condition1 OR condition2) AND NOT condition3;

Always group OR conditions inside parentheses when combining with AND.

Core Rules to Remember

Precedence Rule: AND binds tighter than OR. Always use parentheses when mixing them.

NOT Operator: Inverts conditions: `WHERE NOT status = 'banned'` or `WHERE NOT (age < 18)`.

Short-Circuit Optimization: Database engines evaluate cheaper predicates first when possible.

Common Beginner Traps & How to Fix Them

Mistake:Writing `WHERE status = 'active' AND role = 'admin' OR role = 'editor'` without parentheses.

Why it happens: Assuming left-to-right evaluation order.

How to fix: Group OR branches explicitly: `WHERE status = 'active' AND (role = 'admin' OR role = 'editor')`.

Pro Tip: When testing a column against multiple possible values, prefer `IN ('admin', 'editor')` over chained `OR` statements for clarity.

Live Interactive Example

Hit Run Code to see it live

Combining Logic with Parentheses and NOT

Python 3.12

Output Console

Click "Run Code" to view the rendered output.

How it works: Parentheses ensured that inactive VIP Bob was excluded and only active US VIP/Premiums matched.

Your Turn: Micro Challenge

No pressure! Edit the starter code below and test your solution with instant feedback.

Micro Exercise

Filter with AND, OR, and Parentheses

Select `name` from `vehicles` where `(type = 'Car' OR type = 'Truck') AND available = 1`.

Sandbox Output

Click "Run & Check" to test your solution.

Finished reading and practicing?

Mark this lesson as completed to update your course progress.