PostgreSQL DELETE JOIN

Updated May 25, 2023

Introduction to PostgreSQL DELETE JOIN

It is used to delete table records or rows. We can join two tables with the “using” keyword in PostgreSQL, after table joins, it will delete the specified rows from the specified table. It is used to delete the rows using with and without where conditions. We can also delete the rows of the table using inner, full, right, and left join.

Syntax

Given below is the syntax mentioned:

1. Inner join with PostgreSQL DELETE JOIN.

DELETE FROM table_name1 WHERE condition column_name IN (SELECT table_name1.id FROM table_name1 INNER JOIN table_name2 ON table_name1.column_name = table_name2.column_name WHERE [condition]

2. FULL join with PostgreSQL DELETE JOIN.

DELETE FROM table_name1 WHERE condition column_name IN (SELECT table_name1.id FROM table_name1 FULL JOIN table_name2 ON table_name1.column_name = table_name2.column_name WHERE [condition]

3. Left join with PostgreSQL DELETE JOIN.

DELETE FROM table_name1 WHERE condition column_name IN (SELECT table_name1.id FROM table_name1 LEFT JOIN table_name2 ON table_name1.column_name = table_name2.column_name WHERE [condition]

4. Right join with PostgreSQL DELETE JOIN.

DELETE FROM table_name1 WHERE condition column_name IN (SELECT table_name1.id FROM table_name1 RIGHT JOIN table_name2 ON table_name1.column_name = table_name2.column_name WHERE [condition]

5. Using a keyword with DELETE JOIN in PostgreSQL.

DELETE FROM table1 USING table2 WHERE table1.column_name = table2.column_name;

Given below is the parameter description:

• Delete: This operation deletes all rows or specified rows using the join operation.
• Table name1: This is defined as a table name used to join with the second table; after joining, it will delete the rows from a table.
• Table name2: This is defined as a table name used to join with the first table; after joining, it will delete the rows from a table.
• Column name: Column name is defined as joining the two tables from the specified columns. We have entered the table using column conditions. After joining two table columns, we can delete the rows as per the specific conditions.
• Where condition: We use the where clause to delete the rows from a table; we have to delete the rows while joining two tables with the where clause.
• Inner join: An inner join is a join that retrieves only the records with matching values in table1 and table2. It does not delete any records from the table.
• Full join: A full join is a join that combines records from table1 and table2, including the matched records and the unmatched records from each table. It does not delete any records.
• Left join: A left join is a type of join that retrieves all the records from the left table (table1) and the matching records from the right table (table2). It does not delete any records from the table.
• Right join: This is defined as deleting the records from the table which was matching from the right table.
• Using: We can delete the rows using keywords. It will work the same as the inner join in PostgreSQL.
• Select: Select operations is defined as using select operations to select data from the table; after selecting, we have to delete specified rows as per the condition.
• IN: Developers use the “IN” keyword to specify an inner query for filtering records in a table. It does not delete any records from the table. It does not delete any records from the table.

How to DELETE JOIN in PostgreSQL Using Various Methods?

Below are the various methods which we have used to use DELETE JOIN. We are using the stud1 and stud2 table to describe examples.

Below are the data description and data of stud1 tables.

Code:

select * from stud1;
\d+ stud1;

Output:

Below is the data description and data of stud2 tables.

Code:

select * from stud2;
\d+ stud2;

Output:

Example #1

With INNER JOIN.

In the example below, we have joined the stud1 and stud2 tables with inner join; after joining, we deleted the rows from the table using the where condition.

Code:

DELETE FROM stud1 WHERE id IN (SELECT stud1.id FROM stud1 INNER JOIN stud2 ON stud1.id = stud2.id WHERE stud1.id = 11);
select * from stud1;

Output:

Example #2

With LEFT JOIN.

In the below example, we have joined the stud1 and stud2 tables with left join, after joining, we have deleted the rows from the table by using the where condition.

Code:

DELETE FROM stud1 WHERE id IN (SELECT stud1.id FROM stud1 LEFT JOIN stud2 ON stud1.id = stud2.id WHERE stud1.id = 10);
select * from stud1;

Output:

Example #3

With RIGHT JOIN.

In the below example, we have joined the stud1 and stud2 tables with the right join, after joining, we have deleted the rows from the table by using the where condition.

Code:

DELETE FROM stud1 WHERE id IN (SELECT stud1.id FROM stud1 RIGHT JOIN stud2 ON stud1.id = stud2.id WHERE stud1.id = 11);
select * from stud1;

Output:

Example #4

With using keywords.

In the below example, we have joined the stud1 and stud2 table using keyword, after joining, we have deleted the rows from the table using the where condition.

Code:

DELETE FROM stud1 USING stud2 WHERE stud1.id = stud2.id AND stud1.id = 9;
select * from stud1;

Output:

Example #5

With FULL JOIN.

In the below example, we have joined the stud1 and stud2 tables with full join; after joining, we have deleted the rows from the table.

Code:

DELETE FROM stud1 WHERE id IN (SELECT stud1.id FROM stud1 FULL JOIN stud2 ON stud1.id = stud2.id);
select * from stud1;

Output:

Recommended Articles

We hope that this EDUCBA information on “PostgreSQL DELETE JOIN” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

1. PostgreSQL Wal
2. Log Queries in PostgreSQL
3. PostgreSQL Copy Database
4. PostgreSQL OID