Conditional Queries in MySQL: WHERE and Logical Operators

Understanding the WHERE Clause

The WHERE clause in MySQL is used to filter records based on specific conditions. It can be applied in SELECT, UPDATE, and DELETE statements to ensure that only rows meeting the defined criteria are affected.

Basic Syntax:

SELECT column1, column2, ...
FROM table_name
WHERE condition;

• column1, column2, ... – The columns you want to retrieve.
• table_name – The table being queried.
• condition – A logical expression that determines which rows are selected.

Simple Conditional Queries

Let’s assume we have an employees table:

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    department VARCHAR(50),
    salary DECIMAL(10,2),
    hire_date DATE
);

Example 1: Filter by a Single Condition

SELECT first_name, last_name, salary
FROM employees
WHERE salary > 50000;

Step-by-Step Analysis:

• salary > 50000 is the condition that filters employees earning more than 50,000.
• Only rows meeting this criterion are returned.
• This ensures precise data retrieval and improves query efficiency.

Using Logical Operators

Logical operators allow you to combine multiple conditions to create more complex filters.

1. AND Operator

SELECT first_name, last_name, department, salary
FROM employees
WHERE department = 'Sales' AND salary > 60000;

Logic:

• Both conditions must be true for a row to be included.
• Only employees in the Sales department earning more than 60,000 are returned.

2. OR Operator

SELECT first_name, last_name, department, salary
FROM employees
WHERE department = 'Sales' OR department = 'Marketing';

Logic:

• Either condition being true will include the row in the results.
• Returns employees in either Sales or Marketing.

3. Combining AND and OR

SELECT first_name, last_name, department, salary
FROM employees
WHERE (department = 'Sales' OR department = 'Marketing') AND salary > 50000;

Logic:

• Parentheses group conditions to control evaluation order.
• Only employees in Sales or Marketing with a salary above 50,000 are returned.

4. NOT Operator

SELECT first_name, last_name, department
FROM employees
WHERE NOT department = 'HR';

Logic:

• Excludes rows where the department is HR.
• NOT reverses the logical outcome of a condition.

Using Comparison and Pattern Matching Operators

Conditional queries can also use operators like =, <>, <, >, <=, >=, BETWEEN, IN, and LIKE.

Example: Using BETWEEN

SELECT first_name, last_name, salary
FROM employees
WHERE salary BETWEEN 50000 AND 70000;

Logic:

• Selects employees earning between 50,000 and 70,000, inclusive.

Example: Using IN

SELECT first_name, last_name, department
FROM employees
WHERE department IN ('Sales', 'Marketing', 'IT');

Logic:

• Matches rows where the department is one of the listed values.
• Simplifies multiple OR conditions.

Example: Using LIKE

SELECT first_name, last_name, email
FROM employees
WHERE email LIKE '%@company.com';

Logic:

• % is a wildcard representing any sequence of characters.
• Returns employees whose email ends with @company.com.

Best Practices for Conditional Queries

1. Always Test Conditions: Use SELECT first to ensure your WHERE clause targets the correct rows.
2. Use Parentheses: Clarify precedence when combining AND and OR conditions.
3. Optimize Filters: Filter on indexed columns for better performance.
4. Avoid SELECT * with Conditions: Select only necessary columns for efficiency.
5. Use Pattern Matching Sparingly: LIKE can be slow on large tables without indexing.