Skip to content

SQL Logical Operators (AND, OR, NOT) 🔗

Prerequisites: WHERE Clause

Mentor's Note: Logical operators are the "Brains" of your query. They allow you to combine simple rules into complex decisions. "Show me users who are from Surat AND have spent over ₹5000." This is how databases power modern apps! 💡

Imagine you are searching for a book in a library.

  1. AND: "I want a book by 'J.K. Rowling' AND it must be 'Hardcover'." (Both must be true). ✅
  2. OR: "I want a book by 'J.K. Rowling' OR 'George R.R. Martin'." (Either one is fine). ✅
  3. NOT: "I want books by 'J.K. Rowling' but NOT the ones about 'Harry Potter'." ✅

🎨 Visual Logic: The Truth Table

graph TD
    A[Condition 1] --> C{Operator}
    B[Condition 2] --> C
    C -- AND --> D[True ONLY if BOTH match]
    C -- OR --> E[True if ANY match]
    C -- NOT --> F[True if DOES NOT match]

💻 1. The AND Operator (The Strict Filter)

The result is TRUE only if all conditions are met.

-- Scenario: Find active students in the IT department
SELECT name, department, status
FROM students
WHERE department = 'IT' 
AND status = 'Active';

💻 2. The OR Operator (The Flexible Filter)

The result is TRUE if at least one condition is met.

-- Scenario: Find students from Surat or Mumbai
SELECT name, city
FROM students
WHERE city = 'Surat' 
OR city = 'Mumbai';

💻 3. The NOT Operator (The Exclusion Filter)

Used to find records where a condition is not met.

-- Scenario: Find all employees EXCEPT those in Sales
SELECT emp_name, department
FROM employees
WHERE NOT department = 'Sales';

⚠️ Important: Operator Precedence (The Parentheses Trap)

SQL processes AND before OR. If you mix them without parentheses ( ), you might get wrong results!

-- ❌ Risky Logic: Finds IT staff OR anyone earning > 50k
SELECT * FROM employees 
WHERE dept = 'IT' OR dept = 'HR' AND salary > 50000;

-- ✅ Correct Logic: Finds staff in (IT or HR) who ALSO earn > 50k
SELECT * FROM employees 
WHERE (dept = 'IT' OR dept = 'HR') AND salary > 50000;

💡 Pro Tip

"Always use Parentheses () when combining AND and OR. It makes your code easier to read for humans and prevents logic errors for the computer!"

📈 Learning Path