SQL Logical Operators (AND, OR, NOT) 🔗
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! 💡
🌟 The Scenario: The Library Search 📚
Imagine you are searching for a book in a library.
- AND: "I want a book by 'J.K. Rowling' AND it must be 'Hardcover'." (Both must be true). ✅
- OR: "I want a book by 'J.K. Rowling' OR 'George R.R. Martin'." (Either one is fine). ✅
- NOT: "I want books by 'J.K. Rowling' but NOT the ones about 'Harry Potter'." ✅
🎨 Visual Logic: The Truth Table
💻 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 combiningANDandOR. It makes your code easier to read for humans and prevents logic errors for the computer!"