SQL Server AND Operator

Summary: in this tutorial, you will learn how to use the SQL Server AND operator to combine multiple Boolean expressions.

Introduction to SQL Server AND operator

The AND is a logical operator that allows you to combine two Boolean expressions. It returns TRUE only when both expressions evaluate to TRUE.

The following illustrates the syntax of the AND operator:

boolean_expression AND boolean_expression Code language: SQL (Structured Query Language) (sql)

The boolean_expression is any valid Boolean expression that evaluates to TRUE, FALSE, and UNKNOWN.

The following table shows the result when you combine TRUE, FALSE, and UNKNOWN values using the AND operator:

TRUEFALSEUNKNOWN
TRUETRUEFALSEUNKNOWN
FALSEFALSEFALSEFALSE
UNKNOWNUNKNOWNFALSEUNKNOWN

When you use more than one logical operator in an expression, SQL Server always evaluates the AND operators first. However, you can change the order of evaluation by using parentheses ().

SQL Server AND operator examples

We’ll use the following products table from the sample database:

1) Basic SQL Server AND operator example

The following example uses the AND operator to find the products where the category identification number is one and the list price is greater than 400:

SELECT 
  * 
FROM 
  production.products 
WHERE 
  category_id = 1 
  AND list_price > 400 
ORDER BY 
  list_price DESC;Code language: SQL (Structured Query Language) (sql)

Here is the result:

SQL Server AND operator example

2) Using multiple SQL Server AND operators

The following statement finds the products that meet all the following conditions: category id is 1, the list price is greater than 400, and the brand id is 1:

SELECT 
  * 
FROM 
  production.products 
WHERE 
  category_id = 1 
  AND list_price > 400 
  AND brand_id = 1 
ORDER BY 
  list_price DESC;Code language: SQL (Structured Query Language) (sql)

The result is as follows:

SQL Server AND multiple operators example

3) Using the AND operator with other logical operators

The following example shows how to use the AND operator with the OR operator:

SELECT
    *
FROM
    production.products
WHERE
    brand_id = 1
OR brand_id = 2
AND list_price > 1000
ORDER BY
    brand_id DESC;Code language: SQL (Structured Query Language) (sql)

The following shows the result:

SQL Server AND and OR operators example

In this example, we used both OR and AND operators in the condition. As always, SQL Server evaluates the AND operator first. Therefore, the query retrieves the products with the brand id 2 and list price greater than 1,000 or brand id 1.

To retrieve the product with brand id 1 or 2 and list price larger than 1,000, you can use parentheses as follows:

SELECT
    *
FROM
    production.products
WHERE
    (brand_id = 1 OR brand_id = 2)
AND list_price > 1000
ORDER BY
    brand_id;
Code language: SQL (Structured Query Language) (sql)

The query returned the following result:

SQL Server AND and OR operators with parentheses

Summary

  • Use the SQL Server AND operator to combine two Boolean expressions.
  • The AND operator returns TRUE only if both expressions are TRUE.
Was this tutorial helpful?