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.
- 5 Answers
- 10099 Views
- ABC, Accenture, I also Faced
- E-Mail Answers
Answer Posted / 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 |
Post New Answer View All Answers
What does man by sql wildcard characters in sql server?
What is primary key index?
Define a cross join?
What is data mart? : sql server analysis services, ssas
List layers of abstraction microsoft architectured to provide relational db through cloud platform ?
What command would you use to add a column to a table in sql server?
What is the tcp/ip port on which sql server runs?
How do database indexes work?
What is the difference between left and right outer join?
How to rename databases in ms sql server?
What structure can you implement for the database to speed up table reads?
How to change the data type of an existing column with "alter table" statements in ms sql server?
Do you know what is lock escalation?
Explain transaction server auto commit?
what is the difference between openrowset and openquery?
