Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Union vs. Join Expand / Collapse
Author
Message
Posted Wednesday, December 4, 2002 8:00 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, August 21, 2012 12:50 PM
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
Post #8586
Posted Wednesday, December 4, 2002 8:07 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 3:27 AM
Points: 1,130, Visits: 384
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



Regards,
Andy Jones
Post #48554
Posted Wednesday, December 4, 2002 8:57 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, October 17, 2003 12:00 AM
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).



Post #48555
Posted Wednesday, December 4, 2002 9:18 AM


Keeper of the Duck

Keeper of the Duck

Group: Moderators
Last Login: Friday, November 14, 2014 7:14 AM
Points: 6,625, Visits: 1,876
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, CISA, MCSE, Security+, MVP - SQL Server
Regular Columnist (Security), SQLServerCentral.com
Author of Introduction to SQL Server: Basic Skills for Any SQL Server User
| Professional Development blog | Technical Blog | LinkedIn | Twitter
Post #48556
Posted Wednesday, December 4, 2002 9:21 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, August 21, 2012 12:50 PM
Points: 416, Visits: 169
Thanks guys. I got it straight now.

Christine






Aurora
Post #48557
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse