To access related data from multiple tables, JOINS can be used in PostgreSQL. However, the "DELETE" command can be used to remove a Join if it is no longer needed. The DELETE command allows the removal of data from multiple tables according to the defined “join” conditions.

This guide will explain how to delete a JOIN in PostgreSQL using practical examples.

How to Use DELETE JOIN in PostgreSQL?

The DELETE JOIN is not supported by PostgreSQL directly but the USING clause can be used along with the DELETE keyword to get the same effect. DELETE JOIN can be used to delete data from one table using the reference of other tables. The reference table can either be a single table or multiple tables joined to apply DELETE JOIN in PostgreSQL.

Syntax

The following query is the syntax of the DELETE statement with USING keyword:

DELETE FROM Table_A
USING Table_B
WHERE condition
RETURNING returning_columns;

The above query:

• Starts with a DELETE statement containing the table name and USING clause that has the reference table inside it.
• WHERE condition has the columns which are used to provide the similarity in both the tables for joining the data.

The following query provides a simple description of the DELETE statement with USING clause with A and B tables having an id column common between them:

DELETE FROM tA
USING tB
WHERE tA.id = tB.id;

Example 1: DELETE JOIN in PostgreSQL

First, get the data from the orders table using the below command:

SELECT * FROM orders;

The above query displays the data from the orders table upon its execution:

Use the following code to get data from the “customers” table:

SELECT * FROM customers;

Use the following query to use as the DELETE JOIN:

DELETE FROM orders
USING customers
WHERE orders.customer_id = customers.id;

The above code deletes the data from the orders table using the “customers” table having the customer_id column common between them:

Check the data from the orders table by using the following query:

SELECT * FROM orders;

All the data is deleted from the orders table:

Example 2: Sub-Query For DELETE JOIN

WHERE clause can be used instead of USING to get the DELETE JOIN results as this example uses it to delete data. First, use the following query to access the data from the orders table:

SELECT * FROM orders;

Use the below-given query to apply DELETE JOIN in the PostgreSQL table:

DELETE FROM orders
WHERE customer_id IN (SELECT customer_id FROM customers);

The above code deletes the data from the orders table where customer_id is common in the customers and orders table:

The following query displays the deletion of data from the orders table:

SELECT * FROM orders;

That’s all about using DELETE JOIN in PostgreSQL.

Conclusion

PostgreSQL does not support the direct use of DELETE JOIN in its queries; however, the DELETE statement with USING clause can be used for this purpose. DELETE statements with either USING or WHERE keywords can produce similar results compared to the DELETE JOIN. This guide has explained the use of both these methods with examples in PostgreSQL.