Previous

SQL AND Operator

The AND operator in SQL is used to combine multiple conditions in a WHERE clause. It ensures that only rows meeting all specified conditions are returned.

 

Basic Syntax of AND Operator

SELECT column1, column2, ...
FROM table_name
WHERE condition1 AND condition2 AND condition3;
  • condition1: The first condition to evaluate.
  • condition2: The second condition to evaluate.
  • Rows are returned only if both conditions are true.

 

Key Features of AND Operator

1. Combining Multiple Conditions

Use the AND operator to check multiple conditions in a single query.

SELECT name, age, city  
FROM employees  
WHERE age > 30 AND city = 'New York';
  • This query retrieves all employees older than 30 and living in New York.

2. Filtering Based on Multiple Columns

You can filter rows based on conditions applied to different columns.

SELECT product_name, price, stock  
FROM products  
WHERE price < 100 AND stock > 50;
  • This retrieves products where the price is less than 100 and the stock is greater than 50.

3. Using AND with Comparison Operators

The AND operator can combine conditions using comparison operators (=, >, <, !=, etc.).

SELECT name, department, salary  
FROM employees  
WHERE department = 'HR' AND salary > 5000;
  • This retrieves employees in the HR department earning more than 5000.

 

Example Queries

1. Filter Employees by Age and City

SELECT name, age, city  
FROM employees  
WHERE age >= 25 AND city = 'Chicago';
  • Retrieves employees who are 25 or older and live in Chicago.

2. Filter Products by Price and Stock

SELECT product_name, price, stock  
FROM products  
WHERE price BETWEEN 50 AND 200 AND stock > 0;
  • Retrieves products priced between 50 and 200 and in stock.

3. Using AND with Dates

SELECT order_id, order_date, customer_id  
FROM orders  
WHERE order_date >= '2024-01-01' AND order_date <= '2024-06-30';
  • Retrieves orders placed between January 1, 2024, and June 30, 2024.

4. AND with Multiple Conditions

SELECT name, position, salary, city  
FROM employees  
WHERE position = 'Manager' AND city = 'Los Angeles' AND salary > 6000;
  • Retrieves managers in Los Angeles earning more than 6000.

 

Combining AND with OR

You can combine the AND operator with OR for more complex conditions.

SELECT name, department, salary  
FROM employees  
WHERE department = 'Sales' AND (salary > 7000 OR city = 'New York');
  • This retrieves employees in the Sales department where the salary is greater than 7000 or the city is New York.
  • Parentheses ensure the correct evaluation order.

 

Summary

The AND operator is used in SQL to combine multiple conditions, ensuring all conditions are true before returning rows. It is often combined with comparison operators and works seamlessly with the OR operator for more complex filters.

Previous