SQL DELETE Statement – The Complete Guide

The SQL DELETE statement allows you to delete records from a table. I’ll explain more about the SQL DELETE statement in this article.

This article covers Oracle, SQL Server, MySQL, and PostgreSQL.


What Is The SQL DELETE Statement?

The SQL DELETE statement, or delete query, is a statement you can run to delete records from a table.

A WHERE clause is used to specify the criteria, and any rows matching these criteria will be deleted. You can use it to delete a single record, multiple records, or all records in a table.


How Can I Write a DELETE Query in SQL?

The DELETE query in SQL has some pretty simple syntax:

DELETE FROM [ table | (subquery) ] [alias]
[WHERE condition];

Let me explain this a bit further.

You start with DELETE FROM. There is no need to specify the columns here – we’re working on deleting rows so the columns don’t matter.

Then, you specify either a table or a subquery. The table here can be either a table, a view, or a materialized view.

Then, you can give the table an alias, to help when writing your WHERE condition.

After this, you can write WHERE then a condition. The condition allows you to specify which rows to delete. If you only want to delete some of the rows in a table, you’ll need the WHERE condition. If you want to delete all rows in a table, don’t use a WHERE condition.


Examples of an SQL DELETE Query

Let’s see some examples. First, let’s see some sample data that we can run our DELETE SQL statements on.

SELECT student_id, first_name, last_name
FROM student;
1 John Smith
2 Susan Johnson
3 Tom Capper
4 Mark Holloway
5 Steven Webber
6 Julie Armstrong
7 Michelle Randall
8 Andrew Cooper
9 Robert Pickering
10 Tanya Hall
11 Jarrad Winston
12 Mary Taylor
100 Mark Anderson
102 John Rogers


Example 1 – Simple Delete

This is an example of deleting a single record. Related: How to Delete a Row in SQL.

WHERE student_id = 1;

The record that has the student_id of 1 is deleted. All other rows remain in the table.

Example 2 – Using Two Conditions

This example uses two conditions in the WHERE clause.

WHERE enrolment_date > '10-JAN-2015'
AND fees_paid > 400;
1 row(s) deleted.

As you can see, 1 row has been deleted because 1 row matched the WHERE condition.


Can You Delete From Multiple Tables?

Yes, you can in MySQL, but not in other databases.


MySQL Delete From Multiple Tables

To delete records from multiple tables in MySQL, you mention both tables in your Delete statement.

The syntax looks like this:

DELETE table1, table2
FROM table1
INNER JOIN table2 ON table1.field = table2.field
WHERE condition;

This will delete records from both tables that match your condition.

Let’s see an example.

We have two tables, person and company, which contain a few related records.

  firstname VARCHAR(100),
  company_id INT

