MySQL AND Operator

MySQL AND Operator

The AND operator in MySQL is used to combine two or more conditions in a WHERE clause. It returns TRUE only when all the conditions are TRUE.

Syntax

SELECT column1, column2, ...
FROM table_name
WHERE condition1 AND condition2 AND ...;

1. Using AND with Multiple Conditions

Example

Retrieve employees who are in the "IT" department and earn a salary greater than 50,000.

SELECT id, name, department, salary
FROM employees
WHERE department = 'IT' AND salary > 50000;

Result

+----+-------+------------+--------+
| id | name  | department | salary |
+----+-------+------------+--------+
| 1  | Alice | IT         | 70000  |
| 2  | Bob   | IT         | 80000  |
+----+-------+------------+--------+

2. Combining AND with Other Operators

You can combine the AND operator with comparison operators such as =, >, <, >=, <=, and <> (not equal).

Example

Find products with a price greater than 100 and less than or equal to 500:

SELECT product_id, product_name, price
FROM products
WHERE price > 100 AND price <= 500;

3. Using AND with Logical Operators

You can use AND in conjunction with other logical operators like OR and NOT.

Example

Get customers from "New York" who have made purchases over 500 or are VIP members:

SELECT customer_id, name, city, purchase_amount
FROM customers
WHERE city = 'New York' AND (purchase_amount > 500 OR is_vip = 1);

4. AND with Multiple Conditions on the Same Column

You can use AND to specify multiple conditions for the same column.

Example

Get employees with salaries between 40,000 and 60,000:

SELECT id, name, salary
FROM employees
WHERE salary >= 40000 AND salary <= 60000;

5. Practical Example

Sample Table: orders

+----+------------+-----------+--------+
| id | customer   | product   | amount |
+----+------------+-----------+--------+
| 1  | Alice      | Laptop    | 1500   |
| 2  | Bob        | Monitor   | 300    |
| 3  | Charlie    | Smartphone| 800    |
| 4  | Alice      | Tablet    | 400    |
+----+------------+-----------+--------+

Query: Get orders where the amount is greater than 500 and the product is not "Laptop":

SELECT id, customer, product, amount
FROM orders
WHERE amount > 500 AND product <> 'Laptop';

Result:

+----+----------+-----------+-------+
| id | customer | product   | amount|
+----+----------+-----------+-------+
| 3  | Charlie  | Smartphone| 800   |
+----+----------+-----------+-------+

6. Common Errors

1. Operator Precedence: MySQL evaluates AND before OR. Use parentheses to clarify precedence.

   -- Without Parentheses
   SELECT * FROM orders WHERE product = 'Laptop' AND amount > 500 OR customer = 'Alice';
   
   -- With Parentheses
   SELECT * FROM orders WHERE (product = 'Laptop' AND amount > 500) OR customer = 'Alice';
   

2. Case Sensitivity in String Comparisons: MySQL string comparisons are case-insensitive unless configured otherwise.

7. Best Practices

1. Use Parentheses:
   When combining AND with OR, use parentheses to make conditions explicit and avoid confusion.

2. Optimize with Indexes:
   Ensure columns used in AND conditions are indexed to improve query performance.

Let me know if you need further examples or clarifications!