SQL Filtering — WHERE, Operators, LIKE, IN, BETWEEN, NULL

-- COMPARISON OPERATORS
SELECT * FROM Employee WHERE Salary > 50000;
SELECT * FROM Employee WHERE Salary >= 50000;
SELECT * FROM Employee WHERE Salary < 30000;
SELECT * FROM Employee WHERE Status = 'Active';
SELECT * FROM Employee WHERE Status != 'Terminated';  -- Or: <> 'Terminated'

-- LOGICAL OPERATORS (AND, OR, NOT)
SELECT * FROM Employee
WHERE DeptID = 1 AND Salary > 50000;         -- Both conditions must be true

SELECT * FROM Employee
WHERE DeptID = 1 OR DeptID = 2;              -- Either condition true

SELECT * FROM Employee
WHERE NOT Status = 'Terminated';             -- Negation

-- Operator precedence: NOT > AND > OR
-- Use parentheses to be explicit:
SELECT * FROM Employee
WHERE (DeptID = 1 OR DeptID = 2)             -- Group OR first
  AND Salary > 50000                         -- Then AND
  AND NOT Status = 'Inactive';

-- BETWEEN (inclusive on both ends)
SELECT * FROM Employee WHERE Salary BETWEEN 40000 AND 80000;
-- Equivalent to: WHERE Salary >= 40000 AND Salary <= 80000
SELECT * FROM Orders WHERE OrderDate BETWEEN '2024-01-01' AND '2024-12-31';

-- IN (membership check — cleaner than multiple ORs)
SELECT * FROM Employee WHERE DeptID IN (1, 2, 5, 8);
-- Equivalent to: WHERE DeptID=1 OR DeptID=2 OR DeptID=5 OR DeptID=8

SELECT * FROM Employee WHERE DeptID NOT IN (3, 4);

-- IN with subquery
SELECT * FROM Employee
WHERE DeptID IN (SELECT DeptID FROM Department WHERE Budget > 500000);

-- IS NULL / IS NOT NULL
SELECT * FROM Employee WHERE ManagerID IS NULL;      -- Top-level managers
SELECT * FROM Employee WHERE ManagerID IS NOT NULL;  -- Employees with manager

-- Combine all
SELECT Name, Salary, DeptID FROM Employee
WHERE DeptID IN (1, 2, 3)
  AND Salary BETWEEN 30000 AND 100000
  AND Status IS NOT NULL
  AND Name != 'Admin'
ORDER BY Salary DESC;

-- % wildcard: matches ANY number of characters (including zero)
SELECT * FROM Employee WHERE Name LIKE 'R%';         -- Starts with R
SELECT * FROM Employee WHERE Name LIKE '%kumar';     -- Ends with kumar
SELECT * FROM Employee WHERE Email LIKE '%@gmail%';  -- Contains @gmail
SELECT * FROM Employee WHERE Name LIKE '%ra%';       -- Contains 'ra' anywhere

-- _ wildcard: matches EXACTLY ONE character
SELECT * FROM Employee WHERE Code LIKE 'EMP___';     -- EMP + exactly 3 chars
SELECT * FROM Employee WHERE Phone LIKE '98_______'; -- Starts 98 + 7 any chars

-- ILIKE (case-insensitive LIKE — PostgreSQL)
SELECT * FROM Employee WHERE Name ILIKE 'ravi%';     -- Matches Ravi, RAVI, ravi

-- LIKE with special characters (escape the % itself)
SELECT * FROM Product WHERE Description LIKE '100% discount' ESCAPE '';

-- NOT LIKE
SELECT * FROM Employee WHERE Email NOT LIKE '%@yahoo%';

-- Performance tip: Leading % disables index use
-- LIKE 'Ravi%'  → Can use index (prefix match)
-- LIKE '%Ravi%' → Full table scan (no index benefit)
-- Use full-text search for contains queries on large tables

-- SIMILAR TO (PostgreSQL: regex-like pattern)
SELECT * FROM Employee WHERE Phone SIMILAR TO '(98|99)[0-9]{8}';

-- REGEXP (MySQL)
SELECT * FROM Employee WHERE Name REGEXP '^[AEIOUaeiou]';  -- Starts with vowel

-- EXISTS: returns TRUE if subquery returns at least one row
-- Short-circuits as soon as first match found — efficient for large tables

-- Find employees who manage at least one other employee
SELECT e.Name FROM Employee e
WHERE EXISTS (
    SELECT 1 FROM Employee mgr WHERE mgr.ManagerID = e.EmpID
);

-- NOT EXISTS: find employees with NO subordinates
SELECT e.Name FROM Employee e
WHERE NOT EXISTS (
    SELECT 1 FROM Employee mgr WHERE mgr.ManagerID = e.EmpID
);

-- EXISTS vs IN comparison:
-- IN: executes subquery fully, stores result, then checks membership
SELECT Name FROM Employee WHERE DeptID IN (SELECT DeptID FROM Department WHERE Budget > 500000);

-- EXISTS: correlated — executes once per outer row, stops at first match
SELECT e.Name FROM Employee e
WHERE EXISTS (SELECT 1 FROM Department d WHERE d.DeptID = e.DeptID AND d.Budget > 500000);

-- EXISTS is generally faster when:
-- 1. Subquery returns many rows (IN stores all of them)
-- 2. You only need to know IF a match exists (EXISTS short-circuits)
-- IN is fine for small, static lists