1. Categories >> Software >> Databases >> SQL Server
  2. Suggest New Category

What is the difference between in and exists.
Ex: select * from emp where empno in(....) and
select * from emp where empno exists(....)

What is the difference between a Join and Union and Union
and UnionAll.

Question Posted / dotnetblog4me

Answers were Sorted based on User's Feedback



What is the difference between in and exists. Ex: select * from emp where empno in(....) and sele..

Answer / gopi muluka

IN:Returns true if the column matches to any value in a
subquery or a list.
Exists:Returns true if a subquery contains any row.

Generally EXISTS is faster than IN, because it stops
processing once it finds a row in resultset

Join : Returns only rows which are matching on Joining
Column

Union: Combines two or more resultsets with same number of
columns and displays it as a single resultset by
eliminating duplicate records

Union All: Combines two or more resultsets with same number
of columns,displays it as a single resultset including
duplicate records if any exists

Is This Answer Correct ?    16 Yes 0 No

What is the difference between in and exists. Ex: select * from emp where empno in(....) and sele..

Answer / shafiq...

When you use IN operator it will scan all the row in table
to check and Exists means when its find the data stop
execute to check further row.

Join- to join matching data from two or more table.
Union- Retrive matching data from both table.
UnionAll- Retrive matching as well as unmatched data from
both data.

hope this is clear

Is This Answer Correct ?    19 Yes 6 No

What is the difference between in and exists. Ex: select * from emp where empno in(....) and sele..

Answer / saradhi

Just like JOINS, UNION combines data into a single record-
set but vertically by adding rows from another table. JOINS
combine data horizontally by adding columns from another
table.

UNION insures you get DISTINCT records from both the tables.
UNION ALL pulls out all records from both the tables with
duplicates.

Is This Answer Correct ?    2 Yes 0 No

What is the difference between in and exists. Ex: select * from emp where empno in(....) and sele..

Answer / nandkumar karlekar

Answer:-

1. Union is heavy as compare to Union All

2. result of Union Query is Set where as result of
Union ALL is not a Set it’s only concatenation of two
results

3. Suppose table1 and table2 have T1 ,T2 records
respectively then the number of comparison require to
produce result is as follows



Union All requires O (1) time complexity

Union Requires O ((T1*(T1+1)/2) + (T2*(T2+1)/2) + (T1 *T2))





Example for UNION: - Table1 has 10 records

Table2 has 20 records

Suppose: table1 and table2 has all record distinct.



Total comparisons require = 465 comparisons



Example for UNION ALL: -

Table1 has 10 records

Table2 has 20 records



Suppose: table1 and table2 has all record distinct or not,
no problem.



Total comparisons require = Constant or may be 0

Is This Answer Correct ?    1 Yes 0 No

What is the difference between in and exists. Ex: select * from emp where empno in(....) and sele..

Answer / manoj pandey

Check below links to know the reason with examples from my blog posts:

IN vs EXISTS vs JOIN: http://sqlwithmanoj.wordpress.com/2011/02/15/not-in-not-exists-joins-with-null-values/

UNION vs UNION ALL: http://sqlwithmanoj.wordpress.com/2010/12/30/why-union-all-is-faster-than-union/

Is This Answer Correct ?    1 Yes 0 No

Post New Answer

More SQL Server Interview Questions

You are doing log shipping due to some reasons it is failing. How you will proceed from there

0 Answers  

How can sql server instances be hidden?

0 Answers  

What are the different ways you can create databases in sql server?

0 Answers  

Do you know what is recursion? Is it possible for a stored procedure to call itself or recursive stored procedure? How many levels of sp nesting is possible?

0 Answers  

What are the different ways of moving data/databases between servers and databases in SQL Server?

3 Answers   HCL,




How to Rebuild Master database in sql server 2005.

2 Answers   Wipro,

What is a rownum?

0 Answers  

What is the contrast between sql and pl/sql?

0 Answers  

What is the purpose of floor function?

0 Answers  

one of my database size is 2gb and Unrestricted Growth for Data file up to 10%.But every day after day I am getting Primary Data file is full 99.999 please take appropriate actions.Why it is? Even disk space is also not full,but still I am getting the alerts.

1 Answers   Cognizant,

What stored by the tempdb ? : sql server database administration

0 Answers  

What is the difference between a Local temporary table and a Global temporary table? How is each one used?

2 Answers   HCL,

For more SQL Server Interview Questions Click Here
Categories