Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Union vs. Join


Union vs. Join

Author
Message
MegaDBA
MegaDBA
SSC-Addicted
SSC-Addicted (416 reputation)SSC-Addicted (416 reputation)SSC-Addicted (416 reputation)SSC-Addicted (416 reputation)SSC-Addicted (416 reputation)SSC-Addicted (416 reputation)SSC-Addicted (416 reputation)SSC-Addicted (416 reputation)

Group: General Forum Members
Points: 416 Visits: 169
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
Andy Jones, DBA
Andy Jones, DBA
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1178 Visits: 535
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

.
don1941
don1941
SSC Veteran
SSC Veteran (299 reputation)SSC Veteran (299 reputation)SSC Veteran (299 reputation)SSC Veteran (299 reputation)SSC Veteran (299 reputation)SSC Veteran (299 reputation)SSC Veteran (299 reputation)SSC Veteran (299 reputation)

Group: General Forum Members
Points: 299 Visits: 1
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).



K. Brian Kelley
K. Brian Kelley
Keeper of the Duck
Keeper of the Duck (6.8K reputation)

Group: Moderators
Points: 6816 Visits: 1917
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
MegaDBA
MegaDBA
SSC-Addicted
SSC-Addicted (416 reputation)SSC-Addicted (416 reputation)SSC-Addicted (416 reputation)SSC-Addicted (416 reputation)SSC-Addicted (416 reputation)SSC-Addicted (416 reputation)SSC-Addicted (416 reputation)SSC-Addicted (416 reputation)

Group: General Forum Members
Points: 416 Visits: 169
Thanks guys. I got it straight now.

Christine




Aurora
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search