SQL DELETE Statement

The SQL DELETE statement is used to delete one or more records from a table.

Syntax

The syntax for the DELETE statement can be given with:

DELETE FROM table_name
[WHERE conditions];

Parameters:

• table_name: The table that you want to remove records from.
• WHERE conditions: Optional. The conditions that must be satisfied for the delete to be executed. If no conditions are specified, all records in the table will be deleted.

Note:

• You do not need to list columns in the DELETE statement since it removes the entire record from the table.
• The WHERE clause is the DELETE statement is used to indicate which row or rows should be removed. Even if it is optional, all the records will be deleted permanently from the table if you omit the' WHERE' clause.

Demo Table

Consider that we have a table called "employee" with the following data:

Delete Records Based on Conditions Example

The following SQL statement will delete all employees from the "employee" table whose "first_name" field equal to "James".

DELETE FROM employee
WHERE first_name = 'James';

After running the above SQL statement, the selection from the "employee" table will look as follows:

+--------+-------------+-----------+------------+------------+-----------+------------------+
| emp_no | first_name  | last_name | birth_date | hire_date  | salary    | dept_name        |
+--------+-------------+-----------+------------+------------+-----------+------------------+
| 1002   | Maria       | Martinez  | 1993-06-13 | 2018-10-08 | 9000      | Development      |
| 1003   | Andrew      | Rodriguez | 1994-10-25 | 2019-01-23 | 9500      | Finance          |
| 1004   | Robert      | Hernandez | 1995-09-07 | 2019-09-08 | 9600      | Development      |
| 1005   | Donna       | Williams  | 1996-03-15 | 2012-06-06 | 7400      | Finance          |
| 1007   | Stephanie   | Miller    | 1998-01-17 | 2009-12-12 | 8500      | Human Resources  |
+--------+-------------+-----------+------------+------------+-----------+------------------+

Delete All Records Example

As mentioned above, if you do not specify the WHERE clause in the DELETE statement, all the records from the table will be removed. The target table will not be removed, which means it is just the data that will be deleted, and the table structure, attributes, and indexes will remain intact.

The following SQL statement will delete all the records from the "employee" table.

DELETE FROM employee;

After the execution, if you try to select data from the "employee" table, you will get an empty result set.