In the last Jump Start column “SELECT and JOIN,” you saw how to use a SELECT statement to retrieve just the specific columns that your application needs. In addition, you saw how you can use the SELECT statement with the JOIN keyword to join the contents of two tables together in a single result set. (See also, "Indexes and Joins, February 2002).
An INNER JOIN statement basically joins two tables based on the contents of the values of a common column. To review, let’s take quick look at the example INNER JOIN from the last column:
SELECT c.CustomerID, c.CompanyName, o.OrderID, o.OrderDate FROM Customers c INNER JOIN Orders o ON c.customerID = o.CustomerID WHERE c.customerID = 'ANTON'
The SELECT statement returns the CustomerID, CompanyName, OrderID, and OrderDate columns only when the CustomerID is equal to ANTON. In this statement, the two tables are joined on the CustomerID column that’s common to both tables. Because this example is an INNER JOIN, it takes all the records from the customers table and joins them to the matching records in the orders table. If no match is found--in other words, if there’s a customer with no associated orders--the customer data isn’t included in the results. If a customer has multiple orders, the statement will return one row for each order and repeat the customer data for each row.
As you might expect from the name, LEFT OUTER JOIN works differently than an INNER JOIN. Instead of returning results in which there are rows in both tables as the INNER JOIN does, an OUTER JOIN retrieves all of the records from one of the tables. There are three types of OUTER JOINS: LEFT OUTER, RIGHT OUTER, and FULL OUTER. Let’s look in more detail at the LEFT OUTER JOIN.
The LEFT OUTER JOIN limits its results to the rows in the "left" table. If a row in the left table has no matching row in the right table, the statement will still return a row in the result set. That returned row will have NULL values for the included columns from the right table. You can see an example of the LEFT OUTER JOIN in the following example.
SELECT c.CustomerID, c.CompanyName, o.OrderID FROM Customers c LEFT JOIN Orders o ON c.CustomerID = o.CustomerID