Filtering Rows Using Logical Operators

Single conditions in WHERE clauses are often insufficient for complex data filtering needs. PostgreSQL provides powerful logical operators that allow you to combine multiple conditions, creating sophisticated filters that can precisely target the data you need. This guide covers how to use AND, OR, and NOT operators effectively in your SQL queries.

Introduction to Logical Operators

Logical operators enable you to build compound logic in your queries by combining multiple conditions. PostgreSQL supports three primary logical operators:

Setting Up Sample Data

Let's create an employees table to demonstrate logical operators:

CREATE TABLE employees (
  id SERIAL PRIMARY KEY,
  name TEXT,
  department TEXT,
  salary NUMERIC,
  active BOOLEAN,
  hire_date DATE,
  city TEXT
);

INSERT INTO employees (name, department, salary, active, hire_date, city) VALUES
('Alice', 'Engineering', 75000, true, '2020-03-15', 'New York'),
('Bob', 'HR', 50000, true, '2019-08-01', 'Chicago'),
('Charlie', 'Engineering', 72000, false, '2021-01-10', 'San Francisco'),
('Diana', 'Marketing', 65000, true, '2022-05-21', 'Los Angeles'),
('Eve', 'HR', 51000, false, '2018-11-30', 'Chicago'),
('Frank', 'Marketing', 62000, true, '2023-02-14', 'New York'),
('Grace', 'Engineering', 78000, true, '2021-09-05', 'San Francisco');

Using the AND Operator

The AND operator requires all conditions to be true for a row to be included in the result:

-- Find active employees in Engineering
SELECT * FROM employees
WHERE department = 'Engineering' AND active = true;

-- Find high-earning employees in specific cities
SELECT name, salary, city FROM employees
WHERE salary > 70000 AND city IN ('New York', 'San Francisco');

-- Multiple AND conditions
SELECT * FROM employees
WHERE department = 'Engineering' 
  AND active = true 
  AND salary > 70000;

AND Truth Table

Practical AND Examples

-- Recent high-performing employees
SELECT name, department, salary, hire_date
FROM employees
WHERE salary > 65000 AND hire_date >= '2021-01-01';

-- Active employees in specific departments
SELECT name, department FROM employees
WHERE active = true AND department IN ('Engineering', 'Marketing');

-- Salary range filtering
SELECT name, salary FROM employees
WHERE salary >= 60000 AND salary <= 75000;

Using the OR Operator

The OR operator returns true when at least one condition is true:

-- Find employees in HR or Marketing
SELECT * FROM employees
WHERE department = 'HR' OR department = 'Marketing';

-- Find high earners or employees in specific cities
SELECT name, salary, city FROM employees
WHERE salary > 70000 OR city = 'Chicago';

-- Multiple OR conditions
SELECT * FROM employees
WHERE department = 'HR' 
   OR department = 'Marketing' 
   OR salary > 75000;

OR Truth Table

Practical OR Examples

-- Employees needing attention (inactive or low salary)
SELECT name, department, salary, active
FROM employees
WHERE active = false OR salary < 55000;

-- Flexible department filtering
SELECT name, department FROM employees
WHERE department = 'Engineering' OR department LIKE '%ing';

-- Date-based OR conditions
SELECT name, hire_date FROM employees
WHERE hire_date < '2020-01-01' OR hire_date > '2022-01-01';

Using the NOT Operator

The NOT operator negates a condition, returning the opposite result:

-- Find employees not in Engineering
SELECT * FROM employees
WHERE NOT department = 'Engineering';

-- Alternative syntax (more common)
SELECT * FROM employees
WHERE department != 'Engineering';

-- Find inactive employees
SELECT * FROM employees
WHERE NOT active;

-- Complex NOT conditions
SELECT * FROM employees
WHERE NOT (salary > 70000 AND department = 'Engineering');

NOT Truth Table

Practical NOT Examples

-- Employees not in specific cities
SELECT name, city FROM employees
WHERE NOT city IN ('New York', 'Chicago');

