![]() ![]() Assume that there is an additional column in the tasks table which shows the status of a particular task. Example #4 – SQL Query to Illustrate DELETE FULL JOINįinally, let’s try performing a delete operation with full join on employees and task tables. It is helpful in deleting all the data as FULL join returns all records from both the tables and will substitute the non-matching columns with NULL values. DELETE with FULL JOINĭELETE with FULL JOIN is similar to an INNER join. Let’s check if everything has been successfully deleted or not from the department’s table.Īs you can see we have successfully achieved it. WHERE t2.departmentid NOT IN (SELECT t1.departmentid FROM employees as t1) Let’s now delete the department from the department table using a DELETE with RIGHT JOIN.įROM employees AS t1 RIGHT JOIN department AS t2 Since we have already deleted everything pertaining to the sales and marketing department in the tasks and employee table. Example #3 – SQL Query to Illustrate DELETE RIGHT JOIN DELETE with RIGHT JOINĪ SQL DELETE RIGHT join should be used in cases when we want to delete all data from one table (right) and only matching data from the other table. Let’s check if all the tasks pertaining to the sales and marketing department have been removed from the table.Īs you can see it has been successfully deleted. So, we will be using a LEFT JOIN.įROM tasks AS t1 LEFT JOIN department AS t2 ![]() We want to delete everything from the first table “tasks” but not from “departments”. The following SQL code will delete all the tasks for the sales and marketing department. The tasks table looks something like this: taskid Then we might further want to remove data pertaining to it from other database tables like tasks. Since in the previous steps, we have already shut down the sales and marketing department. Example #2 – SQL Query to Illustrate DELETE LEFT JOIN We can check using the SELECT statement that all the information for sales and marketing employees has been successfully deleted.Ī SQL DELETE LEFT join should be used in cases when we want to delete all data from one table(left) and only matching data from the other table. WHERE t2.departmentname = 'Sales & Marketing' How will you perform this operation? It can be done by writing multiple sub-queries but we will do this simply by writing a delete join statement.įROM employees AS t1 INNER JOIN department AS t2 It would like to remove all the employees from this department from the company’s database. Suppose in this example, a company wants to shut down the “sales & marketing” department. The data in the employee’s table is as follows: employeeidĮxample #1 – SQL Query to Illustrate DELETE INNER JOIN The data in the department’s table look something like this: departmentid DELETE with INNER JOINĭelete with inner join is used to delete all the records from the first table and all the matching records from the second table. In order to understand the concept better, we will take the help of two tables, Employees (this contains personal details of all the employees) and departments (it contains details like department id, name, and its hod). Examples of SQL Delete Joinįollowing are the different examples of SQL Delete Join. Of the above-mentioned parameters, all the parameters except the WHERE clause are mandatory. Go ahead, we will be understanding delete joins in detail one by one. WHERE condition: It is used to specify the conditions to filter records.It can be a pair of primary and foreign keys. ON t1.column_name = t2.column_name: It is used to specify the common conditions on which the two tables will be joined.To join, you may choose from INNER, LEFT, FULL and RIGHT joins. Here, table_name1 is the name of the left table and table_name2 is the name of the right table. FROM table_name1 as t1 JOIN table_name2 as t2: It is used to specify the source from which data has to be fetched and deleted.Here, you may choose from the first table’s instance t1 and the second table’s instance t2. DELETE t1: It is used to delete the required table from the database. ![]()
0 Comments
Leave a Reply. |