CREATE TABLE company (
id INT,
company_name VARCHAR(100)

INSERT INTO person (firstname, company_id) VALUES
('John', 1), ('Sarah', 2), ('Mark', 1), ('Tania', 3);

INSERT INTO company (id, company_name) VALUES
(1, 'something'), (2, 'another co'), (3, 'west'), (4, 'northern');

Here’s what the data looks like.

Person table:

firstname company_id
John 1
Sarah 2
Mark 1
Tania 3

Company table:

company_id company_name
1 something
2 another co
3 west
4 northern

You can delete all company and person records that match a specific company using this statement.

DELETE person, company
FROM person
INNER JOIN company ON person.company_id = company.id
WHERE company.id = 1;

The records are matched between the two tables, and filtered to where company_id = 1;

After this statement is run, these are the results of the tables.

Person table:

firstname company_id
Sarah 2
Tania 3

Company table:

company_id company_name
2 another co
3 west
4 northern

Notice how the company ID of 1 is removed, and all person records with a company of 1 have been removed.

Delete from Multiple Tables in Other Databases

In non-MySQL databases, this statement won’t work. If you want to delete from multiple tables, you need to write multiple DELETE statements.

DELETE from student
WHERE student_id = 1;

DELETE from student_subject
WHERE student_id = 1;


Can You Delete Data Based On Data In Another Table?

Yes, you can.

Let’s explain what I mean.

Say, for example, you had your student table. And let’s say you wanted to delete all student records that were not in any subjects. This data, though, is in the student_subject table, which links students to subjects.

But, we can’t specify more than one table in the SQL DELETE statement, right?

Actually, you can. You just can’t delete from more than one table at a time.

You can do that using a join or using a subquery. The method you use may depend on the vendor:

  • Oracle: Subquery only, does not support join
  • SQL Server: Subquery or join
  • MySQL: Subquery or join
  • PostgreSQL: Subquery or join


SQL Delete With Subquery

Let’s see how we can do this.

WHERE student_id NOT IN (
  SELECT student_subject.student_id
  FROM student_subject

This query first looks up student_id values in the student_subject table. Then, it deletes records from the student table where the student_id is not in that list.

The method of using a subquery to limit values works in a similar way to the SELECT query. You can use IN, EXISTS, and NOT EXISTS in the same way (read more about these operators here).


SQL Delete With Join

To delete from one table based on data in another table, you can delete using a join. This will work in MySQL, PostgreSQL, and SQL Server.

You can join two tables together in the same way as you would with a SELECT statement, but you just need to specify the name of the table to delete from:

DELETE student
FROM student
INNER JOIN student_subject ON student.student_id = student_subject.student_id
WHERE student_subject.subject_id = 1;

This will delete all student records that have a student_subject record with a subject_id of 1.

You can also use table aliases to make the query easier to read and write:

DELETE student
FROM student st
INNER JOIN student_subject ss ON st.student_id = ss.student_id
WHERE ss.subject_id = 1;

This will give you the same result.


Can I See What I’m Deleting Before It Is Deleted?

Yes, you can.

If you want to be sure you’re deleting the right records, you can check what you delete before you actually delete the records.

To do this, simply copy the DELETE statement and run it as a SELECT statement first – with the same WHERE clause and everything.

As an example, if we wanted to delete the students that were not enrolled in a subject (just like the previous example), we can check we’re deleting the right records first.

The SELECT query is here:

SELECT * FROM student
WHERE student_id NOT IN (
  SELECT student_subject.student_id
  FROM student_subject

And, the Oracle DELETE query to do that is here:

WHERE student_id NOT IN (
  SELECT student_subject.student_id
  FROM student_subject

As you can see, I just replaced the DELETE with a SELECT *.


My SQL DELETE Statement Is Too Slow!

Are you trying to delete data from a table but it’s too slow?

Well, there are a few things you can do.

First, if you’re deleting all of the data in the table, it may be better to use the TRUNCATE TABLE statement. It’s faster (because it doesn’t write to a log and allow for rollbacks), but you can’t undo it, so you need to be sure. You also can’t specify a WHERE condition with a TRUNCATE TABLE statement.

If you can’t use TRUNCATE, you can try to filter your data differently or use indexes on your table.

Another method is actually creating a new table with the data you want to keep. You can do this by :

  • Write a CREATE TABLE AS SELECT method and then specifying the WHERE clause for the records you want to keep
  • Delete the original table
  • Rename the new table to the old table


Summary of Differences Between Vendors

There are some differences between the way the DELETE statement works with each vendor:

Criteria Oracle SQL Server MySQL PostgreSQL
Basic DELETE Follows standard Follows standard Follows standard Follows standard
Delete with subquery Allowed Allowed Allowed Allowed
Delete with join Not allowed Allowed Allowed Allowed



So, that’s how you run an Oracle DELETE SQL statement. You can delete a single row, multiple rows, or the entire table, using WHERE conditions which can involve subqueries.

13 thoughts on “SQL DELETE Statement – The Complete Guide”

  1. one great option is to get the number of deleted rows:

    delete from ….
    where ….
    returning count(1) into :variable

    I use this in production,so I always know how many records where deleted in one simple statement

    good post

  2. About “Delete is too slow”: if a lot of other tables reference your table via Foreign Keys (with cascade delete) than deletion may be very slow too

  3. Thank you for the information.
    This is really interesting and informative.

    An analogue of “Delete with join” in Oracle with some restrictions can be “delete with inline view”.

    DELETE FROM (SELECT li.order_nbr order_nbr, li.part_nbr part_nbr

    FROM cust_order co INNER JOIN line_item li

    ON co.order_nbr = li.order_nbr

    INNER JOIN part p

    ON li.part_nbr = p.part_nbr

    WHERE co.cancelled_dt IS NULL AND co.ship_dt IS NULL

    AND p.inventory_qty = 0) suspended_orders;

  4. Hi,

    Can you please help me with the SQL query to delete records older than 13 months. Basically, I have a column named load_timestamp which will populate the sysdate whenever the record is created. My requirement is to delete the records older than 13 months (i.e load_timestamp – 13 months).

    1. Sure, the query depends on which database you’re using (Oracle, SQL Server, MySQL, etc). In SQL Server, it could be something like this:
      DELETE FROM table WHERE DATEDIFF(month, load_timestamp, GETDATE()) > 13;
      It would be slightly different in each database – not all of them have a DATEDIFF function.
      I would recommend writing a SELECT query using the same criteria first, to ensure you are deleting the correct records.

  5. Hi, thanks for this post sir!

    If I have to wordpress tables (jlc_post and jlc_postmeta), and I buid this query:

    SELECT po.post_title,alm.meta_key,alm.meta_value,pmstock.meta_key,pmstock.meta_value
    FROM jlc_posts po
    LEFT JOIN jlc_postmeta alm ON po.id = alm.post_id
    LEFT JOIN jlc_postmeta pmstock ON po.id = pmstock.post_id
    where alm.meta_key=’almacen’ and (alm.meta_value=1 or alm.meta_value=2)
    AND (pmstock.meta_key=’_stock’ and pmstock.meta_value=0)
    order by po.post_title ASC;

    The question is: how can I convert this to DELETE statment?

    Thanks again sir!

Leave a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.