-- Employees not recently hired
SELECT name, hire_date FROM employees
WHERE NOT hire_date >= '2022-01-01';

-- Complex exclusions
SELECT * FROM employees
WHERE NOT (department = 'HR' AND salary < 52000);

Operator Precedence and Grouping

Understanding operator precedence is crucial for writing correct logical expressions:

Precedence Rules

Precedence Examples

-- Without parentheses (AND has higher precedence)
SELECT * FROM employees
WHERE department = 'Engineering' AND salary > 70000 OR active = false;

-- This is evaluated as:
-- (department = 'Engineering' AND salary > 70000) OR (active = false)

-- With explicit parentheses for clarity
SELECT * FROM employees
WHERE department = 'Engineering' AND (salary > 70000 OR active = false);

-- Different grouping changes the logic
SELECT * FROM employees
WHERE (department = 'Engineering' OR department = 'Marketing') AND salary > 65000;

Best Practice: Always Use Parentheses

-- Unclear intent
SELECT * FROM employees
WHERE department = 'HR' OR department = 'Marketing' AND salary > 60000;

-- Clear intent with parentheses
SELECT * FROM employees
WHERE (department = 'HR' OR department = 'Marketing') AND salary > 60000;

-- Different logic, also clear
SELECT * FROM employees
WHERE department = 'HR' OR (department = 'Marketing' AND salary > 60000);

Complex Logical Expressions

Combining Multiple Operators

-- Complex employee filtering
SELECT name, department, salary, active, city
FROM employees
WHERE (department = 'Engineering' OR department = 'Marketing')
  AND salary BETWEEN 60000 AND 80000
  AND active = true
  AND city != 'Chicago';

-- Flexible search criteria
SELECT * FROM employees
WHERE (
    (department = 'Engineering' AND salary > 70000)
    OR 
    (department = 'Marketing' AND city = 'New York')
    OR
    (active = false AND hire_date < '2020-01-01')
  );

Conditional Logic Patterns

-- Either high salary OR in premium departments
SELECT name, department, salary
FROM employees
WHERE salary > 75000 
   OR department IN ('Engineering', 'Marketing');

-- Exclude specific combinations
SELECT * FROM employees
WHERE NOT (department = 'HR' AND salary < 52000)
  AND NOT (active = false AND hire_date > '2022-01-01');

-- Complex date and status logic
SELECT name, hire_date, active, department
FROM employees
WHERE (
    (hire_date >= '2021-01-01' AND active = true)
    OR
    (hire_date < '2020-01-01' AND department = 'Engineering')
  );

Working with NULL Values in Logical Operations

NULL values require special consideration in logical operations:

-- Add some NULL values for demonstration
UPDATE employees SET city = NULL WHERE name = 'Alice';

-- AND with NULL
SELECT name, city, department FROM employees
WHERE city = 'New York' AND department = 'Engineering';
-- Alice won't appear because city IS NULL

-- Handling NULLs explicitly
SELECT name, city, department FROM employees
WHERE (city = 'New York' OR city IS NULL) AND department = 'Engineering';

-- OR with NULL
SELECT name, city FROM employees
WHERE city = 'Chicago' OR city IS NULL;

Three-Valued Logic with Logical Operators

Real-World Applications

Employee Management System

-- Performance review candidates
SELECT name, department, salary, hire_date
FROM employees
WHERE active = true 
  AND hire_date <= CURRENT_DATE - INTERVAL '1 year'
  AND (salary < 70000 OR department = 'HR');

-- Promotion eligibility
SELECT name, department, salary
FROM employees
WHERE active = true
  AND salary < 80000
  AND (
    (department = 'Engineering' AND hire_date <= '2021-01-01')
    OR
    (department IN ('Marketing', 'HR') AND hire_date <= '2020-01-01')
  );

Reporting and Analytics

-- Department performance analysis
SELECT 
  department,
  COUNT(*) as total_employees,
  COUNT(CASE WHEN active = true THEN 1 END) as active_employees,
  AVG(salary) as avg_salary
