Interested to Buy Any Domain ? << Click Here >> for more details...
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
- 11263 Views
- ABC, Accenture, I also Faced
- E-Mail Answers
Answers were Sorted based on User's Feedback
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 |
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 |
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 |
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 |
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 |
what is lazy writer?
Which are the olap features?
what is the Enterprise manager(2000) and management studio (2005)?
How to create logins using windows Authentication mode?
What is normalization according to you and explain its different levels?
How get current date in SQL server 2000
4 Answers Cap Gemini, Polaris,
What is the size of transaction log file?
How do you load large data to the SQL server database?
How To Change Column Ordinal Position in SQL Server 2005 using Query i.e I Want To Add Column at Particular Ordinal Position in SQL Server 2005
Explain how dts is used to extract, transform and consolidate data?
What is the order in which the sql query is executed?
What are the system database in sql server 2005?
-
Oracle (3259) - SQL Server (4518)
- MS Access (429)
- MySQL (1402)
- Postgre (483)
- Sybase (267)
- DB Architecture (141)
- DB Administration (291)
- DB Development (113)
- SQL PLSQL (3330)
- MongoDB (502)
- IBM Informix (50)
- Neo4j (82)
- InfluxDB (0)
- Apache CouchDB (44)
- Firebird (5)
- Database Management (1411)
- Databases AllOther (288)
