PostgreSQL offers three logical operators, i.e., AND, NOT, and OR. Among them, the most frequently used operators are AND and OR operators. Both these operators are used to combine different conditions hence also referred to as conjunctive operators.

These operators are also known as Boolean operators. The AND and OR operators test the specified condition and return a TRUE, FALSE, or NULL value. In this post, you will learn the working of each operator with the help of suitable examples. So, let’s get started!

How to Use AND Operator in PostgreSQL?

In PostgreSQL, the WHERE clause is used to specify a condition in a statement. The AND operator allows us to specify/combine multiple conditions.

Let’s consider the below snippet for a profound understanding of the Postgres AND operator:

SELECT col_1, col_2, ..., col_N
FROM tab_name
WHERE [condition_1] AND [condition_2]... AND [condition_N];

Let’s analyze the working of the above snippet:

● SELECT is a statement used to fetch the data of single or multiple tables.

● col_1, col_2, …, col_N are the columns whose data will be fetched using the SELECT statement.

● tab_name is the targeted table, and col_1, col_2, …, col_N belong to the tab_name table.

● WHERE is a clause that takes a condition or group of conditions based on which the retrieved data will be filtered.

● AND is a logical operator that combines multiple conditions.

How Does AND Operator Work in PostgreSQL?

The below-listed points will let you understand the working of the AND operator in PostgreSQL:

● AND operator returns true only if all conditions are true.

● If any of the conditions didn't match the criteria, then the AND operator will return false.

● It returns a NULL value if all the conditions return NULL.

Example: How to Combine Multiple Conditions in PostgreSQL using AND Operator?

We have already created a table named “student_details”. Let’s execute the SELECT statement to see the table’s record:

SELECT * FROM student_details;

The output shows that there are ten students; six males and four females. Some students are under twenty years while others are above twenty years.

Suppose we need to fetch only those male students whose age is above 20. To do so, we will utilize the AND operator with the SELECTcommand:

SELECT * FROM student_details
WHERE student_age => 20 AND student_gender = 'M';

Let’s analyze how the above query works:

● The SELECT statement will fetch all the records from the student_details table.

● The WHERE clause will filter records based on the specified conditions.

● The AND operator will return the filtered data if both conditions are true. This means the student must be male and at least 20 years old.

The output clarifies that the result-set shows only those students who satisfied both conditions.

How to Use OR Operator in PostgreSQL?

The OR operator is used in the WHERE clause to combine multiple conditions. For the OR operator, if one or more conditions are true, the whole condition will be considered true.

The below-given syntax will explain the working of the Postgres OR operator in a better way:

SELECT col_1, col_2, ..., col_N
FROM tab_name
WHERE [condition_1] OR [condition_2]... OR [condition_N];

Let’s analyze the working of the above snippet:

● SELECT statement fetches the data of single or multiple tables.

● col_1, col_2, …, col_N are the columns whose data will be fetched using the SELECT statement.

● tab_name represents a table whose record will be fetched using the SELECT statement.

● WHERE is a clause that takes a condition or group of conditions based on which the retrieved data will be filtered.

● OR is a logical operator that combines multiple conditions.

How Does OR Operator Work in PostgreSQL?

Consider the following points to understand the working of the OR operator in PostgreSQL:

● OR operator returns true if one or more conditions are true.

● It returns false if all conditions don’t match the specified criteria.

Example: How to Combine Multiple Conditions in PostgreSQL using OR Operator?

Let’s consider the below-given query to understand how the OR operator works in PostgreSQL:

SELECT * FROM student_details
WHERE student_age < 20 OR student_gender = 'F';

● The SELECT statement will fetch all the records from the student_details table.

● The WHERE clause will filter records based on the specified conditions.

● The OR operator will return the filtered data if at least one condition satisfies the criteria.

The output shows that the OR operator includes all those records that fulfill at least one condition. This is how the OR operator works in PostgreSQL.

Conclusion

In PostgreSQL, the AND and OR operators combine more than one condition in the WHERE clause. The AND operator returns true if all the conditions satisfy the given criteria, while the OR operator returns true if at least one condition satisfies the specified criteria. This write-up explained the working of Postgres AND and OR operators with the help of examples.