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.
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 is the difference between varchar and varchar(max) datatypes?
How do I schedule a sql server profiler trace?
What is the difference between clustered and a non-clustered index?
Tell me what is difference between clustered and non clustered index?
What is a full text index?
What is sql stored procedure?
What is the standby server?
What is the difference between writing data to mirrored drives versus raid5 drives
What is the difference between migration and upgradation in sql server?
What is row_number()?
is there a column to which a default can't be bound? : Sql server database administration
How to write character string constants or literals in ms sql server?
Explain rdbms?
Explain full-text query in sql server?
How do we Backup SQL Azure Data?