SELECT and WHERE

SELECT Statement

The SELECT statement is used to retrieve data from one or more tables. It is the most commonly used SQL command and forms the foundation of data querying.

Basic Syntax

-- Select all columns
SELECT * FROM table_name;

-- Select specific columns
SELECT column1, column2 FROM table_name;

-- Select with aliases
SELECT column1 AS alias1, column2 AS alias2 FROM table_name;

Sample Schema for Examples

-- Create sample tables
CREATE TABLE employees (
    emp_id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    department VARCHAR(50),
    salary DECIMAL(10,2),
    hire_date DATE,
    is_active BOOLEAN DEFAULT TRUE
);

-- Insert sample data
INSERT INTO employees (first_name, last_name, email, department, salary, hire_date, is_active)
VALUES
    ('John', 'Smith', 'john.smith@company.com', 'Engineering', 85000.00, '2020-01-15', TRUE),
    ('Jane', 'Doe', 'jane.doe@company.com', 'Marketing', 72000.00, '2019-06-20', TRUE),
    ('Bob', 'Johnson', 'bob.j@company.com', 'Engineering', 92000.00, '2018-03-10', TRUE),
    ('Alice', 'Williams', 'alice.w@company.com', 'HR', 68000.00, '2021-09-01', TRUE),
    ('Charlie', 'Brown', 'charlie.b@company.com', 'Marketing', 71000.00, '2020-11-15', FALSE);

SELECT Examples

-- Select all employees
SELECT * FROM employees;

-- Select specific columns
SELECT first_name, last_name, salary FROM employees;

-- Select with computed columns
SELECT 
    first_name,
    last_name,
    salary,
    salary * 0.1 AS bonus,
    salary * 1.1 AS total_compensation
FROM employees;

-- Select with aliases
SELECT 
    first_name AS "First Name",
    last_name AS "Last Name",
    salary AS "Annual Salary"
FROM employees;

-- Select distinct values
SELECT DISTINCT department FROM employees;

-- Count results
SELECT COUNT(*) AS total_employees FROM employees;

SELECT with Expressions

-- String concatenation
SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM employees;

-- Mathematical operations
SELECT 
    salary,
    salary / 12 AS monthly_salary,
    salary * 0.15 AS tax_estimate
FROM employees;

-- Date functions
SELECT 
    hire_date,
    YEAR(hire_date) AS hire_year,
    DATEDIFF(CURDATE(), hire_date) AS days_employed
FROM employees;

The SELECT statement is your primary tool for extracting data from the database.

WHERE Clause

The WHERE clause filters rows based on specified conditions. Only rows that satisfy the condition are returned in the result set.

Basic Syntax

SELECT column1, column2
FROM table_name
WHERE condition;

WHERE Examples

-- Find employees in Engineering
SELECT first_name, last_name, salary
FROM employees
WHERE department = 'Engineering';

-- Find high earners
SELECT first_name, last_name, salary
FROM employees
WHERE salary > 80000;

-- Find employees hired after 2020
SELECT first_name, last_name, hire_date
FROM employees
WHERE hire_date > '2020-01-01';

-- Find active employees in Marketing
SELECT first_name, last_name
FROM employees
WHERE department = 'Marketing' AND is_active = TRUE;

-- Find employees with salary between 70000 and 90000
SELECT first_name, last_name, salary
FROM employees
WHERE salary BETWEEN 70000 AND 90000;

-- Find employees whose name starts with 'J'
SELECT first_name, last_name
FROM employees
WHERE first_name LIKE 'J%';

-- Find employees in specific departments
SELECT first_name, last_name, department
FROM employees
WHERE department IN ('Engineering', 'Marketing');

-- Find employees without a bonus (NULL check)
SELECT first_name, last_name
FROM employees
WHERE salary IS NOT NULL;

WHERE with Multiple Conditions

-- AND: Both conditions must be true
SELECT first_name, last_name, salary, department
FROM employees
WHERE department = 'Engineering' AND salary > 85000;

