PostgreSQL Exists ConditionIn 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 ConditionIn 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:
PostgreSQL EXISTS Condition SyntaxThe syntax of the PostgreSQL EXISTS condition is as follows: 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: 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:
Note:
Examples of PostgreSQL EXISTS ConditionLet 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: 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.
Example of PostgreSQL EXISTS condition with INSERT CommandWe 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. 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: 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 CommandIn 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: 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 ConditionIn 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:
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. 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 NULLIn 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. 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 CommandIn 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. 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: Output On executing the above command, we will get the below result: Example of PostgreSQL EXISTS condition with DELETE CommandIn 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. 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: Output After successfully executing the above command, we will get the following output: OverviewIn 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.
Next TopicPostgreSQL view |