Introductory Transact-SQL, Page 7

Other Types of Joins

Thus far, we've explored only left joins—both inner and outer. There are a few others that are worth mentioning as well. Transact-SQL also supports RIGHT OUTER JOINs, CROSS JOINs, and FULL OUTER JOINs.

A RIGHT OUTER JOIN isn't really that different from a LEFT OUTER JOIN. In fact, it's really just a LEFT OUTER JOIN with the tables reversed. It's very easy to restate a LEFT OUTER JOIN as a RIGHT OUTER JOIN. Here's the earlier LEFT OUTER JOIN query restated:

SELECT customers.CustomerNumber+2, orders.AmountFROM orders RIGHT OUTER JOIN customers ON(customers.CustomerNumber+2=orders.CustomerNumber)Amount------ --------3      86753.094      NULL5      NULL

A RIGHT JOIN returns the columns in the first table as NULLs when the join condition fails. Since you decide which table is the first table and which one's the second, whether you use a LEFT JOIN or a RIGHT JOIN is largely a matter a preference.

A CROSS JOIN, by contrast, is an intentional Cartesian product. The size of a Cartesian product is the number of rows in one table multiplied by those in the other. So for two tables with three rows each, their CROSS JOIN or Cartesian product would consist of nine rows. By definition, CROSS JOINs don't need or support the use of the ON clause that other joins require. Here's a CROSS JOIN of the customers and orders tables:

SELECT customers.CustomerNumber, orders.AmountFROM orders CROSS JOIN customersCustomerNumber Amount-------------- --------1              123.451              678.901              86753.091              678.902              123.452              678.902              86753.092              678.903              123.453              678.903              86753.093              678.90(12 row(s) affected)

A FULL OUTER JOIN returns rows from both tables regardless of whether the join condition succeeds. When a join column in the first table fails to find a match in the second, the values from the second table are returned as NULL, just as they are with a LEFT OUTER JOIN. When the join column in the second table fails to find a matching value in the first table, columns in the first table are returned as NULL, as they are in a RIGHT OUTER JOIN. You can think of a FULL OUTER JOIN as the combination of a LEFT JOIN and a RIGHT JOIN. Here's the earlier LEFT OUTER JOIN restated as a FULL OUTER JOIN:

SELECT customers.CustomerNumber+2, orders.AmountFROM customers FULL OUTER JOIN orders ON(customers.CustomerNumber+2=orders.CustomerNumber)Amount------ --------3      86753.094      NULL5      NULLNULL   123.45NULL   678.90NULL   678.90

Subqueries

A SELECT statement that's enclosed in parentheses and embedded within another query (usually in its WHERE clause) is called a subquery. A subquery is normally used to return a list of items that is then compared against a column in the main query. Here's an example:

SELECT * FROM customersWHERE CustomerNumber IN (SELECT CustomerNumber FROM orders)

Of course, you could accomplish the same thing with an inner join. In fact, the SQL Server optimizer turns this query into an inner join internally. However, you get the idea—a subquery returns an item or set of items that you may then use to filter a query or return a column value.