Union Vs. Joins

Union : Combines the results of two or more queries into a single result set consisting of all the rows belonging to all queries in the union.

Join : Using Joins you can retrieve data from multiple tables based on the logical relationship between the tables.

Wat is the basic difference between a Union and a Join??

Rgds,
Chandru


suppose you have 2 tables, and we will represent them with # symbol:

# and #

if we UNION them together it makes the results long and thin:
#
#

if they are joined, the result is short and fat:
##

can your imagination see more of the difference now?

use UNION when you must add more results to this result. e.g. you have a query that returns 3 males info, and you want to add on 4 girls to make that up to 7 people, you union them together:

SELECT * FROM males
UNION
SELECT * FROM females

1, john, saggitarius
2, matthew, aries
3, luke, leo
4, cindy, aries
5, selma, saggitarius
6, britney, leo
7, christina, aries

JOIN is used to stick sets of related results together

SELECT * FROM males JOIN females USING(star_sign)
1, john, saggitarius, 5, selma, saggitarius
2, matthew, aries, 4, cindy, aries
2, matthew, aries, 7, christina, aries
3, luke, leo, 6, britney, leo

can you see the difference? one is long and thin, the other short and wide

:D

I occasionally find myself teaching people things and I try to make it relevant to something they know - the UNION/JOIN thing was something I had to teach a while back and it got a good response from the guys who were non database (they can just about manage select * from x where y) so i thought i'd use it again in the future..

As for my location.. i did once consider writing untied kinkdom but then, being a moderator, I have a somewhat more professional designation and purpose so it seemed a little too tongue in cheek. I left it as untied.. my little rebellion:)
..

Hi all.

Your question isn't clear for me...
If you want to know what is the "table origin" for each record selected using the UNION, you should write something like this:

SELECT Item,
Sales_Amt AS Amt
'SALES' AS Origin
FROM sales_history
UNION ALL
SELECT Item,
Purchase_Amt AS Amt
'PURCHASE' AS Origin
FROM Purchase_history

You'll get

Item Amt Origin
A $10 SALES
A $10 PURCHASE
B $10 SALES
C $10 PURCHASE

I hope this will help you

Hi all

What database are you using?
In any case you cannot save your view unless it's right.
Probably you've forgot to set the fields list in the SELECT clause, or table's name in the FROM clause.

anybody?

Hi all.

Your question isn't clear for me...
It wasnt clear for me until i pressed QUOTE reply.

OP doesnt seem to realise his nicely formatted post is lost and all values look like a single column..