MySQL - Delete Join

Table of content

Simple deletion operation in MySQL can be performed on a single entity or multiple entities of a table. But what if this deletion operation is to be performed on multiple entities of multiple tables? This is where Joins come into picture.

MySQL DELETE... JOIN

As we have discussed in this tutorial previously, Joins are used to retrieve records from two or more tables, by combining columns of these tables based on the common fields. This merged data can be deleted with all the changes reflected in original tables.

Syntax

Following is the basic syntax of DELETE... JOIN statement in MySQL −

DELETE table(s)
FROM table1 JOIN table2
ON table1.common_field = table2.common_field;

We can use any join clause (INNER JOIN, LEFT JOIN, RIGHT JOIN etc.) while performing deletion.

Example

In this example, we first create a table named CUSTOMERS, which contains the personal details of customers including their name, age, address and salary etc.

CREATE TABLE CUSTOMERS (
   ID INT NOT NULL,
   NAME VARCHAR (20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),       
   PRIMARY KEY (ID)
);

Now insert values into this table using the INSERT statement as follows −

INSERT INTO CUSTOMERS VALUES 
(1, 'Ramesh', 32, 'Ahmedabad', 2000.00),
(2, 'Khilan', 25, 'Delhi', 1500.00),
(3, 'Kaushik', 23, 'Kota', 2000.00),
(4, 'Chaitali', 25, 'Mumbai', 6500.00),
(5, 'Hardik', 27, 'Bhopal', 8500.00),
(6, 'Komal', 22, 'Hyderabad', 4500.00),
(7, 'Muffy', 24, 'Indore', 10000.00);

The table will be created as −

ID NAME AGE ADDRESS SALARY
1 Ramesh 32 Ahmedabad 2000.00
2 Khilan 25 Delhi 1500.00
3 Kaushik 23 Kota 2000.00
4 Chaitali 25 Mumbai 6500.00
5 Hardik 27 Bhopal 8500.00
6 Komal 22 Hyderabad 4500.00
7 Muffy 24 Indore 10000.00

Let us create another table ORDERS, containing the details of orders made and the date they are made on.

CREATE TABLE ORDERS (
   OID INT NOT NULL,
   DATE VARCHAR (20) NOT NULL,
   CUSTOMER_ID INT NOT NULL,
   AMOUNT DECIMAL (18, 2),
);

Using the INSERT statement, insert values into this table as follows −

INSERT INTO ORDERS VALUES 
(102, '2009-10-08 00:00:00', 3, 3000.00),
(100, '2009-10-08 00:00:00', 3, 1500.00),
(101, '2009-11-20 00:00:00', 2, 1560.00),
(103, '2008-05-20 00:00:00', 4, 2060.00);

The table is displayed as follows −

OID DATE CUSTOMER_ID AMOUNT
102 2009-10-08 00:00:00 3 3000.00
100 2009-10-08 00:00:00 3 1500.00
101 2009-11-20 00:00:00 2 1560.00
103 2008-05-20 00:00:00 4 2060.00

The delete operation is performed by applying the DELETE... JOIN query on these tables.

DELETE a
FROM CUSTOMERS AS a INNER JOIN ORDERS AS b
ON a.ID = b.CUSTOMER_ID;

Verification

To verify if the changes are reflected in the tables, we can use SELECT statement to print the tables.

ID NAME AGE ADDRESS SALARY
1 Ramesh 32 Ahmedabad 2000.00
5 Hardik 27 Bhopal 8500.00
6 Komal 22 Hyderabad 4500.00
7 Muffy 24 Indore 10000.00

DELETE... JOIN with WHERE Clause

The ON clause in DELETE... JOIN query is used to apply constraints on the records. In addition to it, we can also use WHERE clause to make the filtration stricter. Observe the query below; here, we are trying to delete the records of customers, in the CUSTOMERS table, whose salary is lower than Rs. 2000.00.

DELETE a
FROM CUSTOMERS AS a INNER JOIN ORDERS AS b
ON a.ID = b.CUSTOMER_ID
WHERE a.SALARY < 2000.00;

Verification

To verify whether the changes are reflected in the original tables or not, we will use the SELECT statement.

The CUSTOMERS table after deletion is as follows −

ID NAME AGE ADDRESS SALARY
1 Ramesh 32 Ahmedabad 2000.00
3 Kaushik 23 Kota 2000.00
4 Chaitali 25 Mumbai 6500.00
5 Hardik 27 Bhopal 8500.00
6 Komal 22 Hyderabad 4500.00
7 Muffy 24 Indore 10000.00

Delete Join Using Client Program

In addition to joining two or more than two tables using the MySQL query, we can also perform the Delete Join operation using a client program.

