PostgreSQL DELETE JOIN

Introduction to PostgreSQL DELETE JOIN

It is used to delete table records or rows. We can join two tables with “using” keyword in PostgreSQL, after table join 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 is used to delete all rows or specified rows using the join operation.
• Table name1: This is defined as a table name that was 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 which was used to join with the first table, after joining it will delete the rows from a table.
• Column name: Column name is defined as join the two tables from the specified columns. We have join the table using column conditions. After joining two table columns we can delete the rows as per the specific conditions.
• Where condition: We are using the where clause to delete the rows from table, we have delete the rows while joining two table with the where clause.
• Inner join: This is defined as delete the records from table which was matching from both the table.
• Full join: This is defined as delete the records from table which was matching from either table1 or table2.
• Left join: This is defined as delete the records from table which was matching from left table.
• Right join: This is defined as delete the records from table which was matching from right table.
• Using: We can delete the rows using keywords it will work the same as inner join in PostgreSQL.
• Select: Select operations is defined as using select operations we have to select data from table and after selecting we have deleting specified rows as per the condition.
• IN: This keyword is defined as we have to used an inner query to delete records from 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 stud1 and stud2 table to describe examples.

Below is 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 below example we have join the stud1 and stud2 table with inner 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 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 join the stud1 and stud2 table 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 join the stud1 and stud2 table 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 keyword.

In the below example we have join the stud1 and stud2 table using keyword, after joining we have deleted the rows from the table by 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 join the stud1 and stud2 table 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

This is a guide to PostgreSQL DELETE JOIN. Here we discuss the introduction and how to DELETE JOIN in PostgreSQL using various methods? You may also have a look at the following articles to learn more –

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