Forum

Forums
SQL
Interview Questions...
SQL difference betw...
 
Share:
Notifications
Clear all

SQL difference between interview questions

 
    Last Post
RSS

Posts: 69
 Jeevan
Guest
Topic starter
Apr 06, 2021 6:31 am
(@Jeevan)
Trusted Member
Joined: 5 years ago

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.

Leave a reply

Author Name

Author Email

Title *

Preview 0 Revisions Saved
Forum Jump:
  Previous Topic
Next Topic  
Share: