DELETE Query

The DELETE Function

The DELETE command is used to remove one or more rows from a table. Unlike DROP, which removes the entire table, DELETE only removes the data.

Basic Syntax

DELETE FROM table_name
WHERE kondisi;

DELETE with WHERE

Delete a specific employee based on a condition:

DELETE FROM EMPLOYEE
WHERE ID_EMPLOYEE = 5;

Only the employee with ID 5 is deleted from the table.

DELETE with Complex Conditions

Use logical operators to delete multiple rows at once:

DELETE FROM EMPLOYEE
WHERE DEPARTMENT = 'FINANCE' AND STATUS = 'Inactive';

All inactive employees in the FINANCE department will be deleted.

DELETE with IN

Delete several employees based on a list of IDs:

DELETE FROM EMPLOYEE
WHERE ID_EMPLOYEE IN (10, 15, 20);

DELETE with a Subquery

DELETE FROM EMPLOYEE
WHERE DEPARTMENT = (
    SELECT DEPT_CODE FROM DEPARTMENT
    WHERE NAME = 'WAREHOUSE'
);

This deletes all employees in the warehouse department.

DELETE without WHERE

WARNING: Running DELETE without WHERE will remove all rows in the table.

-- DANGER! Deletes all data!
DELETE FROM EMPLOYEE;

The EMPLOYEE table will be empty (the table structure remains). The data cannot be recovered unless you have a backup.

Differences between DELETE, TRUNCATE, and DROP

Command	Function	Can ROLLBACK?	Reset Auto Increment
`DELETE`	Deletes specific rows	Yes	No
`TRUNCATE`	Deletes all rows	Depends on the DBMS	Yes
`DROP`	Deletes the entire table	Depends on the DBMS	Yes

-- Delete all data with TRUNCATE (faster)
TRUNCATE TABLE EMPLOYEE;

Scenario Example

The EMPLOYEE table before the operation:

ID	NAME	STATUS
1	Mike	Active
2	John	Inactive
3	Anna	Inactive
4	Dana	Active

-- Delete inactive employees
DELETE FROM EMPLOYEE
WHERE STATUS = 'Inactive';

-- Result:
-- | 1 | Mike | Active |
-- | 4 | Dana | Active |

Safety Tips

Always run a SELECT with the same conditions before DELETE
Use transactions so you can ROLLBACK if something goes wrong
Consider Soft Delete (adding an IS_DELETED column) as an alternative
Back up your data before bulk DELETE operations

By mastering DELETE, we can now manage the full data lifecycle: reading (SELECT), updating (UPDATE), and deleting (DELETE). Next, we will learn how to search for data with a specific pattern using the LIKE clause.