-- OR: At least one condition must be true
SELECT first_name, last_name, department
FROM employees
WHERE department = 'Engineering' OR department = 'Marketing';

-- NOT: Negates a condition
SELECT first_name, last_name, department
FROM employees
WHERE NOT department = 'HR';

-- Combining AND, OR, NOT
SELECT first_name, last_name, salary
FROM employees
WHERE (department = 'Engineering' OR department = 'Marketing')
  AND salary > 70000
  AND is_active = TRUE;

NULL Handling in WHERE

-- NULL is not equal to anything, use IS NULL / IS NOT NULL
SELECT * FROM employees WHERE department = NULL;     -- Returns nothing!
SELECT * FROM employees WHERE department IS NULL;    -- Correct!
SELECT * FROM employees WHERE department IS NOT NULL;

Common WHERE Patterns

-- Pattern matching with LIKE
SELECT * FROM employees WHERE email LIKE '%@company.com';
SELECT * FROM employees WHERE first_name LIKE '_ohn';  -- exactly 4 chars

-- Range queries
SELECT * FROM employees WHERE hire_date BETWEEN '2020-01-01' AND '2021-12-31';

-- List membership
SELECT * FROM employees WHERE department IN ('Engineering', 'HR', 'Finance');

-- Negation
SELECT * FROM employees WHERE department NOT IN ('HR');
SELECT * FROM employees WHERE salary NOT BETWEEN 60000 AND 70000;

The WHERE clause is essential for filtering data to find exactly what you need.

Logical Operators

Logical operators combine multiple conditions in a WHERE clause. They evaluate to TRUE, FALSE, or UNKNOWN (for NULL values).

AND Operator

Returns TRUE only if both conditions are true.

-- Both conditions must be satisfied
SELECT first_name, last_name, salary, department
FROM employees
WHERE department = 'Engineering' AND salary > 85000;

-- Multiple AND conditions
SELECT * FROM employees
WHERE department = 'Engineering'
  AND salary > 80000
  AND is_active = TRUE
  AND hire_date > '2019-01-01';

OR Operator

Returns TRUE if at least one condition is true.

-- Either condition can be satisfied
SELECT first_name, last_name, department
FROM employees
WHERE department = 'Engineering' OR department = 'Marketing';

-- OR with different columns
SELECT first_name, last_name
FROM employees
WHERE salary > 90000 OR department = 'HR';

NOT Operator

Negates a condition.

-- Negates the condition
SELECT first_name, last_name, department
FROM employees
WHERE NOT department = 'Engineering';

-- NOT with IN
SELECT * FROM employees
WHERE department NOT IN ('Engineering', 'Marketing');

-- NOT with BETWEEN
SELECT * FROM employees
WHERE salary NOT BETWEEN 70000 AND 80000;

Operator Precedence

1. NOT (highest)
2. AND
3. OR (lowest)

-- Without parentheses (AND evaluated first)
SELECT * FROM employees
WHERE department = 'Engineering' OR department = 'Marketing' AND salary > 80000;
-- Equivalent to: department = 'Engineering' OR (department = 'Marketing' AND salary > 80000)

-- With parentheses (explicit grouping)
SELECT * FROM employees
WHERE (department = 'Engineering' OR department = 'Marketing') AND salary > 80000;
-- Both departments considered, then filtered by salary

Best Practices

1. Always use parentheses when mixing AND and OR
2. Avoid complex NOT - use != or <> instead
3. Be explicit about NULL handling
4. Use parentheses for readability even when not required

-- Good: Clear parentheses
SELECT * FROM employees
WHERE (department = 'Engineering' OR department = 'Marketing')
  AND salary > 80000;

-- Bad: Ambiguous without parentheses
SELECT * FROM employees
WHERE department = 'Engineering' OR department = 'Marketing' AND salary > 80000;

Three-Valued Logic

SQL uses three-valued logic: TRUE, FALSE, and UNKNOWN.

-- NULL comparisons return UNKNOWN, not TRUE or FALSE
SELECT * FROM employees WHERE NULL = NULL;     -- Returns nothing
SELECT * FROM employees WHERE NULL = 1;        -- Returns nothing
SELECT * FROM employees WHERE NULL <> 1;       -- Returns nothing
SELECT * FROM employees WHERE NULL IS NULL;    -- Returns rows where NULL

