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 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
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.
Page 7 of 10