SQL difference between interview questions

• Where vs. Having
• Union vs. Union ALL
• IN vs. Exists
• Group By vs. ORDER BY
• Join vs. Sub query
• Join vs. UNION

1) Difference between Where vs. Having

Where is used for filtering rows and it works on row's data not on aggregated data.

In where the basic query would be something like below.

Example: Select * from Employee Where Score>=50

The above is very basic query for filtering the rows.

Having

Having works on aggregated data and not on normal rows.

To understand aggregate data we can have discuss about the below examples.

To perform calculations on multiple rows of a single column. It returns a single value.

There are five commonly used aggregate functions used to summarize the data.

      1) COUNT

      2) MAX

      3) MIN

      4) AVG

      5) SUM

Below is one of the basic examples for aggregate function. Consider the employee table.

   Employee ID    Salary

       1           5000

       2           6000

       3           7000

       4           8000

       5           9000

Example: Select MAX (Salary) from Employee

The above query returns Maximum of salary from Employee table. Here what happens the query is being performed on multiple rows of single column.

Similarly we can apply all other aggregate functions.

Remember with aggregate function you will always have having or Group BY and we cannot use Where clause.

2) Difference between Union and Union ALL

It is very simple Union removes duplicate records

Union ALL doesn't remove duplicate records.

Union operator combine result set of 2 or more select statements.

Each select statement must have

• Same number of columns
• Columns must have similar data type.
• Columns must in same order

If you would like to know more about this topic you may check this

What is the difference between UNION and UNION ALL

3) IN vs. Exists

IN is basically multiple OR

Now for example when I had a select statement something like

Select * from Customers Where City = 'Mumbai' OR City='Bangalore' OR City='Chennai'

The below is much easiest statement

Select * from Customers where City IN ('Mumbai','Bangalore','Chennai')

Instead of putting list over here you can also select values from another table.

Select * from Customers Where City IN (Select City from table3);

Exists:

Returns either True or False Values.

Select * from Customers Where EXISTS (Select City from table2 where table2.id=table1.id)

When we should use IN When we should use EXISTS

IN: When we have a big outer query and small Inner Query

EXISTS: Small outer query and big inner query.

IN: We use when we want to compare one value to several values.

EXISTS: tells you whether a query returned any results.