MySQL - Delete Join

Table of content

MySQL DELETE... JOIN
DELETE... JOIN with WHERE Clause
Delete Join Using Client Program

Quiz

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

Print Page