FROM employees
WHERE department IN ('Engineering', 'Marketing', 'HR')
  AND (active = true OR hire_date >= '2020-01-01')
GROUP BY department;

-- Salary audit
SELECT name, department, salary,
  CASE 
    WHEN salary > 75000 AND department != 'Engineering' THEN 'Review Required'
    WHEN salary < 50000 AND active = true THEN 'Below Minimum'
    ELSE 'OK'
  END as audit_status
FROM employees
WHERE active = true OR salary > 70000;

Performance Considerations

Index Usage with Logical Operators

-- Create indexes for better performance
CREATE INDEX idx_employees_dept_active ON employees(department, active);
CREATE INDEX idx_employees_salary ON employees(salary);
CREATE INDEX idx_employees_hire_date ON employees(hire_date);

-- Efficient query using indexes
EXPLAIN ANALYZE
SELECT * FROM employees
WHERE department = 'Engineering' AND active = true;

-- Less efficient due to OR (may not use indexes effectively)
EXPLAIN ANALYZE
SELECT * FROM employees
WHERE department = 'Engineering' OR salary > 75000;

Optimization Tips

Common Mistakes and Solutions

Mistake 1: Incorrect Precedence Assumptions

-- Problematic (unclear intent)
SELECT * FROM employees
WHERE department = 'HR' OR department = 'Marketing' AND salary > 60000;

-- Solution (explicit grouping)
SELECT * FROM employees
WHERE (department = 'HR' OR department = 'Marketing') AND salary > 60000;

Mistake 2: Ignoring NULL Values

-- Problematic (NULLs excluded unexpectedly)
SELECT * FROM employees
WHERE city = 'New York' OR city != 'New York';

-- Solution (handle NULLs explicitly)
SELECT * FROM employees
WHERE city = 'New York' OR city != 'New York' OR city IS NULL;

Mistake 3: Inefficient OR Conditions

-- Potentially slow
SELECT * FROM employees
WHERE department = 'Engineering' OR salary > 75000;

-- Potentially faster (if indexes support it)
SELECT * FROM employees WHERE department = 'Engineering'
UNION
SELECT * FROM employees WHERE salary > 75000;

Practice Exercises

Try these exercises with the sample data:

1. Find all active employees in HR
2. Find employees who are either in Marketing or earn more than 70,000
3. Find employees who are NOT in the Engineering department
4. Find employees who are either inactive OR have a salary under 60,000
5. Find active employees hired after 2020 in Engineering or Marketing
6. Find employees in New York or San Francisco with salaries between 65,000 and 80,000
7. Find employees who are NOT (in HR with salary less than 52,000)

Exercise Solutions

-- 1. Active HR employees
SELECT * FROM employees WHERE department = 'HR' AND active = true;

-- 2. Marketing or high earners
SELECT * FROM employees WHERE department = 'Marketing' OR salary > 70000;

-- 3. Not in Engineering
SELECT * FROM employees WHERE NOT department = 'Engineering';

-- 4. Inactive or low salary
SELECT * FROM employees WHERE active = false OR salary < 60000;

-- 5. Recent hires in specific departments
SELECT * FROM employees 
WHERE active = true 
  AND hire_date > '2020-01-01' 
  AND (department = 'Engineering' OR department = 'Marketing');

-- 6. Location and salary criteria
SELECT * FROM employees 
WHERE (city = 'New York' OR city = 'San Francisco') 
  AND salary BETWEEN 65000 AND 80000;

-- 7. Complex NOT condition
SELECT * FROM employees 
WHERE NOT (department = 'HR' AND salary < 52000);

Best Practices Summary

1. Use parentheses liberally: Make your logical expressions clear and unambiguous
2. Understand precedence: Know that AND has higher precedence than OR
3. Handle NULLs explicitly: Consider how NULL values affect your logical conditions
4. Test complex conditions: Verify that your logical expressions return expected results
5. Consider performance: Structure conditions to take advantage of indexes
6. Use meaningful names: Make your conditions self-documenting
7. Break down complex logic: Use CTEs or subqueries for very complex conditions