In this section, we are going to understand the working of PostgreSQL EXISTS Condition, which is used with the WHERE clause to evaluate the existing rows in a subquery. And we also see examples of EXISTS Condition with different queries such as INSERT, SELECT, NOT EXISTS, NULL, UPDATE, and DELETE.
Introduction of PostgreSQL EXISTS Condition
In PostgreSQL, the EXISTS condition can combine with the SELECT, INSERT, UPDATE, and DELETE commands. In other words, we can say that the EXISTS condition is used to check for the presence of any data in a subquery, and returns true if the subquery returns several records.
It is used to group with a subquery and test the existence of records in a subquery. The PostgreSQL EXISTS condition is a type of Boolean operator which returns the true or false result. In other words, we can say that:
- If a subquery returns any record, the Exists condition will return a TRUE value, or else, it will return a FALSE
- Here the TRUE value is always illustrated with numeric value 1, and the FALSE value is denoted as numeric value 0.
PostgreSQL EXISTS Condition Syntax
The syntax of the PostgreSQL EXISTS condition is as follows:
WHERE EXISTS (subquery); The Exists condition takes an argument that is known as a Subquery. It is frequently used with the related subquery.
The EXISTS condition’s output rely on whether any row fetched by the subquery, and not on the row information. Hence, the columns which occur on the SELECT command of the subquery are not significant.
In such cases, we will use the following syntax for the EXISTS condition:
SELECT column1, column2…columnN
FROM table1
WHERE [NOT] EXISTS (
SELECT 1
FROM table2
WHERE column2= table1.column1
);In the above syntax, we also used the NOT operator who helps us to contradicts the EXISTS condition. And it will return TRUE when the subquery does not return any row or else it will return FALSE.
In the above syntax, we have used the following parameter:
| Parameter | Description |
|---|---|
| Column1, Cloumn2…columnN | It is used to specify the column name which used in the particular table. |
| table_name | This parameter is used to define the table names from which we are going to perform the EXISTS condition. |
| Conditions | The condition parameter is used to search the specific value from the table. |
| Subquery | Generally, the SELECT command starts with the SELECT *, but PostgreSQL ignores it in a subquery. |
Note:
- Usually, the SELECT command begins with SELECT * rather than a column name or list of expressions. Here, we are using the SELECT 1in its place of SELECT *, which helps us to enhance the query’s performance in PostgreSQL.
- Meanwhile, the subquery column result is not appropriate because here, only the rows returned matters.
- It will give the same output as PostgreSQL ignores the select list in the Subquery.
- The EXISTS condition will stop directly for additional processing after the matching output is identified.
- In PostgreSQL, the SQL commands we will use in the EXISTS condition are very ineffective. Therefore, in advance, the sub-query is RE-RUN for each row in the outer query’s table.
- And we have more effective ways to write all the commands, which do not use the EXISTS condition.
Examples of PostgreSQL EXISTS Condition
Let us see different examples to understand how the PostgreSQL EXISTS Condition works.
Firstly, we will see one sample example of EXISTS condition to learn the working EXISTS operator in the PostgreSQL.
For this, we are taking the Client and Client_details tables from the Javatpoint database.
In the below example, we identify those Clients’ information with at least one record from the client_details table where the client_salary is greater than 30000.
The following command will return those clients information whose salary is more than 30000:
SELECT client_name, client_profession,
client_qualification, Client_salary
FROM Client c
WHERE EXISTS
(SELECT 1
FROM Client_details cd
WHERE cd.client_id = c.client_id
AND client_salary > 30000 )
ORDER BY client_name, client_profession;Output
After successful execution of the above command, we will get the following result:
As we can see in the above screenshot, the subquery analysis the Client_details table to identify the following scenarios of every client’s detail in the Client table.
- If the client_id from the Client_details table, and client_id from Client table (cd.client_id = c.client_id) are equal.
- And the client_salary is greater than 30000 (client_salary > 30000).
Example of PostgreSQL EXISTS condition with INSERT Command
We will display how to use the EXISTS condition with the INSERT command.
For this, we are taking the employee, department, and Jobs tables 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 department table from the employee table. We take the phone and address columns records from the employee table where the emp_id and job_id is equal in both tables.
INSERT INTO department
(phone, address)
SELECT phone, address
FROM employee
WHERE EXISTS (SELECT 1
FROM Jobs
where employee.emp_id= Jobs.Job_id);Output
After executing the above command, we will get the below message window displaying that the value has been inserted successfully in the department table.
To check whether the records have been inserted into the department table or not, we will use the SELECT command as follows:
Select * from department; Output
On executing the above command, we will get the following output:
In the above screenshot, we can see that the PostgreSQL EXISTS condition inserted four records into the department table successfully.
Example of PostgreSQL EXISTS condition with SELECT Command
In the below example, we will display how to use the EXISTS condition with the SELECT command with two conditions.
We are taking the Client and Client_details table from the Javatpoint database to select all the records from the one table.
We are using the EXISTS condition with WHERE clause as we can see the following command:
SELECT *
FROM Client
WHERE EXISTS (SELECT 1
FROM Client_details
WHERE Client.client_id = client_details.client_id);Output
After executing the above command, we will get the following output:
In the above Subquery statement, we have used the SELECT 1, which helps us to enhance the performance since the column output is not required in the Exists condition as only the presence of a returned row matters.
As we can see in the above screenshot, the PostgreSQL EXISTS condition will return all the records from the Client table where at least one records in the Client_details table having a similar Client_id.
Example of PostgreSQL EXIST Condition using NOT EXISTS Condition
In PostgreSQL, we can also combine the NOT condition with the EXISTS condition.
The PostgreSQL NOT Operator with EXISTS Condition is used to fetch those rows whose values do not match the list’s values.
Note: The NOT condition contradicts the output of the EXISTS condition. As we know that the NOT condition is the exact opposite to the EXISTS condition, which implies that:
- If the subquery returns no row, the NOT EXISTScondition will return TRUE.
- Or if the subquery returns multiples rows, then the NOT EXISTScondition will return FALSE.
For this, we are taking a similar table (Client and client_details) as the above example.
The following example displays Not condition with EXISTS condition to fetch that client information, which does not exist in the Client_details table.
SELECT *
FROM Client
WHERE NOT EXISTS (SELECT 1
FROM Client_details
WHERE Client.client_id = client_details.client_id);Output
On executing the above command, we will get the below output:
In the above example, the PostgreSQL EXISTS example will return all records from the Client table where there are no records in the client_details table for the given client_id.
Example of using the EXISTS condition with NULL
In PostgreSQL, we can also use the EXISTS condition with NULL.
The PostgreSQL EXISTS Condition with NULL is used to get those rows where the subquery will return NULL, and the EXISTS condition will return TRUE.
For this, we are taking the employee table from the Javatpoint database.
Let us see the below example to understand how the Exists condition will work with NULL.
SELECT emp_fname, emp_lname
FROM employee
WHERE EXISTS (SELECT NULL)
ORDER BY emp_fname, emp_lname;Output
On executing the above command, we will get the following output:
In the above example, the subquery statement returned NULL. Hence, the command will return all rows from the employee table.
Example of PostgreSQL EXISTS Condition with UPDATE Command
In the below example, we will display how to use the EXISTS condition with the UPDATE command.
For this, we are taking the Summer_fruits and Winter_fruits tables from the Javatpoint database.
In the following command, the PostgreSQL EXISTS condition is used to update the Fruits_name column values in the summer_fruits table as of the winter_fruits_name column from the winter_fruits table where the winter_fruits’ wf_id column is equal to summer_fruits’ sf_id column.
UPDATE summer_fruits
SET Fruits_name=(SELECT winter_fruits.winter_fruits_name
FROM winter_fruits
WHERE winter_fruits.wf_id = summer_fruits.sf_id)
WHERE EXISTS (SELECT 1
FROM winter_fruits
WHERE winter_fruits.wf_id = summer_fruits.sf_id);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 EXISTS condition with DELETE Command
In the below example, we will display how to use the EXISTS Condition with the DELETE command.
In the below example, we will take the Course and Course_categories table from the JavatPoint Database for deleting the particular records from the table.
In the following command, the PostgreSQL EXISTS Condition is used to delete all the records from the Course table where the course_id from Course table and course_category_id from the Course_categories table are equal.
DELETE FROM Course
WHERE EXISTS (SELECT 1
FROM Course_categories
WHERE Course.course_id = Course_categories.course_category_id);Output
After executing the above command, we will get the below message window displaying that the records have been deleted successfully from the Course table.
We will now use the Select command to check whether the particular records have been deleted or not in the Course table:
SELECT *
FROM Course;Output
After successfully executing the above command, we will get the following output:
Overview
In the PostgreSQL EXISTS Condition section, we have learned the following topics:
The use of PostgreSQL EXISTS condition provides the advantages of EXISTS condition to assess rows existence in the subquery.
- We have seen the sample example of EXISTS condition with a subquery to check whether the condition has been met once the subquery returns at least one row.
- We used the EXISTS Condition with INSERT command to insert the records from one table to another.
- We used the EXISTS Condition with the SELECT command to get the particular table records.
- We used the EXISTS Condition with the NOT Operator into the SELECT command to fetch the not exists records from the particular table.
- We used the EXISTS Condition with the NULL to get the particular table records.
- We used the EXISTS Condition with the UPDATE statement to update the table’s records.
- We used the EXISTS Condition with the DELETE statement to remove the particular table’s records.
Leave a Reply