MySQL Tutorial

MySQL Tutorial MySQL Features MySQL Database Introduction MySQL Environmental Setup MySQL Data Types MySQL variable MySQL Advance table Query MySQL database queries MySQL Entity-Relationship Model MySQL Table Query MySQL Operators MySQL logical conditions MySQL Queries MySQL Clauses Clustered vs Non-Clustered Index MySQL Full text index MySQL Descending Index MySQL Invisible Index MySQL Composite Index MySQL Prefix index MySQL Index MySQL Create index MySQL Drop Index MySQL Show index MySQL Unique index MySQL Table MySQL Variable MySQL View MySQL Constraints MySQL Command Line Client Basic Queries MySQL Stored Procedure MySQL IF Statement MySQL Subquery MySQL Triggers

MySQL Join

MySQL Join MySQL CROSS JOIN MySQL DELETE JOIN MySQL EQUI JOIN MySQL INNER JOIN MySQL Union MySQL NATURAL JOIN MySQL RIGHT JOIN MySQL SELF JOIN MySQL UPDATE JOIN

MySQL Function

MySQL Function MySQL AVG() Function MySQL SUM() Function MySQL String() Function MySQL Advance() Function MySQL Aggregate() Function MySQL COALESCE() Function MySQL Control Flow Function MySQL COUNT() Function MySQL Date And Time Function MySQL GREATEST() Function MySQL ISNULL() Function MySQL LEAST() Function MySQL Math() Function MySQL MAX() Function MySQL MIN() Function MySQL find_in_set() function MySQL ASIN() Function MySQL CEIL() function MySQL CEILING() function MySQL TAN() Function MySQL Truncate() Function MySQL FLOOR() function MySQL LN() function MySQL LOG2() function MySQL LOG10() function MySQL MOD() function MySQL PI() function MySQL POW() function MySQL RADIANS() function MySQL RAND() function MySQL ROUND() function MySQL Character Length Function MySQL Current Date Function MySQL Date Add Function MySQL Date Format Function MySQL Datediff Function MySQL Day Function MySQL Elt Function MySQL Export Set Function MySQL Field Function MySQL Format Function MySQL From Base64 Function MySQL Hex Function MySQL Insert Function MySQL Instr Function MySQL Length Function MySQL CONCAT() function MySQL FIND_IN_SET() function MySQL LIKE() function MySQL LOAD_FILE() function MySQL LOCATE() function MySQL LOG() function MySQL MONTHNAME() function MySQL NOW() function MySQL PERIOD_ADD() function MySQL PERIOD_DIFF() function MySQL POWER() function MySQL QUARTER() function MySQL REVERSE() function MySQL RIGHT() Function MySQL RPAD() function MySQL RTRIM() function MySQL SEC_TO_TIME() function MySQL SOUNDEX() function

Questions

Which Clause is Similar to Having Clause in MySQL

Misc

MySQL Error 1046 - No Database Selected Failed to Start MySQL Service Unit MySQL Service Unit not Found Import MySQL Connector Mudule not Found Error No Module Named MySQL Joins Available in MySQL MySQL Docs MySQL Download For Windows 7 64 Bit MySQL Error Code 1064 MySQL Export MySQL History MySQL Host MySQL Import MySQL Drop All Tables MySQL Drop MySQL Error Code 1175 MySQL Events MySQL Except MYSQL Foreign Key Constraint MySQL If Exists MySQL IndexOf MySQL List All Tables json_extract in MySQL

MySQL DELETE JOIN

Sometimes join becomes complicated and unwanted. In such cases, we can delete the unwanted joins in the tables. You can delete inner join, left join, right join as per requirement. The delete join query is an essential part to handle and remove multiple joins. The data manipulation requires a delete query to remove complex join columns.

Syntax

The syntax to delete data using inner join shows below.

DELETE table1, table2
FROM table1
INNER JOIN table2 
ON table1.column1 = table2.column1
WHERE join_condition;

The syntax to delete data using left join shows below.

DELETE table1, table2
FROM table1
LEFT JOIN table2 
ON table1.column1 = table2.column1
WHERE table2.column IS NULL;

The syntax to delete data using the right join shows below.

DELETE table1, table2
FROM table2
RIGHT JOIN table1 
ON table2.column1 = table1.column1
WHERE table1.column IS NULL;

Examples of the MySQL delete join

1) Delete the data using the inner join example shows below.

Execute the below query to delete inner join. It would be best if you will use the "ON" keyword to remove a specific column. The "DELETE" keyword is required for deleting given table data. The "WHERE" clause is used to remove the specific row.

mysql> DELETE department, subject from department 
INNER JOIN subject 
ON department.department_id = subject.subject_id 
WHERE department.department_id = 2;

OUTPUT

Execute the below query to get updated table data.

mysql> Select * from department;
DELETE JOIN

The above image shows that the query deletes the second row using the inner join and the delete keyword.

2) Delete the data using the left join example shows below.

Execute the below query to delete left join. It becomes helpful if we use the "ON" keyword to remove a specific column.

mysql> DELETE department, subject from department LEFT JOIN subject 
ON department.department_id = subject.subject_id 
WHERE department.department_id = 2;

OUTPUT

Execute the below query to get updated table data.

mysql> Select * from department;
DELETE JOIN

The above image deletes the table row using the left join and the delete keyword. Here, table data removes the second row.

3) Delete the data using the right join example shows below.

Execute the below query to delete the right join. It is useful if we use the "ON" keyword to remove a specific column.

mysql> DELETE department, subject from department RIGHT JOIN subject 
ON department.department_id = subject.subject_id 
WHERE department.department_id = 2;

OUTPUT

Execute the below query to get updated table data.

mysql> Select * from department;
DELETE JOIN

The above image deletes the table row that contains the department_id 2. The result displays entire rows except for the second department_id.

4) Delete the data using the inner join example shows below.

Execute the below query to delete the required data in which the "WHERE" clause and "ON"   operator use the "less than" condition.

mysql> DELETE department, subject from department INNER JOIN subject 
ON department.department_id < subject.subject_id 
WHERE department.department_id < 3;

OUTPUT

Execute the below query to get updated table data.

mysql> Select * from department;
DELETE JOIN

In the above image, we can see that the data less than the "3" department_id is removed successfully. Hence, the table displays entire rows except less than the third department_id.

5) Delete the data using the left join example shows below.

Execute the below query to delete the required data in which the "WHERE" clause and "ON"   operator use the "less than" condition.

mysql> DELETE department, subject from department LEFT JOIN subject 
ON department.department_id < subject.subject_id 
WHERE department.department_id < 3;

OUTPUT

Execute the below query to get updated table data.

mysql> Select * from department;
DELETE JOIN

In the above image, we can see that the data less than the "3" department_id is removed successfully. Hence the table displays entire rows except less than the third department_id.

6) Delete the data using the left join example shows below.

Execute the below query to delete the required data in which the "WHERE" clause uses the "equal to" condition and the "ON" operator uses the ‘greater than’ condition.

mysql> DELETE department, subject from department LEFT JOIN subject 
ON department.department_id > subject.subject_id 
WHERE department.department_id = 6 OR subject.subject_id = 2;

OUTPUT

Execute the below query to get updated table data.

mysql> Select * from department;
DELETE JOIN

The above image deletes table data using the left join and deletes keyword. Here, table data removes data "equal to" the "6" department_id.