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
- 11441 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 identity?
How many types of Joins in SQL Server 2005?
9 Answers CarrizalSoft Technologies, HCL,
Can truncate be rolled back?
when we use function instead of procedure? plz tell me the situation with ex?
How to include date and time values in sql statements?
What is the purpose of indexing?
Define cross join in sql server joins?
How to loop through result set objects using odbc_fetch_row()?
How to store pdf file in sql server?
1 Answers CarrizalSoft Technologies, College School Exams Tests, TATA,
What is difference between createstatement and preparedstatement?
Name 3 ways to get an accurate count of the number of records in a table?
What are a scheduled jobs or what is a scheduled tasks?
-
Oracle (3253) - 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)
