SQL Server 2005: JOINS and UNION

There are actually 4 joins in SQL Server: INNER, OUTER, CROSS, AND FULL.  If you come from an Access background, you already know INNER, OUTER, and CROSS joins.

INNER JOIN is the default join in SQL Server so any SQL statements that has only JOIN in it is, by default, an INNER JOIN.  Use this type of join if you only want to include records where the joined fields from both tables are equal.  Example of an INNER JOIN:

SELECT *
FROM Products
INNER JOIN Suppliers ON Products.SupplierID = Suppliers.SupplierID

OUTER JOIN can be a LEFT OUTER JOIN or a RIGHT OUTER JOIN.  Use LEFT OUTER JOIN if you want to include all records from the table left of the JOIN keyword, and only those records from the table right of the JOIN keyword, where the joined fields are equal  Use RIGHT OUTER JOIN if you want to include all records from the table right of the JOIN keyword, and only those records from the table left of the JOIN keyword, where the joined fields are equal.

When mixing INNER and OUTER JOINS, it is best to do the INNER JOINS first before the OUTER JOINS.

CROSS JOIN is just the cartesian product of all the records on both tables.  It doesn’t have the ON operator like the other 3 JOINS.  Typically used in scientific environments but you can also use it to build your test data.  Example of a CROSS JOIN:

SELECT Customers.CustomerName, Addresses.Address
FROM Customers
CROSS JOIN Addresses

FULL JOIN, also known as FULL OUTER JOIN, is like having a LEFT OUTER JOIN and a RIGHT OUTER JOIN in one JOIN.  It is rarely used which might be the reason Access does not have this type of join.

UNION, unlike the JOINS, appends the result from your second query to your first query, essentially adding more rows instead of adding more columns.  When you use UNION, all queries should have the same number of columns.  The data types of each column in a query should be implicitly compatible with the data type in the same relative column in the other queries.  The heading returned for a UNION query is taken from the first query.  This means you only need to provide meaningful column names on your first query.  Also by default the UNION query returns DISTINCT records so if you want it to return duplicate records, then you need to specify UNION ALL:

SELECT Column1, Column2
FROM Table1 

UNION ALL 

SELECT Column1, Column2
FROM Table2
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s