PL-SQL
What is PL/SQL? Full Form, Architecture of PL/SQL Developer
What is PL/SQL? Oracle PL/SQL is an extension of SQL language that combines the data manipulation power...
The EXISTS operator tests whether a row(s) exists in a subquery. This means that the operator is used together with a subquery. The Exists operator is said to have been met when at least one row is found in the subquery. You can use this operation along with SELECT, UPDATE, INSERT, and DELETE statements.
In this PostgreSQL Tutorial, you will learn the following:
Here is the syntax for the PostgreSQL EXISTS statement:
WHERE EXISTS (subquery);
The above syntax shows that the EXISTS operator takes in an argument, which is a subquery. The subquery is simply a SELECT statement that should begin with a SELECT * instead of a list of column names or expressions.
Let see how to use a SELECT statement with the EXISTS operator. We have the following tables:
Book:
Price:
Run the following statement:
SELECT * FROM Book WHERE EXISTS (SELECT * FROM Price WHERE Book.id = Price.id);
This returns the following:
The above command should return all records in the Book table whose id matches the id of any records by the subquery. Only one id was matched. Hence, only one record was returned.
We can use the EXISTS operator in an INSERT statement. We have the following 3 tables:
Book:
Price:
Price2:
We can then run the following statement:
INSERT INTO Price SELECT id, price FROM Price2 WHERE EXISTS (SELECT id FROM Book WHERE Book.id = Price2.id);
The Price table is now as follows:
The row with an id of 5 in the table named Price2 was matched. This record was then inserted into the Price table.
We can use the EXISTS operator in an UPDATE statement.
Run the following query:
UPDATE Price SET price = (SELECT price FROM Price2 WHERE Price2.id = Price.id) WHERE EXISTS (SELECT id, price FROM Price2 WHERE Price2.id = Price.id);
We are updating the price column of the Price table. Our aim is to have the prices of items sharing an id to be the same. Only one row was matched, that is, 5.
However, since the prices are equal, that is, 205, no update was made. If there was a difference, an update would have been made.
A PostgreSQL DELETE statement can use the EXISTS operator. Here is an example:
DELETE FROM Price WHERE EXISTS (SELECT * FROM Price2 WHERE Price.id = Price2.id);
The Price table is now as follows:
The row with an id of 5 has been deleted.
Now let's see how these actions can be performed using pgAdmin.
To accomplish the same through pgAdmin, do this:
Step 1) Login to your pgAdmin account.
Step 2)
Step 3) Type the query in the query editor:
SELECT * FROM Book WHERE EXISTS (SELECT * FROM Price WHERE Book.id = Price.id);
Step 4) Click the Execute button.
It should return the following:
To accomplish the same through pgAdmin, do this:
Step 1) Login to your pgAdmin account.
Step 2)
Step 3) Type the query in the query editor:
INSERT INTO Price SELECT id, price FROM Price2 WHERE EXISTS (SELECT id FROM Book WHERE Book.id = Price2.id);
Step 4) Click the Execute button.
The Price table should now be as follows:
To accomplish the same through pgAdmin, do this:
Step 1) Login to your pgAdmin account.
Step 2)
Step 3) Type the query in the query editor:
UPDATE Price SET price = (SELECT price FROM Price2 WHERE Price2.id = Price.id) WHERE EXISTS (SELECT id, price FROM Price2 WHERE Price2.id = Price.id);
Step 4) Click the Execute button.
The Price table should now be as follows:
To accomplish the same through pgAdmin, do this:
Step 1) Login to your pgAdmin account.
Step 2)
Step 3) Type the query in the query editor:
DELETE FROM Price WHERE EXISTS (SELECT * FROM Price2 WHERE Price.id = Price2.id);
Step 4) Click the Execute button.
The Price table should now be as follows:
Download the Database used in this Tutorial
What is PL/SQL? Oracle PL/SQL is an extension of SQL language that combines the data manipulation power...
What is For Loop? "FOR LOOP" statement is best suitable when you want to execute a code for a...
What is SQLite? SQLite is an open-source, embedded, relational database management system,...
PL/SQL Tutorial Oracle PL/SQL is an extension of SQL language, designed for seamless processing of SQL...
In this tutorial, we will introduce SQL* Plus and learn how to connect it to the database. After...
What are views? VIEWS are virtual tables that do not store any data of their own but display data...