PostgreSQL AND Condition

In this section, we are going to understand the working of PostgreSQL AND Condition, which is used with the WHERE clause to select unique data from more than one column in a table.

And we also see examples of AND Condition with different queries such as INSERT, SELECT, UPDATE, and DELETE.

Introduction of PostgreSQL AND Condition

In PostgreSQL, the AND condition can combine with the SELECT, INSERT, UPDATE, and DELETE commands. In other words, we can say that the AND condition is used to specify the data if all the conditions separated by AND are TRUE.

NOTE: The PostgreSQL AND condition allows us to return two or more conditions. And the PostgreSQL AND condition require any of the conditions that could be condition1, condition2, ...conditionN must be occurred in the data and involved in the output.

PostgreSQL AND Condition Syntax

The syntax of the PostgreSQL AND condition is as follows:

WHERE condition1  
AND condition2  
...  
AND condition_n;  

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

Examples of PostgreSQL AND Condition

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

Example of PostgreSQL AND condition with INSERT Command

We will display how to use the AND 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.

In the following example, we are inserting the record into the employee table from the department table. We take the phone and address columns records from the department table, the dept_id is less than 5, AND department_name is SALES.

INSERT INTO employee
(phone, address)
SELECT phone, address
FROM department
WHERE dept_id < 5
AND department_name = 'SALES';

Output

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

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

Select * from employee;

Output

As we can see in the below screenshot that the PostgreSQL AND condition inserted one records into the employee table.

Example of PostgreSQL AND condition with SELECT Command

In the below example, we will display using the AND condition with the SELECT command having two conditions.

For this, we are taking the Client table from the TheDeveloperBlog database to get the records from the table.

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

SELECT client_name, client_profession, client_qualification, client_salary
FROM client
WHERE client_qualification = 'MBA'
AND client_salary <= 1000000;

Output

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

As we can see in the above screenshot, the PostgreSQL AND condition will return those clients information whose client_qualification is 'MBA' and have a client_salary less than or equal to 1000000.

Example of PostgreSQL AND condition to Join two or more tables

To Join various tables using PostgreSQL AND condition with SELECT command, we take the client and client_details tables from the TheDeveloperBlog database.

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

SELECT client.client_id, client.client_name, 
client.client_qualification,client_details.mobile_number
FROM client, client_details
WHERE client.client_id = client_details.client_id
AND client.client_qualification= 'MBA';

Output

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

As we can see that the above command is working fine now; if we want to do it more precisely, we can write the above statement with the help of the PostgreSQL INNER Join condition.

SELECT client.client_id, client.client_name, 
client.client_qualification,client_details.mobile_number
FROM client
INNER JOIN client_details
ON client.client_id = client_details.client_id
WHERE client.client_qualification= 'MBA';

Output

On implementing the above command, we will get the following result:

As we can see in the above screenshot, the PostgreSQL AND condition will return those rows whose client_qualification is MBA. And the Client and Client_details tables are joined on client_id.

Note:

• All of the columns are started with the table names like client.client_id.
• It is necessary to remove any uncertainty as to which column is being referenced because sometimes, we have a similar column name in both tables.
• Like in the above, the Client and the Client_details tables have a similar column as client_id.
• In such a case, the output will only be displaying the client_id, client_name, client_qualification, and mobile_number columns, which are mentioned in the SELECT command.

Example of PostgreSQL AND Condition with UPDATE Command

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

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

In the following command, the PostgreSQL AND condition is used to update the following values:

The summer_fruits_name values to Strawberry, in the summer_fruits table where fruit_id is equal to 4 AND the summer_fruits_name is Guava.

UPDATE summer_fruits
SET summer_fruits_name = 'Strawberry'
WHERE fruit_id = 4
AND summer_fruits_name= 'Guava';

Output

After executing the above command, we will get the below output, where we can see that the summer_fruits 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 summer_fruits table:

SELECT * FROM summer_fruits;

Output

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

Example of PostgreSQL AND condition with DELETE Command

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

In the below example, we take the Customer table from the Organization Database, deleting the particular records from the table.

In the following command, the PostgreSQL AND Condition is used to delete all the records from the customer table where the first_name is 'Jane', and last_name is 'Miller'.

DELETE FROM customer
WHERE first_name = 'Jane' 
AND last_name ='Miller';

Output

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

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

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

Overview

• The use of PostgreSQL AND condition provides the advantages of AND condition in just a single command.
• We used the AND Condition with INSERT Command to insert the records from one table to another.
• We used the AND Condition with the SELECT Command to get the particular table records.
• We used the AND Condition with the SELECT Command to join two or more tables.
• We used the AND Condition with the UPDATE Statement to update the particular table's records.
• We used the AND Condition with the DELETE Statement to remove the particular table's records.