SQL DELETE Query

What is DELETE query?

As name implies, erase records from table by using DELETE query. That means, you can delete existing records or rows from database tables. You may either delete all records or specified records by using “where” clause with delete queries in sql.

There are two ways to delete records from database tables as mentioned above.

  • Delete all records
    Here, you may use basic syntax to delete records from database tables. You don’t need to use where clause in this case.
  • Delete specified records
    Here, you can use where clause with condition based on requirement so that will delete only specified records from database tables.

Why DELETE query needed?

Sometime it may happen that you need to delete records which are not necessary to store into database table, like unwanted, test or junk data. In that case, you may easily delete those records and free the database space for other purpose or to store other data into database tables.

Syntax for DELETE query

As mentioned above, there are two way to delete records from database tables with following syntax.

  • Delete all records
    DELETE FROM TABLENAME
  • Delete specified records
    DELETE FROM TABLENAME
    WHERE CONDITION;
  • Let’s understand DELETE query with examples

Examples 1: Delete with where clause

Data before DELETE query

Id Employee Code First Name Last name Gender Birth Date
1 EMP_1001 John Peter Male 10-11-1982
2 EMP_1002 Josh Wills Male 09-07-1980
3 EMP_1003 Jeremy Parkar Female 08-09-1990


DELETE FROM Employee
WHERE Id = 1;


Data after DELETE query

Id Employee Code First Name Last name Gender Birth Date
2 EMP_1002 Josh Wills Male 09-07-1980
3 EMP_1003 Jeremy Parkar Female 08-09-1990

Here, in above table, we can see record get deleted where Id was having 1 value. That’s because of we have used where clause and specified condition which record we want to delete.

Examples 2: Delete with where clause and multiple values with one colunms by using IN

Data before DELETE query

Id Employee Code First Name Last name Gender Birth Date
1 EMP_1001 John Peter Male 10-11-1982
2 EMP_1002 Josh Wills Male 09-07-1980
3 EMP_1003 Jeremy Parkar Female 08-09-1990


DELETE FROM Employee
WHERE Id IN (1,2);


Data after DELETE query

Id Employee Code First Name Last name Gender Birth Date
3 EMP_1003 Jeremy Parkar Female 08-09-1990

Here, in above table, we can see record get deleted where Id was having value 1 and 2. That’s because of we have used where clause and specified condition using IN to delete multiple records which we want.

Examples 3: Delete without where clause (DELETE All)

Data before DELETE query

Id Employee Code First Name Last name Gender Birth Date
1 EMP_1001 John Peter Male 10-11-1982
2 EMP_1002 Josh Wills Male 09-07-1980
3 EMP_1003 Jeremy Parkar Female 08-09-1990

DELETE FROM Employee


Data after DELETE query

Here, no records exist after DELETE query executed. It deletes all records from database table.

Important: When you use DELETE query, be careful, because it will permanent delete records from database table either you use where clause or not.