Syntax

PHP NodeJS Java Python

To perform Delete Join through a PHP program, we need to execute the DELETE statement with JOIN clause using the mysqli function query() as follows −

$sql = 'DELETE tutorials_tbl, tcount_tbl FROM tcount_tbl INNER JOIN tutorials_tbl ON tcount_tbl.tutorial_author = tutorials_tbl.tutorial_author';
$mysqli->query($sql);

To perform Delete Join through a JavaScript program, we need to execute the DELETE statement with JOIN clause using the query() function of mysql2 library as follows −

sql = "DELETE tutorials_tbl, tcount_tbl FROM tcount_tbl INNER JOIN tutorials_tbl ON tcount_tbl.tutorial_author = tutorials_tbl.tutorial_author";
con.query(sql);

To perform Delete Join through a Java program, we need to execute the DELETE statement with JOIN clause using the JDBC function executeUpdate() as follows −

String sql = "DELETE tutorials_tbl, tcount_tbl FROM tcount_tbl INNER JOIN tutorials_tbl ON tcount_tbl.tutorial_author = tutorials_tbl.tutorial_author";
statement.executeUpdate(sql);

To perform Delete Join through a python program, we need to execute the DELETE statement with JOIN clause using the execute() function of the MySQL Connector/Python as follows −

delete_join_query = "DELETE a FROM CUSTOMERS AS a INNER JOIN ORDERS AS b ON a.ID = b.CUST_ID"
cursorObj.execute(delete_join_query)

Example

Following are the programs −

PHP NodeJS Java Python 
$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = 'password';
$dbname = 'TUTORIALS';
$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);
if ($mysqli->connect_errno) {
    printf("Connect failed: %s
", $mysqli->connect_error);
    exit();
}
// printf('Connected successfully.
');

$sql = 'DELETE tutorials_tbl, tcount_tbl FROM tcount_tbl INNER JOIN tutorials_tbl ON tcount_tbl.tutorial_author = tutorials_tbl.tutorial_author';
if ($mysqli->query($sql)) {
    printf("Join deleted successfully!.
");
}
if ($mysqli->errno) {
    printf("Join could not be deleted !.
", $mysqli->error);
}
$mysqli->close();

Output

The output obtained is as follows −

Join deleted successfully!.  

var mysql = require("mysql2");
var con = mysql.createConnection({
  host: "localhost",
  user: "root",
  password: "password",
}); //Connecting to MySQL

con.connect(function (err) {
  if (err) throw err;
  //   console.log("Connected successfully...!");
  //   console.log("--------------------------");
  sql = "USE TUTORIALS";
  con.query(sql);

  //Delete Join
  sql = "DELETE tutorials_tbl, tcount_tbl FROM tcount_tbl INNER JOIN tutorials_tbl ON tcount_tbl.tutorial_author = tutorials_tbl.tutorial_author";
  con.query(sql, function (err, result) {
    if (err) throw err;
    console.log(result);
  });
});

Output

The output produced is as follows −

ResultSetHeader {
  fieldCount: 0,
  affectedRows: 2,
  insertId: 0,
  info: '',
  serverStatus: 34,
  warningStatus: 0,
  changedRows: 0
}

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class DeleteJoin {
   public static void main(String[] args) {
      String url = "jdbc:mysql://localhost:3306/TUTORIALS";
      String username = "root";
      String password = "password";
      try {
         Class.forName("com.mysql.cj.jdbc.Driver");
         Connection connection = DriverManager.getConnection(url, username, password);
         Statement statement = connection.createStatement();
         System.out.println("Connected successfully...!");

         //MySQL Delete JOIN...!;
         String sql = "DELETE tutorials_tbl, tcount_tbl FROM tcount_tbl INNER JOIN tutorials_tbl ON tcount_tbl.tutorial_author = tutorials_tbl.tutorial_author";
         statement.executeUpdate(sql);
         System.out.println("JOIN Deleted successfully...!");
         connection.close();
      } catch (Exception e) {
         System.out.println(e);
      }
   }
}

Output

The output obtained is as shown below −

Connected successfully...!
JOIN Deleted successfully...! 

import mysql.connector
#establishing the connection
connection = mysql.connector.connect(
    host='localhost',
    user='root',
    password='password',
    database='tut'
)
cursorObj = connection.cursor()
delete_join_query = f"""DELETE a FROM CUSTOMERS AS a INNER JOIN ORDERS AS b ON a.ID = b.CUST_ID"""
cursorObj.execute(delete_join_query)
connection.commit()
print("deleted succesfully")
cursorObj.close()
connection.close()

Output

Following is the output of the above code −

deleted successfully
Advertisements