AND Operator in SQL
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)
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.
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%");