Understanding the SQL AND Operator

Published : 06 July 2024
Updated : 06 July 2024

183 Views

AND operator is a fundamental logical operator used to combine multiple conditions in a WHERE clause. This operator plays a crucial role in querying databases. In SQL, mostly AND operator is used with WHERE and JOIN. You can use more than one AND operator in single SQL query based on your business requirement.

Syntax

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

AND operator is a fundamental logical operator.
AND Operator is used to combine multiple conditions.
AND operator is used to specify multiple criteria to narrow down the result set.
Use parentheses to group conditions for clarity and to control the order of operations .
AND operator evaluates conditions from left to right.
Try to avoid unnecessary AND operators to maintain query efficiency and readability.

Demo Database

we have 2 tables Users (Id, Name , Mobile ,Weight, DOB) and UserAddress (Id, UserId, AddressType, Address, Country, City and PostalCode)

CREATE TABLE Users (
    Id       INT           PRIMARY KEY,
    Name     VARCHAR(100)  NOT NULL,
    Mobile   VARCHAR(20),
    Weight   DECIMAL(9,2),
    DOB      DATE
);

CREATE TABLE UserAddress (
    Id         INT           PRIMARY KEY,
    UserId     INT           NOT NULL,
    AddressType VARCHAR(50)  NOT NULL,
    Address    VARCHAR(255)  NOT NULL,
    Country    VARCHAR(100)  NOT NULL,
    City       VARCHAR(100)  NOT NULL,
    PostalCode VARCHAR(20)   NOT NULL,
    FOREIGN KEY (UserId) REFERENCES Users(Id)
);

AND vs OR

The AND operator displays a record if all the conditions are TRUE.

The OR operator displays a record if any of the conditions are TRUE.

Behavioural Details - Deeper Dive

1. Handling NULL and three‐valued logic

Because SQL supports three logical values TRUE, FALSE, UNKNOWN (when NULL is involved) the AND operator’s behavior in presence of NULL deserves close attention..

expression1	expression2	result of `expression1 AND expression2`
TRUE	TRUE	TRUE
TRUE	FALSE	FALSE
TRUE	NULL	NULL (i.e. UNKNOWN , treated as not TRUE -> excluded)
FALSE	NULL	FALSE
NULL	NULL	NULL (UNKNOWN)

Thus if one condition yields UNKNOWN (via NULL), the whole AND chain does not yield TRUE so the row is excluded.

Best Practice

If a column can be NULL and you expect the condition to matter (i.e. you care whether the value is present or not), then you should explicitly handle it. For example:

Use IS NOT NULL or IS NULL to ensure you cover the NULL case.
Use COALESCE(column, defaultValue) to convert NULL to a safe default before applying comparisons.

Example

WHERE someColumn = 'value'  
  AND anotherColumn IS NOT NULL  
  AND anotherColumn > 100

WHERE someColumn = 'value'  
  AND COALESCE(anotherColumn, 0) > 100

By doing so, you ensure the logic behaves as you intend. you don’t accidentally filter out or include rows because of unintended UNKNOWN results.

2. Operator precedence & combining with OR and NOT

AND has higher precedence than OR. That means x = 1 OR y = 2 AND z = 3 is interpreted as x = 1 OR (y = 2 AND z = 3) unless parentheses override.
Always use parentheses when mixing AND, OR and NOT to control logic order and improve readability. Example:
```
WHERE (ConditionA = 'X' AND ConditionB = 'Y')
  OR (ConditionC = 'Z' AND ConditionD = 'W');
```
Unclear grouping can lead to unintended results so for maintainability, prefer clear grouping over implicit precedence.

3. Short‐circuiting & performance behaviour

Many database engines optimize AND by evaluating left‐to‐right and stop evaluation once a condition is found false (because false AND anything = false). This means you can place more selective or inexpensive conditions earlier to possibly avoid evaluating expensive ones. Be mindful if conditions include heavy expressions (functions, subqueries) or divisions by zero etc.

Example

SELECT *
FROM Orders
WHERE IsActive = 1               -- inexpensive, selective check
  AND TotalAmount / Discount > 100;  -- expensive expression (division, maybe large computation)

In this case, if IsActive = 1 is false, the engine may skip evaluating the second condition entirely.

Key tip : Be mindful when you have conditions involving costly operations (functions, subqueries, calculations) that they follow simpler checks. That way you reduce unnecessary work and potential performance impact.

AND with WHERE clause

AND is a fundamental logical operator which is mostly used with WHERE and JOIN in SQL.

SQL query to fetch users whose age is greater than 30 and weight is less than 60.

SELECT Id, Name, Weight, DOB
FROM Users  
WHERE TIMESTAMPDIFF(YEAR, DOB, CURDATE()) > 30 
      AND Weight < 60;

Multiple AND Operators

You can specify multiple AND operations in single SQL query to fetch relevant data based on your business requirement. if more than one AND conditions are present then row that satisfy all of the specified conditions will be displayed in the result set

SQL query to fetch permanent Indian users whose age is greater than 30 and has less than 60 weight.

