Assignment: DELETE, TRUNCATE, IN, EXISTS

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