The DELETE command allows you to remove rows from a table. This command deletes the specified rows from the table based on the given condition.


The syntax for a DELETE statement is:

DELETE FROM table_name [AS alias] WHERE condition


  • FROM table_name: This is where you specify the table that you want to delete rows from.
  • AS alias: This is an optional part of the syntax. You can create a temporary name for tables using it. Anywhere that table is then referred to in the query can use this alias.
  • WHERE condition: This is where you specify the conditions that must be met for rows to be deleted. If the condition is not met, no rows will be deleted.


The following SQL statement deletes the employee with id 3 from the employees table:

DELETE FROM employees
WHERE id = 3;

In more complex scenarios, the alias can be quite useful. For example, if you have an employees table with a self-referential manager_id column, you might want to delete all employees who have a manager with a salary less than 50000. You could accomplish this with a DELETE statement using an alias like this:

DELETE FROM employees AS e1
WHERE manager_id IN (
    SELECT id FROM employees AS e2
    WHERE e2.salary < 50000

In this query, e1 and e2 are aliases for the employees table. The e2 alias is used in the inner SELECT query to find the ids of all managers with a salary less than 50000. The e1 alias is then used in the outer DELETE query to delete all employees whose manager_id matches one of these ids.

To delete all records from a table, you can use the DELETE statement without a WHERE clause:

DELETE FROM employees;

This statement will delete all records in the employees table.

Comparison with TRUNCATE

The DELETE statement and the TRUNCATE statement are both used to delete data from a table. However, they work in different ways:

  • The DELETE command removes rows one at a time and records an entry in the transaction log for each deleted row. Because of this, it’s slower than TRUNCATE when it comes to deleting all rows from a table. However, it allows you to specify a WHERE clause to filter which rows should be removed.

  • The TRUNCATE command is a faster way to empty a table completely because it doesn’t log individual row deletions. However, it doesn’t allow for the specification of a WHERE clause and removes all rows.

Learn more about the TRUNCATE command in the corresponding documentation.


The DELETE statement in Polypheny supports rollback. This means that if you delete some rows and then decide that you need to undo that operation, you can roll back the transaction to get the deleted rows back, as long as the transaction hasn’t been committed yet. This is another feature that differentiates DELETE from TRUNCATE, as the TRUNCATE operation cannot be rolled back.

Although DELETE operations can be rolled back, it is essential to use this feature with caution. Once a transaction is committed, the deleted data cannot be recovered.
© Polypheny GmbH. All Rights Reserved.