Introduction to PostgreSQL EXISTS
The PostgreSQL EXISTS condition is used to check the existence of rows from the result set of the subquery by combining with a subquery, which is acceptable even if the result set is returned by the subquery contains at least one row which means the PostgreSQL EXISTS works with a subquery. The PostgreSQL EXISTS can be used with a SELECT, INSERT, UPDATE, or DELETE SQL statements.
Syntax:
The syntax of the PostgreSQL EXISTS is as follows:
WHERE EXISTS ( subquery );
Explanation:
Subquery: The SELECT statement which generally we use with an asterisk (*) operator as SELECT * instead of defining the list of expressions or the list of names of the columns. We can use SELECT 1 instead of SELECT * in order to improve the performance as we are not bothered about the result of the SELECT statement what we need is only the rows returned.
How EXISTS works in PostgreSQL?
The result of the PostgreSQL EXISTS will be true if the result set of subquery contains at least one row. And it will be false if the result set of subquery has the result with no row.
The subquery written in the PostgreSQL EXISTS operator is very inefficient as it executes for each row of the outer query’s table. So it is not good practice to use EXISTS as we have other more efficient ways to write SQL queries.
If the PostgreSQL subquery returns us the NULL result then the result of the PostgreSQL EXISTS will be true.
Examples of PostgreSQL EXISTS
Let’s create two tables named’ transaction’ and ‘invoices’ in order to understand the PostgreSQL NATURAL JOIN examples.
The following CREATE TABLE statements will create the transaction and invoices table.
CREATE TABLE transaction (
transaction_id serial PRIMARY KEY,
transaction_data VARCHAR (256) NOT NULL
);
CREATE TABLE invoices (
invoice_id serial PRIMARY KEY,
transaction_id INT NOT NULL,
invoice_data VARCHAR (256) NOT NULL,
FOREIGN KEY (transaction_id) REFERENCES transaction (transaction_id)
);
The transaction_id is the primary key of the transaction table which is referred to as foreign key for the invoices table. So while performing natural join operation we will use the transaction_id column as it is the common column for both tables. The transaction may have zero or more invoices and the invoice will belong to one and only one transaction.
Now insert some data into the transaction and invoices tables using INSERT statement as follows:
INSERT INTO transaction (transaction_data)
VALUES
('Purchase of Mobile'),
('Purchase of PC'),
('Purchase of Headphone');
INSERT INTO invoices (invoice_data, transaction_id)
VALUES
('Purchase of Mobile', 1),
('Purchase of Mobile', 1),
('Purchase of PC', 2),
('Purchase of PC', 2),
('Purchase of Headphone', 3),
('Purchase of Headphone', 3);
Illustrate the content of the transaction table using the following statement and snapshot.
select * from transaction;
Illustrate the content of the invoices table using the following statement and snapshot.
select * from invoices;
Examples #1 – The PostgreSQL with SELECT statement
Use the operator with SELECT statement from the invoices table as follows:
SELECT *
FROM invoices
WHERE EXISTS (SELECT 1
FROM transaction
WHERE transaction.transaction_id = invoices.transaction_id);
This example with PostgreSQL EXISTS operator will return all rows from the invoices table where at least one row is present in the transaction table on the operator to match using column transaction_id. Here, we have defined 1 instead of an asterisk(*) with SELECT statement in the subquery to improve the performance.
The result returned the subquery is not relevant for us as we need the operator to check the existence of rows only.
Illustrate the result of the above statement using the following snapshot.
Example #2 – The PostgreSQL with an INSERT statement
Use the operator to INSERT the data in the invoices table as follows:
INSERT INTO invoices (transaction_id, invoice_data)
SELECT transaction_id, transaction_data
FROM transaction
WHERE EXISTS (SELECT 1
FROM transaction
WHERE transaction.transaction_id = 2);
Illustrate the result of the above statement using the following snapshot and the following SQL statement.
Select * from invoices;
Example #3 – The PostgreSQL with an UPDATE statement
Use the operator to UPDATE the data in the invoices table as follows:
UPDATE invoices
SET invoice_data = (SELECT transaction.transaction_data
FROM transaction
WHERE transaction.transaction_id = 3)
WHERE EXISTS (SELECT 1
FROM transaction
WHERE transaction.transaction_id = 3);
Illustrate the result of the above statement using the following snapshot and following SQL statement.
select * from invoices;
Example #4 – The PostgreSQL with the DELETE statement
Use the operator to DELETE the row from the invoices table as follows:
DELETE FROM invoices
WHERE EXISTS (SELECT 1
FROM transaction
WHERE transaction.transaction_id = 2);
Advantages
- We can use the clause with a subquery in a SQL statement.
- We can use the operation along with SELECT, UPDATE, INSERT, and DELETE SQL statements.
- The result depends on the numbers of rows returned by the subquery but does not depend on the column values of the table.
- The result will be true if the subquery returns at least one row or returns NULL.
Conclusion
We hope from the above article you have learned about the PostgreSQL EXISTS operator works and what are its advantages. Also, we have added some examples to demonstrate to you how to use the PostgreSQL EXISTS operator to query data.
Recommended Articles
This is a guide to PostgreSQL EXISTS. to PostgreSQL EXISTS. Here we discuss the examples of PostgreSQL Exist on How to use it and How does it work in detail. You may also have a look at the following articles to learn more –
