Supposed that you have been asked to
remove the data of a table named `purchase` while retaining the schema. You
have two different ways to do that -
i) DELETE FROM `purchase`
ii) TRUNCATE TABLE `purchase`
a) Which one of the above alternatives
will you execute?
b) Explain your decision by describing
the pros and cons of both alternatives.
Solution-1:
a) If I have to decide that which one has
to execute from DELETE or TRUNCATE, defiantly I will execute the DELETE command. Even though the
TRNUCATE command is lightening faster from the DELETE I would like to execute
it for myself.
b)
DELETE is a DML
command, TRUNCATE is DDL command and
TRUNCATE is much faster than DELETE but I will select the DELETE command while
I am removing data from a table.
The main reasons behind executing
DELETE that the TRUNCATE doesn't generate any rollback data, it just
deallocates the data pages used by the table. If
I am in a transaction and want to "undo"
this delete, I need to use DELETE FROM, which gives the ability to rollback.
The DELETE statement removes rows one
at a time and records an entry in the transaction log for each deleted row.
TRUNCATE TABLE removes the data by deallocating the data pages used to store
the table data and records only the page deallocations in the transaction log.
And from a security perspective is that
TRUNCATE requires ALTER privileges
on the table, while DELETE merely requires DELETE
permissions on that table only.
Here I can clear better my point with
some differences between DELETE and TRUNCATE as their Pros & Cons in a
simple table.
|
Object |
DELETE |
TRUNCATE |
|
Command
type |
DML (DELETE
from purchase;) |
DDL (TRUNCATE purchase;) |
|
Where
condition |
Specify
condition for deleting |
Remove
all rows or tuples from a table |
|
Performance |
Slower |
Much
faster than DELETE |
|
Transaction
Log |
For
each deleted row |
One
log for deallocation of the page |
|
Rollback |
Rollback
is possible for undo |
Cannot
rollback the transactions |
|
Remove
data |
Removes
rows one at a time |
Removes
all rows at a time (purge) |
|
//TRUNCATE Query TRUNCATE TABLE purchase; |
//DELETE Query DELETE FROM purchase
WHERE condition; |
Problem-2:
Find out all differences among 'IN' and
'EXISTS' operators with examples.
Solution-2:
To improve the
performance of the query sometimes we use many clauses or operators. Here we
are going to share differences between EXISTS and IN with some of examples that
may clear our point to differentiate the real facts of their performance.
EXISTS looks for a boolean result whereas IN looks for individual values.
IN Clause returns true if the specified values match any of the
value in sub query or the list.
EXISTS Clause returns true value if the sub query
contains any rows.
|
SELECT * FROM EMP_TABLE WHERE EMP_ID IN (SELECT EMP_ID FROM EMP_TABLE WHERE EMP_ID = 100); |
SELECT a.* FROM EMP_TABLE a WHERE EXISTS (SELECT b.* FROM EMP_TABLE b WHERE
b.EMP_ID = 100 AND b.EMP_ID = a.EMP_ID); |
The inner
query will execute first and list of values as its result will be used by the
outer query. The inner query will execute only once at IN Clause.
The first row from the outer query will be selected, then the
inner query will execute. In EXISTS, the rows those are result from outer
query, the inner query will execute that many numbers of times in EXISTS Clause.
|
SELECT e.* FROM EMP_TABLE e WHERE e.DEPT_ID IN (30, 50); |
SELECT e.* FROM EMP_TABLE e WHERE EXISTS (SELECT 1 FROM DEPT_TABLE d WHERE
d.DEPT_ID = 10); |
I am
mentioning here a comparison table in a short way that will help to disclose
some more differences among IN and EXIXTS.
|
EXIXTS |
IN |
|
Cannot compares the
values between the sub-query query and parent query |
can compares the values
between sub-query and parent queries. |
|
If sub-query result is
large, then EXISTS is faster than IN. |
If sub-query result is
less, then IN is faster than EXISTS. |
|
EXISTS is used to
determine if any values are returned or not. |
Whereas, IN can be used
as a multiple OR operator. |
|
The output of EXISTS
can be either FALSE or TRUE |
The output of IN can be
TRUE or NULL or FALSE |
|
Once the single
positive condition is met in the EXISTS condition then the SQL Engine will
stop the process. |
In the IN-condition SQL
Engine compares all the values |