> This page location: Modifying Data > DELETE Join
> Full Neon documentation index: https://neon.com/docs/llms.txt

# PostgreSQL DELETE JOIN

**Summary**: in this tutorial, you will learn how to use the PostgreSQL `DELETE` statement to emulate delete join operations.

## Introduction to PostgreSQL DELETE statement with USING clause

PostgreSQL does not support the [DELETE JOIN statement like MySQL](https://www.mysqltutorial.org/mysql-basics/mysql-delete-join/). Instead, it offers the `USING` clause in the [`DELETE`](https://neon.com/postgresql/postgresql-tutorial/postgresql-delete) statement that provides similar functionality to the `DELETE JOIN`.

Here's the syntax of the `DELETE USING` statement:

```sql
DELETE FROM table1
USING table2
WHERE condition
RETURNING returning_columns;
```

In this syntax:

- First, specify the name of the table (`table1`) from which you want to delete data after the `DELETE FROM` keywords
- Second, provide a table (`table2`) to join with the main table after the `USING` keyword.
- Third, define a condition in the `WHERE` clause for joining two tables.
- Finally, return the deleted rows in the `RETURNING` clause. The `RETURNING` clause is optional.

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`:

```sql
DELETE FROM t1
USING t2
WHERE t1.id = t2.id
```

## PostgreSQL DELETE JOIN examples

Let's explore some examples of using the `DELETE USING` statement.

### Setting up sample tables

The following statements create `member` and `denylist` tables and insert some sample data into them:

```sql
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:

```text
 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 the `members` table with the phone number exists in the `denylist` table:

```sql
DELETE FROM member
USING denylist
WHERE member.phone = denylist.phone;
```

Output:

```sql
DELETE 2
```

The output indicates that the `DELETE` statement has deleted two rows from the `member` table.

Verify the deletion by retrieving data from the `contacts` table:

```sql
SELECT * FROM member;
```

Output:

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

### 2) Delete join using a subquery example

The `USING` 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 the `USING` clause in the `DELETE` statement. Instead, you may consider using a [subquery](https://neon.com/postgresql/postgresql-tutorial/postgresql-subquery).

The following statement uses the `DELETE` statement to delete all rows from the member table whose phones are in the `denylist` table:

```sql
DELETE FROM member
WHERE phone IN (
    SELECT
      phone
    FROM
      denylist
);
```

In this example:

- First, the subquery returns a list of phones from the `denylist` table.
- Second, the `DELETE` 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 the `DELETE USING` statement or a subquery to emulate the `DELETE JOIN` operation.

---

## Related docs (Modifying Data)

- [INSERT](https://neon.com/postgresql/postgresql-tutorial/postgresql-insert)
- [INSERT Multiple Rows](https://neon.com/postgresql/postgresql-tutorial/postgresql-insert-multiple-rows)
- [UPDATE](https://neon.com/postgresql/postgresql-tutorial/postgresql-update)
- [UPDATE Join](https://neon.com/postgresql/postgresql-tutorial/postgresql-update-join)
- [DELETE](https://neon.com/postgresql/postgresql-tutorial/postgresql-delete)
- [DELETE CASCADE](https://neon.com/postgresql/postgresql-tutorial/postgresql-delete-cascade)
- [Delete Duplicate Rows](https://neon.com/postgresql/postgresql-tutorial/how-to-delete-duplicate-rows-in-postgresql)
- [Upsert](https://neon.com/postgresql/postgresql-tutorial/postgresql-upsert)
- [MERGE](https://neon.com/postgresql/postgresql-tutorial/postgresql-merge)