Hi,
What are the differences between UNION and JOINS?
Regards!
Hi,
What are the differences between UNION and JOINS?
Regards!
Union : The union operator combines the results of two or more queries into a single result set. But no.of columns must match in both/all the queries (and also the order) which are used for union.
Union -- returns with no duplicate rows
Union all -- retruns with duplicate rows (No. of rows returned = No. of rows in Query1 + No. of rows in Query 2)
Union is a set operator.
You cannot use the union operator within a create view statement.
You cannot use the union operator on text and image columns.
You cannot use the for browse clause in statements involving the union operator.
Joins: Joins are used to extract information from more than one table based on the related column/coloums (PK and RFK) any no. of rows can be retrived based on matching matching colums. Different types of joins are Inner join=equi join= join, outer join(Right outer join/right join, Left outer
join/ left join), Cross join, and full outer join/outer join
I think the only reason people are confused is because both these operators are used to combine two or more set of rows. But of both of them a very different concepts and are used under different circumstances. They are definitely not equivalent. Now what is the difference?
A joins is usually used to combine rows from multiple tables. There are different types of joins, such as inner and outer joins. Inner joins return all the rows from multiple tables on which the condition is set whereas outer join returns all rows from one table and only those rows from a secondary table where the join condition is met.
Now coming to union. It is a set operator. Suppose you have
Col1
A
Col2
B
Col1 union Col2 returns
A
B
The difference between them is when to use.
A join is used when we want to filter rows on the basis of (Cross Multiplication/Subtraction/Minus etc) and the tables on which union is applied has different or same number of columns.
Union is basically used to merge two or more sets with number/type/name of column. It should be noted that the union operator can only be applied when number and data types of the columns in the two tables are the same.
Please refer this for more help!
UNION
http://www.w3schools.com/sql/sql_union.asp
JOIN
http://www.w3schools.com/sql/sql_join.asp
Bookmarks