SQL AND Operator

Understanding the SQL AND Operator

The AND operator in SQL is used to combine multiple conditions in a WHERE clause or HAVING clause. For a row to be included in the results, all conditions joined by AND must evaluate to TRUE.

Basic Syntax

Common SQL AND Operator Interview Questions

How does the AND operator work with three or more conditions?
What is the order of operations when combining AND with other logical operators?
How does the AND operator handle NULL values?
What is the performance impact of using multiple AND conditions?

How the SQL AND Operator Works

Basic AND Usage

When you use the AND operator, every condition must be TRUE for a row to be included in the results. If any condition is FALSE or NULL, the row is excluded.

Combining Multiple AND Conditions

You can chain multiple conditions with AND to create more specific filters. All conditions must be TRUE for a row to be included.

Truth Table for AND

The AND operator follows this truth table for two conditions A and B:

A	B	A AND B
TRUE	TRUE	TRUE
TRUE	FALSE	FALSE
FALSE	TRUE	FALSE
FALSE	FALSE	FALSE
TRUE	NULL	NULL
FALSE	NULL	FALSE
NULL	NULL	NULL

Understanding SQL's Three-Valued Logic with NULLs

In SQL, NULL represents an unknown or missing value. The AND operator follows these rules when dealing with NULLs:

TRUE AND NULL = NULL - When one value is TRUE and another is NULL, the result is NULL (unknown)
FALSE AND NULL = FALSE - When one value is FALSE, the result is always FALSE regardless of NULL
NULL AND NULL = NULL - When both values are NULL, the result is NULL

Important: In WHERE clauses, only rows where the condition evaluates to TRUE are included in the result set. Rows where the condition evaluates to FALSE or NULL are both excluded.

AND with Other Logical Operators

Order of Operations: AND and OR

When combining AND and OR operators, AND has higher precedence than OR. This means that AND conditions are evaluated first, then OR conditions.

Best Practice: Always use parentheses to make your logical grouping clear, even when it follows the default precedence.

Combining AND with OR Using Parentheses

Use parentheses to explicitly control the order of evaluation and create complex logical expressions.

Combining AND with NOT

Use NOT with AND to exclude rows that match specific conditions.

Practical SQL AND Examples

Example 1: Filtering Videos by Multiple Criteria

This example shows how to find popular recent videos with specific titles:

Example 2: Advanced User Filtering

This example demonstrates how to find users who match specific criteria:

Example 3: Complex Filtering with AND and OR

This example shows how to use AND with OR and proper parentheses for complex logic:

Note: This query uses a subquery to find videos from creators with specific usernames.

Best Practices for SQL AND Operator

1. Use Parentheses for Clarity

Always use parentheses when combining AND with other logical operators like OR to make your query's logic clear and avoid unexpected results.

2. Consider Performance with Multiple Conditions

When using multiple AND conditions, order your conditions to filter out the most rows first when possible. The database may optimize this automatically, but it's good practice to understand which conditions are more selective.

3. Be Careful with NULL Values

Remember that AND with NULL follows three-valued logic. If a condition evaluates to NULL, the entire expression will be NULL if the other condition is TRUE, or FALSE if the other condition is FALSE.

This can lead to unexpected filtering behavior. When a WHERE clause filters with an AND condition:

Rows are included only when the entire condition is TRUE
Both FALSE and NULL results are excluded from the result set
To include NULL values, use specific IS NULL checks or COALESCE

4. Use Indexes Effectively

When writing queries with multiple AND conditions, consider which columns have indexes. Conditions on indexed columns can be processed more efficiently by the database.

SQL Learning Hub