1) Delete from account;
This will delete all records from the account table. If you are going to use this and you know you arent' going to roll it back, you should use 'truncate table account' It will be MUCH faster if the account table is big because it does not generate redo. Also, it will reset the highwatermark on the table which is an important thing
2) delete from account a where account_number in (select distinct account_number from another_table)
this will delete all records in the account table that have a matching account number in another_table.
3) delete from account a
where a.account_number = (select b.account_number
from another_table b
where a.account_number = b.account_number)
This query will do the same thing as #2, but it will give errors if there is more than one row to delete!
The 2nd DELETE statement is called uncorrelated subquery.
The inner query is executed only once, and for each row in the outer query the account is checked, if it matches, then oracle deletes the row, otherwise it reads the next row in the outer query.
The 3rd DELETE statement is called correlated subquery. The inner query is executed as many times as the number of rows obtained in the outer query. This will take long time to process.
I hope you now understand the sub query concept in oracle.