SQL

AND in SQL

🔗 The AND operator in SQL is a logical operator used to combine two or more conditions in a query. It returns records only when all specified conditions are true. The AND operator is commonly used with the WHERE, HAVING, and JOIN clauses to retrieve more precise results.

📖 What is the AND Operator?

The AND operator evaluates multiple conditions together. A row is included in the result set only if every condition connected by AND evaluates to TRUE. If even one condition is false, the row is excluded.

Information

Think of the AND operator as requiring allconditions to be satisfied before a record is returned.

📝 Basic Syntax

General Syntax

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

📊 Sample Table

Consider the following Students table:

StudentID	Name	Department	Marks	City
101	Alice	Computer Science	92	Chennai
102	Bob	Mathematics	85	Coimbatore
103	Charlie	Computer Science	78	Madurai
104	David	Computer Science	95	Chennai
105	Eva	Physics	88	Chennai

🎯 Using AND with Two Conditions

Retrieve students who belong to the Computer Science department and have marks greater than 90.

AND Example

SELECT *
FROM Students
WHERE Department = 'Computer Science'
AND Marks > 90;

Only students who satisfy both conditions are returned.

📌 Using AND with Multiple Conditions

The AND operator can connect more than two conditions.

Multiple AND Conditions

SELECT *
FROM Students
WHERE Department = 'Computer Science'
AND Marks >= 90
AND City = 'Chennai';

This query returns only students who:

✅ Belong to the Computer Science department.
✅ Have marks greater than or equal to 90.
✅ Live in Chennai.

⚖️ AND with Comparison Operators

The AND operator works with all SQL comparison operators.

Operator	Example
=	Department = 'Physics'
>	Marks > 80
<	Age < 25
>=	Salary >= 50000
<=	Price <= 1000
<>	City <> 'Delhi'

🔍 AND with BETWEEN

Combine AND with BETWEEN to apply additional filters.

BETWEEN with AND

SELECT *
FROM Students
WHERE Marks BETWEEN 80 AND 95
AND Department = 'Computer Science';

📝 AND with LIKE

You can combine pattern matching with other conditions.

LIKE with AND

SELECT *
FROM Students
WHERE Name LIKE 'A%'
AND Department = 'Computer Science';

📊 AND with ORDER BY

After filtering the data using AND, the results can be sorted using ORDER BY.

Filter and Sort

SELECT Name,
       Marks
FROM Students
WHERE Department = 'Computer Science'
AND Marks >= 80
ORDER BY Marks DESC;

🧮 AND with Aggregate Functions

The AND operator is also useful in the HAVING clause for filtering grouped data.

HAVING with AND

SELECT Department,
       COUNT(*) AS TotalStudents,
       AVG(Marks) AS AverageMarks
FROM Students
GROUP BY Department
HAVING COUNT(*) > 1
AND AVG(Marks) >= 85;

⚠️ AND vs OR

Feature	AND	OR
Condition Requirement	All conditions must be true.	At least one condition must be true.
Result Set	More restrictive.	Less restrictive.
Typical Usage	Apply multiple filters.	Match alternative conditions.

💼 Real-World Example

A university administrator wants to find students from the Computer Science department who scored at least 90 marks and live in Chennai.

Real-World Query

SELECT StudentID,
       Name,
       Marks,
       City
FROM Students
WHERE Department = 'Computer Science'
AND Marks >= 90
AND City = 'Chennai'
ORDER BY Marks DESC;

Only students meeting all three conditions appear in the result.

⚠️ Best Practices

Best Practice

Use the AND operator when every condition must be satisfied. Format each condition on a separate line for better readability, use parentheses when combining AND and OR in the same query, and filter using indexed columns whenever possible to improve query performance.

🚀 Key Points to Remember

📌 AND combines two or more conditions.
📌 Every condition connected by AND must evaluate to TRUE.
📌 It is commonly used with WHERE and HAVING.
📌 It can be combined with operators such as BETWEEN, LIKE, and comparison operators.
📌 Use parentheses when mixing AND with OR to avoid ambiguity.

>>"The AND operator helps narrow your search by ensuring every specified condition is satisfied."

Summary

✅ The AND operator is a fundamental SQL logical operator that retrieves records only when all specified conditions are true. Mastering AND enables you to build precise, efficient, and reliable SQL queries for filtering and analyzing data.