(Continued from previous part...)

How you will avoid your query from using indexes?

By changing the order of the columns that are used in the index, in the Where condition, or by concatenating the columns with some constant values.

What is a OUTER JOIN?

An OUTER JOIN returns all rows that satisfy the join condition and also returns some or all of those rows from one table for which no rows from the other satisfy the join condition.

Which is more faster - IN or EXISTS?

Well, the two are processed very differently.
Select * from T1 where x in ( select y from T2 )
is typically processed as:
select *
from t1, ( select distinct y from t2 ) t2
where t1.x = t2.y;

The sub query is evaluated, distinct’ed, indexed (or hashed or sorted) and then joined to the original table — typically. As opposed to select * from t1 where exists ( select null from t2 where y = x )

That is processed more like:
for x in ( select * from t1 )
loop
if ( exists ( select null from t2 where y = x.x )
then
OUTPUT THE RECORD
end if
end loop

It always results in a full scan of T1 whereas the first query can make use of an index on T1(x). So, when is where exists appropriate and in appropriate? Lets say the result of the sub query ( select y from T2 ) is “huge” and takes a long time. But the table T1 is relatively small and executing ( select null from t2 where y = x.x ) is very fast (nice index on t2(y)). Then the exists will be faster as the time to full scan T1 and do the index probe into T2 could be less then the time to simply full scan T2 to build the sub query we need to distinct on.

Lets say the result of the sub query is small — then IN is typically more appropriate. If both the sub query and the outer table are huge — either might work as well as the other — depends on the indexes and other factors.

When do you use WHERE clause and when do you use HAVING clause?

The WHERE condition lets you restrict the rows selected to those that satisfy one or more conditions. Use the HAVING clause to restrict the groups of returned rows to those groups for which the specified condition is TRUE.

There is a % sign in one field of a column. What will be the query to find it?

SELECT column_name FROM table_name WHERE column_name LIKE ‘%\%%’ ESCAPE ‘\’;

(Continued on next part...)