PostgreSQL: AND Condition

This PostgreSQL tutorial explains how to use the PostgreSQL AND condition with syntax and examples.

Description

The PostgreSQL AND condition (also called the AND Operator) is used to test two or more conditions in a SELECT, INSERT, UPDATE, or DELETE statement.

Syntax

The syntax for the AND condition in PostgreSQL is:

WHERE condition1
AND condition2
...
AND condition_n;

Parameters or Arguments

condition1, condition2, condition_n are all of the conditions that must be met for the records to be selected.

Note

• The PostgreSQL AND condition allows you to test 2 or more conditions.
• The PostgreSQL AND condition requires that all of the conditions (ie: condition1, condition2, condition_n) be must be met for the record to be included in the result set.

Example - With SELECT Statement

Let's look at some examples that show how to use the AND condition in PostgreSQL.

The first PostgreSQL AND condition query involves a SELECT statement with 2 conditions.

For example:

SELECT *
FROM products
WHERE product_type = 'Clothing'
AND product_id <= 2000;

This PostgreSQL AND example would return all products that have a product_type of 'Clothing' and have a product_id less than or equal to 2000. Because the * is used in the SELECT statement, all fields from the products table would appear in the result set.

Example - JOINING Tables

Our next PostgreSQL AND example shows how the AND condition can be used to join multiple tables in a SELECT statement.

For example:

SELECT products.product_id, products.product_name, inventory.quantity
FROM products, inventory
WHERE products.product_id = inventory.product_id
AND products.product_type = 'Hardware';

Though the above SQL works just fine, you would more traditionally write this SQL as follows using a proper INNER JOIN.

For example:

SELECT products.product_id, products.product_name, inventory.quantity
FROM products
INNER JOIN inventory
ON products.product_id = inventory.product_id
WHERE products.product_type = 'Hardware';

This PostgreSQL AND condition example would return all rows where the product_type is 'Hardware'. And the products and inventory tables are joined on product_id. You will notice that all of the fields are prefixed with the table names (ie: products.product_id). This is required to eliminate any ambiguity as to which field is being referenced; as the same field name can exist in both the products and the inventory tables.

In this case, the result set would only display the product_id, product_name, and quantity fields (as listed in the first part of the SELECT statement.).

Example - With INSERT Statement

This next PostgreSQL AND example demonstrates how the AND condition can be used in the INSERT statement.

For example:

INSERT INTO contacts
(last_name, first_name)
SELECT last_name, first_name
FROM customers
WHERE customer_id > 3000
AND last_name = 'Johnson';

This PostgreSQL AND condition example would insert into the contacts table, all last_name and first_name records from the customers table whose customer_id is greater than 3000 and the last_name is 'Johnson'.

Example - With UPDATE Statement

This PostgreSQL AND condition example shows how the AND condition can be used in the UPDATE statement.

For example:

UPDATE contacts
SET last_name = 'Smith'
WHERE contact_id > 500
AND first_name = 'Jane';

This PostgreSQL AND condition example would update all last_name values in the contacts table to 'Smith' where the contact_id is greater than 500 and first_name is 'Jane'.

Example - With DELETE Statement

Finally, this last PostgreSQL AND example demonstrates how the AND condition can be used in the DELETE statement.

For example:

DELETE FROM contacts
WHERE last_name = 'Smith'
AND first_name = 'Jane';

This PostgreSQL AND condition example would delete all records from the contacts table whose last_name is 'Smith' and first_name is 'Jane'.