In SQL Server, the AND operator is a logical operator used to combine multiple conditions within a WHERE, HAVING, or JOIN clause.
For a row to be included in the result set, every condition connected by the AND operator must evaluate to TRUE. If even a single condition evaluates to FALSE (or UNKNOWN), the entire row is filtered out.
1. Core Syntax and Usage
You can chain multiple AND conditions together to narrow down your data retrieval precisely.
Example:
SQL
SELECT ProductName, UnitPrice, UnitsInStock, CategoryID
FROM dbo.Products
WHERE UnitPrice > 50.00 -- Condition 1
AND UnitsInStock > 0 -- Condition 2
AND CategoryID = 1; -- Condition 3
In this query, a product will only appear in the results if its price is greater than 50, it is currently in stock, and it belongs to Category 1.
2. Truth Table for AND
Because SQL Server uses Three-Valued Logic (handling TRUE, FALSE, and NULL / UNKNOWN), the AND operator follows strict evaluation rules:
| Condition A | Condition B | Result of A AND B |
TRUE |
TRUE |
TRUE |
TRUE |
FALSE |
FALSE |
FALSE |
FALSE |
FALSE |
TRUE |
NULL |
NULL (Unknown) |
FALSE |
NULL |
FALSE (Because one side is false, the whole expression is false) |
Key Takeaway: Notice that
FALSE AND NULLresults inFALSE. Even though one part of the condition is unknown, the presence of an absoluteFALSEguarantees the overall statement cannot be true.
3. Operator Precedence: AND vs. OR
When you mix AND and OR operators in a single query without parentheses, AND takes precedence over OR. This means SQL Server evaluates all AND conditions as tight logical groups before evaluating OR conditions.
The Danger of Missing Parentheses:
SQL
-- What you might intend: (Active OR Pending) AND HighPriority
-- What SQL Server actually executes: Active OR (Pending AND HighPriority)
SELECT * FROM Tasks
WHERE Status = 'Active'
OR Status = 'Pending'
AND Priority = 'High';
If you want to ensure the OR conditions are evaluated together before checking the AND condition, you must use parentheses:
SQL
-- Correct Implementation
SELECT * FROM Tasks
WHERE (Status = 'Active' OR Status = 'Pending')
AND Priority = 'High';
4. Evaluation Order and “Short-Circuiting”
In procedural programming languages (like C#, Java, or JavaScript), logical operators often “short-circuit”—meaning if the first condition is false, the second condition isn’t even evaluated.
SQL Server does not work this way.
-
The Query Optimizer analyzes your query and decides the order of execution based on cost, indexes, and statistics, not the left-to-right order you wrote them in.
-
Warning: Never write a query assuming
ANDwill protect you from runtime errors like division by zero. For example:SQL
-- Dangerous: SQL Server might evaluate the division before checking if the denominator is zero SELECT * FROM Metrics WHERE Denominator <> 0 AND (Numerator / Denominator) > 5;(To safely handle division by zero in SQL Server, use a
CASEstatement or functions likeNULLIFinstead).
5. Best Practices
-
Always Use Parentheses for Mixed Logic: Never mix
ANDandORin the same query block without explicit parentheses. It prevents severe logic bugs and makes your code self-documenting for other developers. -
Order by Selectivity (Readability/Maintenance): While the optimizer reorders things anyway, placing the most restrictive or fastest-filtering conditions first in your
ANDchain makes the query easier to read and troubleshoot. -
Index Optimization: If you frequently query multiple columns using
AND(e.g.,WHERE CustomerID = 5 AND OrderDate >= '2024-01-01'), consider building a Composite Non-Clustered Index that includes both columns to maximize performance.