Understanding logical operators is crucial for building complex filter conditions.

Comparison Operators

Comparison operators compare values and return TRUE or FALSE. They are used in WHERE clauses to filter data.

Basic Comparison Operators

Operator	Meaning	Example
=	Equal to	WHERE salary = 85000
!= or <>	Not equal to	WHERE department != 'HR'
>	Greater than	WHERE salary > 80000
<	Less than	WHERE salary < 70000
>=	Greater than or equal	WHERE salary >= 85000
<=	Less than or equal	WHERE salary <= 75000

Examples

-- Equal to
SELECT * FROM employees WHERE department = 'Engineering';

-- Not equal to
SELECT * FROM employees WHERE department <> 'HR';

-- Greater than
SELECT * FROM employees WHERE salary > 85000;

-- Less than or equal
SELECT * FROM employees WHERE hire_date <= '2020-12-31';

-- Combining comparisons
SELECT first_name, last_name, salary
FROM employees
WHERE salary >= 70000 AND salary <= 90000;

Special Comparison Operators

BETWEEN

-- Range inclusive (>= and <=)
SELECT * FROM employees
WHERE salary BETWEEN 75000 AND 90000;

-- Date range
SELECT * FROM employees
WHERE hire_date BETWEEN '2020-01-01' AND '2021-12-31';

-- Not in range
SELECT * FROM employees
WHERE salary NOT BETWEEN 70000 AND 80000;

IN

-- Match any value in list
SELECT * FROM employees
WHERE department IN ('Engineering', 'Marketing', 'HR');

-- Not in list
SELECT * FROM employees
WHERE department NOT IN ('Engineering', 'Marketing');

-- IN with subquery
SELECT * FROM employees
WHERE department IN (SELECT department FROM departments WHERE location = 'NYC');

LIKE

-- % matches any sequence of characters
SELECT * FROM employees WHERE first_name LIKE 'J%';      -- Starts with J
SELECT * FROM employees WHERE email LIKE '%@company.com'; -- Ends with @company.com
SELECT * FROM employees WHERE name LIKE '%smith%';        -- Contains smith

-- _ matches exactly one character
SELECT * FROM employees WHERE first_name LIKE '_ohn';     -- 4 chars, ends with ohn
SELECT * FROM employees WHERE phone LIKE '555-____';      -- 555- followed by 4 chars

-- Escape special characters
SELECT * FROM products WHERE description LIKE '%100\\%%' ESCAPE '\\';

NULL Comparison

-- NULL is not equal to anything
SELECT * FROM employees WHERE department = NULL;  -- Returns nothing!
SELECT * FROM employees WHERE department IS NULL; -- Correct!
SELECT * FROM employees WHERE department IS NOT NULL;

-- NULL in expressions
SELECT * FROM employees
WHERE salary > 80000 OR department IS NULL;

Comparison Operator Best Practices

-- Good: Use >= and < for ranges (avoids NULL issues)
SELECT * FROM employees
WHERE salary >= 75000 AND salary <= 90000;

-- Also good: BETWEEN (inclusive)
SELECT * FROM employees
WHERE salary BETWEEN 75000 AND 90000;

-- Avoid: String comparison for dates
SELECT * FROM employees WHERE hire_date > '2020-01-01';  -- Good
SELECT * FROM employees WHERE YEAR(hire_date) > 2020;    -- Less efficient

Performance Tips

1. Avoid functions on indexed columns in WHERE clauses
2. Use appropriate data types for comparisons
3. Consider索引 usage for frequently queried columns

-- Bad: Function on column (prevents index use)
SELECT * FROM employees WHERE YEAR(hire_date) = 2020;

-- Good: Range comparison (can use index)
SELECT * FROM employees 
WHERE hire_date >= '2020-01-01' AND hire_date < '2021-01-01';

Comparison operators are the building blocks of filtering data in SQL queries.