MySQL AND Operator

MySQL AND Operator

The AND operator in MySQL is used to combine two or more conditions in a WHERE, HAVING, or ON clause. It ensures that all the specified conditions must be evaluated  TRUE for the query to return results.

Syntax

condition1 AND condition2 [AND condition3 ...]

• condition1, condition2, ...: These are the conditions that need to be satisfied.

Key Points

1. The AND operator returns:
   • TRUE if all conditions are TRUE.
   • FALSE if at least one condition is FALSE.
   • NULL if any condition evaluates to NULL and none are explicitly FALSE.
2. Commonly used in:
   • Filtering rows in the WHERE clause.
   • Grouped conditions in the HAVING clause.
   • Joining tables with conditions in the ON clause.

Examples

1. Basic Usage

Retrieve employees with salaries greater than $50,000 AND in department 1:

SELECT employee_id, name, salary, department_id
FROM employees
WHERE salary > 50000 AND department_id = 1;

Output:

employee_id | name    | salary | department_id
------------|---------|--------|--------------
101         | John    | 60000  | 1
102         | Alice   | 70000  | 1

2. Using Multiple AND Conditions

Find orders placed in 2025 AND by customers from region "North":

SELECT order_id, customer_id, region, order_date
FROM orders
WHERE YEAR(order_date) = 2025 AND region = 'North';

3. Combining AND with Other Operators

Retrieve products priced between $50 and $100 AND available in stock:

SELECT product_id, product_name, price, stock
FROM products
WHERE price BETWEEN 50 AND 100 AND stock > 0;

4. Using AND in Joins

Retrieve orders where the customer's region is "East" AND the product category is "Electronics":

SELECT o.order_id, c.customer_name, p.product_name
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
JOIN products p ON o.product_id = p.product_id
WHERE c.region = 'East' AND p.category = 'Electronics';

5. Using AND with HAVING

Find customers who placed orders with a total value greater than $1,000 AND have more than 5 orders:

SELECT customer_id, COUNT(order_id) AS total_orders, SUM(order_value) AS total_value
FROM orders
GROUP BY customer_id
HAVING total_value > 1000 AND total_orders > 5;

6. Using AND with NULL Values

The AND operator returns NULL if one condition is NULL and no condition explicitly evaluates to FALSE.

Example:

SELECT 1 AND NULL, 1 AND 0, 1 AND 1;

Output:

1 AND NULL | 1 AND 0 | 1 AND 1
-----------|---------|--------
NULL       | 0       | 1

Best Practices

1. Parentheses for Clarity:

   • Use parentheses to group conditions, especially when combining AND with OR.

   SELECT * 
   FROM employees 
   WHERE (salary > 50000 AND department_id = 1) OR title = 'Manager';
   

2. Optimize Index Usage:

   • Ensure columns used in AND conditions are indexed for better performance.

3. NULL Handling:

   • Be aware of NULL values in conditions, as they can lead to unexpected results.

4. Avoid Overusing Conditions:

   • Simplify conditions where possible to maintain query performance and readability.

Conclusion

The AND operator is a fundamental tool in MySQL for filtering data based on multiple conditions. When used effectively, it helps in retrieving precisely filtered results. Combine it with other operators and clauses for complex queries, ensuring clarity and performance optimization.