What is the main difference between the IN and EXISTS clause in sub queries??
The main difference between the IN and EXISTS predicate in sub query is the way in which the query gets executed. IN — The inner query is executed first and the list of values obtained as its result is used by the outer query. The inner query is executed for only once. EXISTS — The first row from the outer query is selected, then the inner query is executed and, the outer query output uses this result for checking. This process of inner query execution repeats as many no .of times as there are outer query rows. That is, if there are ten rows that can result from outer query, the inner query is executed that many no. of times. Question: TRUNCATE TABLE EMP; DELETE FROM EMP; Will the outputs of the above two commands differ Answer: The difference is that the TRUNCATE call cannot be rolled back and all memory space for that table is released back to the server. TRUNCATE is much faster than DELETE and in both cases only the table data is removed, not the table structure.
