Assignment: DELETE, TRUNCATE, IN, EXISTS

DELETE TRUNCATE
Problem-1:

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

 

0 Comments