Introduction to PostgreSQL DELETE statement with USING clause

PostgreSQL does not support theDELETE JOIN statement like MySQL. Instead, it offers theUSING clause in theDELETE statement that provides similar functionality to theDELETE JOIN.

Here’s the syntax of theDELETE USING statement:

DELETE FROM table1USING table2WHERE conditionRETURNING returning_columns;

In this syntax:

• First, specify the name of the table (table1) from which you want to delete data after theDELETE FROM keywords
• Second, provide a table (table2) to join with the main table after theUSING keyword.
• Third, define a condition in theWHERE clause for joining two tables.
• Finally, return the deleted rows in theRETURNING clause. TheRETURNING clause is optional.

For example, the following statement uses theDELETE statement with theUSING clause to delete data fromt1 that has the same id ast2:

DELETE FROM t1USING t2WHERE t1.id= t2.id

PostgreSQL DELETE JOIN examples

Let’s explore some examples of using theDELETE USING statement.

Setting up sample tables

The following statements createmember anddenylist tables and insert some sample data into them:

CREATE TABLE member(   id SERIAL PRIMARY KEY,   first_name VARCHAR(50)NOT NULL,   last_name VARCHAR(50)NOT NULL,   phone VARCHAR(15)NOT NULL);CREATE TABLE denylist(    phone VARCHAR(15)PRIMARY KEY);INSERT INTO member(first_name, last_name, phone)VALUES ('John','Doe','(408)-523-9874'),       ('Jane','Doe','(408)-511-9876'),       ('Lily','Bush','(408)-124-9221');INSERT INTO denylist(phone)VALUES ('(408)-523-9874'),       ('(408)-511-9876');SELECT * FROM member;SELECT * FROM denylist;

The member table:

id | first_name | last_name |     phone----+------------+-----------+----------------  1 | John       | Doe       | (408)-523-9874  2 | Jane       | Doe       | (408)-511-9876  3 | Lily       | Bush      | (408)-124-9221(3 rows)

The denylist table:

phone---------------- (408)-523-9874 (408)-511-9876(2 rows)

1) Basic PostgreSQL delete join example

The following statement deletes rows in themembers table with the phone number exists in thedenylist table:

DELETE FROM memberUSING denylistWHERE member.phone = denylist.phone;

Output:

DELETE 2

The output indicates that theDELETE statement has deleted two rows from themember table.

Verify the deletion by retrieving data from thecontacts table:

SELECT * FROM member;

Output:

id | first_name | last_name |     phone----+------------+-----------+----------------  3 | Lily       | Bush      | (408)-124-9221(1 row)

2) Delete join using a subquery example

TheUSING clause is not a part of the SQL standard, meaning that it may not be available in other database systems.

If you intend to ensure compatibility with various database products, you should avoid using theUSING clause in theDELETE statement. Instead, you may consider using asubquery.

The following statement uses theDELETE statement to delete all rows from the member table whose phones are in thedenylist table:

DELETE FROM memberWHERE phone IN (    SELECT      phone    FROM      denylist);

In this example:

• First, the subquery returns a list of phones from thedenylist table.
• Second, theDELETE statement deletes rows in the member table whose values in the phone column are in the list of phones returned by the subquery.

Summary

• Use theDELETE USING statement or a subquery to emulate theDELETE JOIN operation.

Last updated onJanuary 23, 2024

On this page

• Introduction to PostgreSQL DELETE statement with USING clause
• PostgreSQL DELETE JOIN examples
• Summary