Teachnique
      CourseRoadmaps
      Login

      HomeIntroductionFeaturesVersionsVariablesInstallationAdministrationPHP SyntaxNode.js SyntaxJava SyntaxPython SyntaxConnectionWorkbench

      Create DatabaseDrop DatabaseSelect DatabaseShow DatabaseCopy DatabaseDatabase ExportDatabase ImportDatabase Info

      Create UsersDrop UsersShow UsersChange PasswordGrant PrivilegesShow PrivilegesRevoke PrivilegesLock User AccountUnlock User Account

      Create TablesShow TablesAlter TablesRename TablesClone TablesTruncate TablesTemporary TablesRepair TablesDescribe TablesAdd/Delete ColumnsShow ColumnsRename ColumnsTable LockingDrop TablesDerived Tables

      QueriesConstraintsInsert QuerySelect QueryUpdate QueryDelete QueryReplace QueryInsert IgnoreInsert on Duplicate Key UpdateInsert Into Select

      Create ViewsUpdate ViewsDrop ViewsRename Views

      IndexesCreate IndexDrop IndexShow IndexesUnique IndexClustered IndexNon-Clustered Index

      Where ClauseLimit ClauseDistinct ClauseOrder By ClauseGroup By ClauseHaving ClauseAND OperatorOR OperatorLike OperatorIN OperatorANY OperatorEXISTS OperatorNOT OperatorNOT EQUAL OperatorIS NULL OperatorIS NOT NULL OperatorBetween OperatorUNION OperatorUNION vs UNION ALLMINUS OperatorINTERSECT OperatorINTERVAL Operator

      Using JoinsInner JoinLeft JoinRight JoinCross JoinFull JoinSelf JoinDelete JoinUpdate JoinUnion vs Join

      Unique KeyPrimary KeyForeign KeyComposite KeyAlternate Key

      TriggersCreate TriggerShow TriggerDrop TriggerBefore Insert TriggerAfter Insert TriggerBefore Update TriggerAfter Update TriggerBefore Delete TriggerAfter Delete Trigger

      Data TypesVARCHARBOOLEANENUMDECIMALINTFLOATBITTINYINTBLOBSET

      Regular ExpressionsRLIKE OperatorNOT LIKE OperatorNOT REGEXP Operatorregexp_instr() Functionregexp_like() Functionregexp_replace() Functionregexp_substr() Function

      Fulltext SearchNatural Language Fulltext SearchBoolean Fulltext SearchQuery Expansion Fulltext Searchngram Fulltext Parser

      Date and Time FunctionsArithmetic OperatorsNumeric FunctionsString FunctionsAggregate Functions

      NULL ValuesTransactionsUsing SequencesHandling DuplicatesSQL InjectionSubQueryCommentsCheck ConstraintsStorage EnginesExport Table into CSV FileImport CSV File into DatabaseUUIDCommon Table ExpressionsOn Delete CascadeUpsertHorizontal PartitioningVertical PartitioningCursorStored FunctionsSignalResignalCharacter SetCollationWildcardsAliasROLLUPToday DateLiteralsStored ProcedureExplainJSONStandard DeviationFind Duplicate RecordsDelete Duplicate RecordsSelect Random RecordsShow ProcesslistChange Column TypeReset Auto-IncrementCoalesce() Function

      Useful FunctionsStatements ReferenceQuick GuideUseful ResourcesDiscussion

      Feedback

      Submit request if you have any questions.

      Course
      Delete Join

      MySQL Tutorial

      This SQL tutorial is structured for beginners to guide them from the foundational concepts to advanced data manipulation and querying techniques in SQL. By the end of this tutorial, you will have developed a robust understanding of SQL, equipping you with the knowledge to efficiently manage and analyze data across various database systems. This guide sets the stage for your growth into a skilled data professional, ready to tackle complex data challenges and contribute to the field of data analysis and database management.

      Delete Join

      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

      PHPNodeJSJavaPython
      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);
      

      Example

      Following are the programs −
      PHPNodeJSJavaPython
      $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!.