August 1, 2023
Summary: in this tutorial, you will learn how to use the PostgreSQL DELETE
statement to emulate delete join operations.
Table of Contents
Introduction to PostgreSQL DELETE statement with USING clause
PostgreSQL doesn’t support the DELETE JOIN
statement. However, it does support the USING
clause in the DELETE
statement that provides similar functionality as the DELETE JOIN
.
The following shows the syntax of the DELETE
statement with the USING
clause:
DELETE FROM table_name1
USING table_expression
WHERE condition
RETURNING returning_columns;
In this syntax:
- First, specify the table expression after the
USING
keyword. It can be one or more tables. - Then, use columns from the tables that appear in the
USING
clause in theWHERE
clause for joining data.
For example, the following statement uses the DELETE
statement with the USING
clause to delete data from t1
that has the same id as t2
:
DELETE FROM t1
USING t2
WHERE t1.id = t2.id
Let’s take a look at an example.
First, use the following statements to create two tables: contacts
and blacklist
:
DROP TABLE IF EXISTS contacts;
CREATE TABLE contacts(
contact_id serial PRIMARY KEY,
first_name varchar(50) NOT NULL,
last_name varchar(50) NOT NULL,
phone varchar(15) NOT NULL
);
DROP TABLE IF EXISTS blacklist;
CREATE TABLE blacklist(
phone varchar(15) PRIMARY KEY
);
INSERT INTO contacts(first_name, last_name, phone)
VALUES ('John','Doe','(408)-523-9874'),
('Jane','Doe','(408)-511-9876'),
('Lily','Bush','(408)-124-9221');
INSERT INTO blacklist(phone)
VALUES ('(408)-523-9874'),
('(408)-511-9876');
Second, delete the contacts in the contacts
table with the phone number exists in the blacklist
table:
DELETE FROM contacts
USING blacklist
WHERE contacts.phone = blacklist.phone;
Output:
DELETE 2
Third, query data from the contacts
table:
SELECT * FROM contacts;
Delete join using a subquery
The USING
clause is not a part of the SQL standard. It means that the USING
clause may not available in other database systems.
If you plan to make your application compatible with other database products, you should not use the USING
clause in the DELETE
statement. Instead, you can use a subquery.
The following statement uses the DELETE
statement to delete all contacts whose phones are in the blacklist
table:
DELETE FROM contacts
WHERE phone IN (SELECT phone FROM blacklist);
In this example, the subquery returns a list of phones from the blacklist
table and the DELETE
statement deletes the contacts whose phones match with the phones returned by the subquery.
Summary
- Use
USING
clause in theDELETE
statement or a subquery to emulate theDELETE JOIN
operation.