SQL AND Operator

The AND operator is a logical operator in SQL that allows you to narrow down your query results by requiring multiple conditions to be met. Think of it as a strict filter: for a row to appear in your results, it must pass every single test you define in the WHERE clause.

Basic Syntax of AND Operator

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

• condition1: The primary filter applied to the data.
• condition2: An additional filter that must also be satisfied.
• Logical Result: A row is only included in the result set if condition1, condition2, and all subsequent conditions evaluate to TRUE.

Key Features of AND Operator

1. Combining Multiple Conditions

The AND operator is essential when you need to be specific. Instead of getting all employees, you can target a very specific subgroup by stacking requirements.

SELECT name, age, city  
FROM employees  
WHERE age > 30 AND city = 'New York';

• This query excludes anyone under 31, and it also excludes anyone who doesn't live in New York. Only the "intersection" of these two groups is returned.

2. Filtering Based on Multiple Columns

You aren't restricted to filtering by just one column. You can use AND to create cross-column validation, which is common in inventory and reporting systems.

SELECT product_name, price, stock  
FROM products  
WHERE price < 100 AND stock > 50;

• This is a practical example of finding "affordable, high-availability" products. It filters the price column and the stock column simultaneously.

3. Using AND with Comparison Operators

The AND operator acts as the logical "glue" between different comparison expressions, such as equals (=), greater than (>), or not equal to (!=).

SELECT name, department, salary  
FROM employees  
WHERE department = 'HR' AND salary > 5000;

• This query helps identify high-earning individuals within a specific department.

Example Queries

1. Filter Employees by Age and City

In a real-world HR system, you might need to find local candidates for a senior role.

SELECT name, age, city  
FROM employees  
WHERE age >= 25 AND city = 'Chicago';

• This finds employees who have reached a certain age threshold and are located in the Chicago office.

2. Filter Products by Price and Stock

In e-commerce, you often want to display items that are both within a budget and currently ready to ship.

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

• Note that BETWEEN itself uses an internal AND logic. This query ensures the price is in a range and the item isn't sold out.

3. Using AND with Dates

Working with timeframes is one of the most common uses for the AND operator in business intelligence reporting.

SELECT order_id, order_date, customer_id  
FROM orders  
WHERE order_date >= '2024-01-01' AND order_date <= '2024-06-30';

• This retrieves all records for the first half of 2024. Standard SQL dates are typically formatted as 'YYYY-MM-DD'.

4. AND with Multiple Conditions

You can chain as many AND operators as needed to pinpoint specific records.

SELECT name, position, salary, city  
FROM employees  
WHERE position = 'Manager' AND city = 'Los Angeles' AND salary > 6000;

• This query is highly specific, filtering by three distinct criteria to find high-paid managers in a specific branch.

Combining AND with OR

Complex logic often requires combining AND and OR. However, SQL evaluates AND before OR by default (similar to how multiplication is done before addition in math).

SELECT name, department, salary  
FROM employees  
WHERE department = 'Sales' AND (salary > 7000 OR city = 'New York');

• This query finds employees who are in Sales and meet at least one of these two criteria: earning over 7000 or living in New York.
• Parentheses are vital here. Without them, the logic would change completely.

Summary

The AND operator is the cornerstone of data filtering in SQL. It allows you to create precise queries by ensuring every condition is satisfied before a row is returned. By mastering AND, especially in combination with parentheses and other logical operators, you can extract meaningful insights from even the largest datasets.