Introduction to the PostgreSQL AND operator

In PostgreSQL, aboolean value can have one of three values:true,false, andnull.

PostgreSQL usestrue,'t','true','y','yes','1' to representtrue andfalse,'f','false','n','no', and'0' to representfalse.

A boolean expression is an expression that evaluates to a boolean value. For example, the expression1=1 is a boolean expression that evaluates totrue:

SELECT 1 = 1 AS result;

Output:

result-------- t(1 row)

The lettert in the output indicates the value oftrue.

TheAND operator is a logical operator that combines two boolean expressions.

Here’s the basic syntax of theAND operator:

expression1AND expression2

In this syntax,expression1 andexpression2 are boolean expressions that evaluate totrue,false, ornull.

TheAND operator returnstrue only if both expressions aretrue. It returnsfalse if one of the expressions isfalse. Otherwise, it returnsnull.

The following table shows the results of theAND operator when combiningtrue,false, andnull. Note that the order of the expressions doesn't matter, for example bothtrue AND null andnull AND true will evaluate tonull.

In practice, you often use theAND operator in aWHERE clause to ensure that all specified expressions must be true for a row to be included in the result set.

PostgreSQL AND operator

Let’s explore some examples of using theAND operator.

1) Basic PostgreSQL AND operator examples

The following example uses theAND operator to combine two true values, which returns true:

SELECT trueAND trueAS result;

Output:

result-------- t(1 row)

The following statement uses theAND operator to combine true with false, which returns false:

SELECT trueAND falseAS result;

Output:

result-------- f(1 row)

The following example uses theAND operator to combine true with null, which returns null:

SELECT trueAND null AS result;

Output:

result-------- null(1 row)

The following example uses theAND operator to combine false with false, which returns false:

SELECT falseAND falseAS result;

Output:

result-------- f(1 row)

The following example uses theAND operator to combine false with null, which returns false:

SELECT falseAND null AS result;

Output:

result-------- f(1 row)

The following example uses theAND operator to combine null with null, which returns null:

SELECT null AND null AS result;

Output:

result-------- null(1 row)

2) Using the AND operator in the WHERE clause

We’ll use thefilm table from thesample database for the demonstration:

The following example uses theAND operator in theWHERE clause to find the films that have a length greater than 180 and a rental rate less than 1:

SELECT  title,  length,  rental_rateFROM  filmWHERE  length > 180  AND rental_rate < 1;

Output:

title        | length | rental_rate--------------------+--------+------------- Catch Amistad      |    183 |        0.99 Haunting Pianist   |    181 |        0.99 Intrigue Worst     |    181 |        0.99 Love Suicides      |    181 |        0.99 Runaway Tenenbaums |    181 |        0.99 Smoochy Control    |    184 |        0.99 Sorority Queen     |    184 |        0.99 Theory Mermaid     |    184 |        0.99 Wild Apollo        |    181 |        0.99 Young Language     |    183 |        0.99(10 rows)

Summary

• Use theAND operator to combine multiple boolean expressions.

Last updated onFebruary 12, 2024

On this page

• Introduction to the PostgreSQL AND operator
• PostgreSQL AND operator
• Summary