The DELETE command in SQL

The SQL DELETE command is used to remove the records from the relational database tables permanently.

Using DELETE command without the WHERE clause removes table data completely. For example:

DELETE FROM table_name;

This is the simplest syntax of using the DELETE SQL statement.

You should always use WHERE clause in order to remove only specific rows from the table. For example:

DELETE FROM table_name

WHERE col_name = some_value;

If you require removing the table data completely then consider using the TRUNCATE statement. This is faster in execution as compared to DELETE FROM statement if the task is to remove table data completely.

Note that, the DELETE statement only removes table data. The table structure, constraints etc. remain in place.

The table data removed after using the DELETE command can be rolled back.

The next section shows using DELETE statement with various examples.

The example of deleting a row

In the first example of a delete query in SQL, I will remove only one row from the table. For that, the DELETE statement is used with the WHERE clause. The condition is set in the WHERE clause to remove the record of an employee which id = 10.

The DELETE query:

DELETE FROM sto_employees

WHERE id = 10;

The table data before and after delete query:

SQL DELETE

How to empty a table?

As mentioned earlier, the DELETE command removes the table data completely when used without a WHERE clause. The query below shows removing the table data completely.

I wrote four queries as follows:

The first query retrieves the complete data from the sto_employees table.

While the second query is the “DELETE FROM” without the WHERE clause. This is placed inside the ROLLBACK block in MS SQL Server. So, after emptying the table the ROLLBACK command is executed.

Before ROLLBACK, the table data is displayed in the graphic below.

The last query is again complete table data retrieval after the ROLLBACK. See the queries and resultsets:

SELECT * FROM sto_employees;



DECLARE @DelROLLBACKRemoval varchar(20) = 'RollbackDelete'; 



BEGIN TRAN @DelROLLBACKRemoval 

    DELETE FROM sto_employees;

                SELECT * FROM sto_employees;                              

ROLLBACK TRAN @DelROLLBACKRemoval; 



SELECT * FROM sto_employees;

SQL DELETE Empty

You should use TRUNCATE statement for removing table data completely. For learning more about it, go to TRUNCATE tutorial.

The demo of using IN clause in the DELETE statement

In the WHERE clause with DELETE statement, you may use various operators for removing the specific records.

The next query uses IN operator where I specified a few employee names to remove rows from the table.

The query:

DELETE FROM sto_employees

WHERE emp_name IN ('John', 'Jimmy', 'Shaun');

SQL DELETE IN operator

You may see the difference before and after executing the DELETE command. In the second table, three records are removed from the employee’s table.

Using BETWEEN operator with the DELETE command

Similarly, you may specify the range of values by using the SQL BETWEEN operator in the DELETE command for removing records.

The following query removes data where I used the BETWEEN operator for emp_salary column:

DELETE FROM sto_employees

WHERE emp_salary BETWEEN 4000 AND 5000;

SQL DELETE BETWEEN operator

An example of using JOIN with DELETE statement

The JOIN clause enables combining different tables in the RDBMS that are related by common fields. The JOIN clause is generally used with the SELECT statement for retrieving data.

You may also use JOIN clause with the DELETE statement. See an example query below where I joined two tables – sto_employees and sto_orders by an INNER JOIN clause.

DELETE EMP FROM sto_employees EMP

INNER JOIN sto_orders ORD  ON EMP.id=ORD.emp_id

WHERE emp_id IN (1,3,5) AND emp_name = 'Jimmy';

SQL DELETE JOIN

Note the table alias used after the DELETE command.

Author - Atiq Zia

Atiq is the writer at jquery-az.com, an online tutorial website started in 2014. With a passion for coding and solutions, I navigate through various languages and frameworks. Follow along as we unravel the mysteries of coding together!