December 4, 2002 at 8:00 am
The difference between the two? [Platform is SQL 2k]When is the best time to use them? In other words I would use the join if...or the union if...
Thanks Chris
Aurora
December 4, 2002 at 8:07 am
Joins are to return data from 2 or more related tables e.g.
select * from customers c inner join orders o on c.id=o.id
Union is to return multiple result sets of the the same structure as one e.g. select 1 union select 2.
Regards,
Andy Jones
.
December 4, 2002 at 8:57 am
There is not an either/or situation with join and union. join is used to combine 2 or more tables into a single virtual table with a set of columns from both tables.
UNION is used to combine the results of 2 separate SELECTS into a single rowset (or ADO RECORDSET).
December 4, 2002 at 9:18 am
Easiest way to think of it:
With a join I put the data in two tables side-by-side. However, since I determine how the rows match up (table1.col1 = table2.col2 for instance), I can only get the rows that match exactly based on my join criteria (INNER JOIN), all the rows of the first table but only the matching rows of the second (LEFT JOIN or LEFT OUTER JOIN), all the rows of the second table but only the matching rows of the first (RIGHT JOIN or RIGHT OUTER JOIN), or all rows of both table, regardless of match (FULL JOIN or FULL OUTER JOIN). I could also do a CROSS JOIN which takes every row of table1 and matches it with every row of table2. If you remember Punnett squares from Biology with the XX and Xx type of stuff, that's a CROSS JOIN.
Something like:
A UNION is where I take one table and then I take the second table and put the contents right after it. Like appending to the end of the list. A UNION call by itself will only return distinct records, meaning if there are identical records in Table1 and Table2, only one record will be returned. A UNION ALL says give me all the records, even the duplicate.
A big difference between the two is with a JOIN the tables have to be relatable in some way (with the exception of the cross join). I've got to be able to do a comparison where I can say a row in table1 matches a row in table2. Another difference is with a UNION or UNION all, my queries both have to have the same number of columns. A join can have different numbers of columns on both sides.
Graphically:
JOIN: A - B
UNION:
A
|
B
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley
December 4, 2002 at 9:21 am
Thanks guys. I got it straight now.
Christine
Aurora
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply