The DELETE statement is used to remove records from the database. As with UPDATE, there are two ways of deleting: using sub-select or using another table or tables. The sub-select syntax is as follows:

DELETE FROM <table_name> [WHERE <condition>];

Records that follow the condition will be removed from the table. If the WHERE clause is omitted, then all the records will be deleted.

DELETE based on another table is similar to using the FROM clause of the UPDATE statement. Instead of FROM, the USING keyword should be used because FROM is already used in the syntax of the DELETE statement:

car_portal=> DELETE FROM car_portal_app.a USING car_portal_app.b WHERE a.a_int=b.b_int;
DELETE 6

The preceding statement will delete all the records from a when there is a record in b with the same value of the numeric field. The output of the command indicates the number of records that were deleted.

The previous command is equivalent to the following:

DELETE FROM car_portal_app.a WHERE a_int IN (SELECT b_int FROM car_portal_app.b);

As well as UPDATE and INSERT, the DELETE statement can return deleted rows when the RETURNING keyword is used:

car_portal=> DELETE FROM car_portal_app.a RETURNING *;
 a_int | a_text
-------+--------
     0 | one
     0 | two
...