SELECT u.Id, u.Name, u.Weight, u.DOB, ua.AddressType, ua.Country
FROM Users u
JOIN UserAddress ua on ua.UserId = u.Id
WHERE TIMESTAMPDIFF(YEAR, u.DOB, CURDATE()) > 30 
      AND u.Weight < 60 AND ua.AddressType = "Permanent"
      AND ua.Country = "india";

AND with Combining other Operators

AND operator is a fundamental logical operator. You can use AND with other operators. make sure to use parentheses to group conditions for clarity and to control the order of operation (AND operator evaluates conditions from left to right) when using AND with other operators it will be efficient and readable.

SQL query to fetch users whose age is greater than 30 and has less than 60 weight OR a permanent Indian User OR Name start with `ra`.

SELECT u.Id, u.Name, u.Weight, u.DOB, ua.AddressType, ua.Country
FROM Users u
left JOIN UserAddress ua on ua.UserId = u.Id
WHERE TIMESTAMPDIFF(YEAR, u.DOB, CURDATE()) > 30 
      AND (u.Weight < 60 OR (ua.AddressType = "Permanent"
      AND ua.Country = "india") OR u.Name like "ra%");

Performance & Pitfalls

Performance considerations

Composite filters joined by AND across multiple columns can still lead to full table scans unless indexes exist.
If you use AND on columns that have poor selectivity (many duplicates) you may not get benefit of index.
Using functions on channels inside AND often prevents index usage transform to avoid.
When distributing logic across multiple ANDs, check execution plan to ensure filter pushes down and is selective early.

Common pitfalls

NULLs causing exclusions
Using comparison operators like = or > when a column can be NULL can lead to unintended exclusion of rows. Comparisons with NULL result in UNKNOWN, not TRUE so those rows are omitted.
Example
```
SELECT * 
FROM Orders
WHERE CustomerId = 123
  AND Discount > 0;
```
If Discount is NULL for some rows, Discount > 0 becomes UNKNOWN and those rows are not returned even though you might have expected them to be included.
Fix: Explicitly handle the NULL like
```
... AND Discount > 0
  AND Discount IS NOT NULL
```
or use COALESCE(Discount, 0) > 0.
Mis‐grouped logic with OR : When mixing AND and OR without parentheses, operator precedence may lead to unintended logic. In SQL, AND has higher precedence than OR. Example
```
SELECT * 
FROM Products
WHERE Category = 'Electronics'
  AND Price < 100
  OR Price > 1000;
```
This is interpreted as
```
(Category = 'Electronics' AND Price < 100)
  OR Price > 1000
```
So it will include products priced > 1000 regardless of category. Fix: Use parentheses to group conditions as intended
```
SELECT * 
FROM Products
WHERE Category = 'Electronics'
  AND (Price < 100 OR Price > 1000);
```
Overly complex AND chains
Having too many AND conditions in a query can make it difficult to read, understand and maintain. If those conditions change frequently, it’s a good idea to refactor the query, by moving the logic into a view, CTE or separate function for better clarity and flexibility.
Example
```
SELECT * 
FROM Users
WHERE IsActive = 1
  AND Country = 'USA'
  AND SignupDate > '2023-01-01'
  AND LastLoginDate > '2023-05-01'
  AND SubscriptionType = 'Premium'
  AND EmailVerified = 1;
```
Over the time more conditions may be added, making it harder to maintain and prone to errors. Fix: Consider extracting logic into a view or auxiliary query:
```
CREATE VIEW ActivePremiumUsers AS
SELECT * FROM Users
WHERE IsActive = 1
  AND SubscriptionType = 'Premium'
  AND EmailVerified = 1;

SELECT * FROM ActivePremiumUsers
WHERE Country = 'USA'
  AND SignupDate > '2023-01-01'
  AND LastLoginDate > '2023-05-01';
```
Blind use of functions (non-sargable predicates)
Wrapping columns inside functions like YEAR(DOB) = 2024, makes the query non-sargable, meaning the database can’t use indexes efficiently. This leads to slower performance because the function must be applied to every row before filtering.
Fix: Rewrite the condition to keep the column “raw” and make use of indexes.
Example
```
SELECT *
FROM Employees
WHERE YEAR(DOB) = 1990;
```
Here using YEAR(DOB) means the database can’t use an index on DOB effectively. Fix: Rewrite to a range query
```
SELECT *
FROM Employees
WHERE DOB BETWEEN '1990-01-01' AND '1990-12-31';
```
Not updating logic after schema changes
When the table structure changes (new column added, datatype changed, semantics altered) but your WHERE clause remains unchanged, you risk wrong or unexpected results.
Example
Suppose Users table gets a new column IsDeleted (flag for soft-delete) but queries still use WHERE IsActive = 1. You will include “deleted” users because you didn’t add AND IsDeleted = 0. Fix: Whenever schema changes, review and update all filters/conditions that may be affected.

Summary

The AND operator is a fundamental logical tool in SQL for enforcing that all specified conditions must hold true for a row to be included. When used thoughtfully and in conjunction with correct handling of NULL, operator precedence, indexing and business logic. it helps you craft precise, maintainable, high‐performing queries. By mastering AND (and how it interacts with OR, NOT, joins, subqueries, functions and indexing) you’ll be well equipped to handle real-world data filtering and manipulation tasks.