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: