PostgreSQL AND & OR Condition

In this section, we are going to understand the working of PostgreSQL AND & OR Condition, which provides the advantages of AND & OR Condition both in just a single command. Here, we will also see the examples of AND & OR operator with different statements, such as INSERT, SELECT, UPDATE, and DELETE.

Introduction of PostgreSQL AND & OR Condition

In PostgreSQL, the AND & OR Condition can be combined with the INSERT, SELECT, UPDATE and DELETE commands. While combining these conditions, we must be alert of where to use the parentheses so that the database knows the order to assess every condition.

The PostgreSQL AND & OR conditions allow us to test several conditions. And we never forget the order of operation round brackets.

PostgreSQL AND & OR Condition Syntax

The syntax of the PostgreSQL AND & OR condition are as follows:

WHERE condition1  
AND condition2  
...  
OR condition_n;  

In the above syntax, we have used the following parameters:

Examples of PostgreSQL AND & OR Condition

Let us see different examples to understand how the PostgreSQL AND & OR Condition works.

Example of PostgreSQL AND & OR with INSERT Command

In the below example, we will display how to use the AND & OR condition with the INSERT command.

For this, we are taking the employee and department table from the Organization database to insert the records from one table to another table.

INSERT INTO department
(phone, address)
SELECT phone, address
FROM employee 
WHERE (employee_name = 'Nia Davis' OR employee_name = 'Kat Taylor')
AND emp_id <5;

Output

After executing the above command, we will get the below message window displaying that the value has been inserted successfully into the department table.

To check whether the records have been inserted into the department table or not, we need to use the SELECT command as follows:

Select * from department;

Output

As we can see in the below screenshot, the PostgreSQL AND & OR condition inserted the records into the department table.

All phone and address records from the employee table whose employee_name is either 'Nia Davis OR Kat Taylor' AND the emp_id is less than 5.

Example of PostgreSQL AND & OR with SELECT Command

In the below example, we will display how to use the AND & OR condition with the SELECT command.

For this, we are taking the Customer table from the TheDeveloperBlog database to all the records from the table.

We are using the AND & OR condition with WHERE clause as we can see the following command:

SELECT cust_id, cust_name, cust_address, cust_age
FROM customer 
WHERE (cust_address = 'Florida' AND cust_name = 'harvey')
OR (cust_age >=26);

Output

After executing the above command, we will get the following output:

In the above example, we can see that the AND & OR condition will return all those customers who live in Florida, AND Cust_name is 'Harvey', OR all customers whose cust_age is greater than or equal to 26.

And the parentheses () are used to specify the order that the AND & OR conditions analyzed.

Let us see one more example of SELECT command with AND & OR condition. For this, we are taking the car table from the TheDeveloperBlog database.

The following command is a more complex query as compared to the above statement:

SELECT car_id, car_name, car_model, car_price, car_color, body_style
FROM car
WHERE (body_style = 'coupe')
OR (body_style ='wagon' AND car_color = 'blue')
OR (body_style = 'sedan' AND Car_color = 'black' AND car_price = 63890);

Output

After successfully executing the above command, we will get the following output:

As we can see in the above screenshot that the AND & OR condition will return all those records from the car_id, car_name, car_model, car_price, car_color, body_style columns values from the car table whose body_style is coupe OR whose body_style is Wagon, and the car_color is blue, OR whose body_styleis sedan the car_color is black, and the car_price is 63890.

Example of PostgreSQL AND & OR with UPDATE Command

In the below example, we will display how to use the AND & OR condition with the UPDATE command.

For this, we are taking the department table from the TheDeveloperBlog database.

In the following command, the PostgreSQL AND & OR condition updates the below values:

The dept_name values to RESEARCH in the department table where either the emp_fname is 'Flora' OR dept_id is 4, and the emp_id is less than 5.

UPDATE department
SET dept_name= 'RESEARCH'
WHERE (emp_fname = 'Flora' OR dept_id = 4)
AND emp_id < 5;

Output

After executing the above command, we will get the below output, where we can see that the department table has been updated successfully.

We will now use the Select command to check whether the particular records have been updated or not in the department table:

SELECT * 
FROM department;

Output

On executing the above command, we will get the below result:

Example of PostgreSQL AND & OR with DELETE Command

In the below example, we will display how to use the AND & OR Condition with the DELETE command.

Here, we will take the customer table from TheDeveloperBlog Database, where we are deleting the particular records from the table.

In the following command, the PostgreSQL AND & OR Condition is used to delete records from the customer table where we have the following values.

The cust_address was 'Florida', and cust_name was 'Harvey' or the cust_age is greater than or equal to 26.

DELETE FROM customer
WHERE (cust_address = 'Florida' AND cust_name = 'harvey')
OR cust_age >=26;

Output

After executing the above command, we will get the below message window displaying that the records have been deleted successfully.

We will now use the Select command to check whether the particular records have been deleted or not in the Customer table:

SELECT * 
FROM customer;

Output

On executing the above command, we will get the below result:

Overview

In the PostgreSQL AND & OR Condition section, we have learned the following topics:

• The use of PostgreSQL AND & OR condition provides the benefits of AND & OR condition both in just a single command.
• We used the AND & OR Condition with INSERT Command to insert the records from one table to another.
• We used the AND & OR Condition with the SELECT Command to get the particular table records.
• We used the AND & OR Condition with the UPDATE Statement to update the particular table's records.
• We used the AND & OR Condition with the DELETE Statement to remove the particular table's records.