The DELETE statement deletes rows from a table. This statement has two different forms:
DELETE FROM table_name
[WHERE predicate];
DELETE table_name
FROM table_name [,…n]
[WHERE condition];
All rows that satisfy the condition in the WHERE clause will be deleted. Explicitly naming columns within the DELETE statement is not necessary (or allowed), because the DELETE statement operates on rows and not on columns.
NOTE The TRUNCATE TABLE statement, which is semantically equivalent to UPDATE, will be explained in the next section.
Example 7.16 shows an example of the first form of the DELETE statement.
Example 7.16
Delete all managers in the works_on table:
USE sample;
DELETE FROM works_on
WHERE job = ‘Manager’;
The WHERE clause in the DELETE statement can contain an inner query, as shown in Example 7.17.
Example 7.17
Mrs. Moser is on leave. Delete all rows in the database concerning her:
USE sample;
DELETE FROM works_on
WHERE emp_no IN
(SELECT emp_no
FROM employee
WHERE emp_lname = ‘Moser’);
DELETE FROM employee
WHERE emp_lname = ‘Moser’;
Example 7.17 can also be performed using the FROM clause, as Example 7.18 shows. This clause has the same semantics as the FROM clause in the UPDATE statement.
Example 7.18
USE sample;
DELETE works_on
FROM works_on, employee
WHERE works_on.emp_no = employee.emp_no
AND emp_lname = ‘Moser’;
DELETE FROM employee
WHERE emp_lname = ‘Moser’;
The use of the WHERE clause in the DELETE statement is optional. If the WHERE clause is omitted, all rows of a table will be deleted, as shown in Example 7.19.
Example 7.19
USE sample;
DELETE FROM works on;
NOTE There is a significant difference between the DELETE and the DROP TABLE statements. The DELETE statement deletes (partially or totally) the contents of a table, whereas the DROP TABLE statement deletes both the contents and the schema of a table. Thus, after a DELETE statement, the table still exists in the database (although possibly with zero rows), but after a DROP TABLE statement, the table no longer exists.
Source: Petkovic Dusan (2020), Microsoft SQL Server 2019: A Beginner’s Guide, Seventh Edition-McGraw-Hill Education.