Data Management

Build Your Skills: Join syntax construction

Building database joins requires a thorough understanding of the idiosyncrasies of each type. This article explains each type (inner, outer, cross, and self) and shows the syntax associated with each.


Knowing the right join may keep you from making a left turn and taking your troubleshooting efforts way off track. In a previous article, "Build Your Skills: The underpinnings of SQL joins," we used what may be a new term to you—inner join. SQL supports a number of join types: inner, left outer, right outer, full outer, and cross. Most of the time, you'll probably depend on the default inner join, but, occasionally, you'll need to put all that SQL power to use and rely on an outer or cross join. In this article, I will discuss the syntax of each join type.

Note
Not all systems support all join types. Check your system's documentation for specifics on which joins it supports.

Inner join
An inner join returns only those records from both tables that have a matching value in the related field. This type of join is by far the most commonly used. In fact, many systems use this type as the default join. To help you visualize what an inner join looks like, think of your tables as intersecting circles. Each circle represents a table within a relationship, and the area where the circles intersect represents the records that have a matching primary and foreign key value. SQL retrieves or acts upon those records—and only those records—where the two circles intersect.

Our Northwind example from the previous article was an inner join—retrieving data from both tables only when the CategoryID values matched. In this particular example, each product record had a corresponding category value, but that won't always be the case. Sometimes, records are eliminated simply because they don't have a matching primary/foreign key value. As you can see, a join works somewhat like a filter.

The outer joins
I mentioned in the previous article that an inner join's ON condition can specify comparison operators other than Equals To. In a way, that's what an outer join does—an outer join retrieves records where the primary/foreign key values match, but it also retrieves records that do not have a matching value. There are three types of outer joins: left outer, right outer, and full outer. All outer joins retrieve records from both tables, just as an inner join does. However, an outer join retrieves all of the records from one of the tables.

Note
Your system may or may not support other comparison operators in each join type. Check your documentation for specific information.

There's another very significant difference between outer joins and the inner join. The order in which you reference the tables may be significant because the first table becomes what's known as the left table, or the one table in a one-to-many relationship. The second table is the right table, or the many table in a one-to-many relationship. What this means is that the SELECT clause order not only dictates how the columns are displayed in the resultset, but that this order also determines which table delivers all of its records. In an inner join, this latter point just doesn't exist.

Note
This rule doesn't apply to all systems, so check your documentation for more information. For instance, Oracle uses a left to right hierarchy in most syntax, but you can override that behavior by using parenthesis.

Left outer
The left outer join retrieves records from both tables, retrieving all the records from the one side of the relationship—that's the left table—and any records from the right table (or many table) where the primary/foreign key values match. If there are no matching values in the primary/foreign key columns, the join still retrieves all the records from the one side. Consequently, the resulting recordset often appears to have incomplete records.

The following statement illustrates the usefulness of a left outer join:
 
SELECT Customers.CustomerID, Customers.CompanyName, Orders.OrderID
FROM Customers LEFT JOIN Orders
  ON Customers.CustomerID = Orders.CustomerID
ORDER BY OrderID
 

This statement returns all the customer records from the Customers table and relates each customer to every order number in the Orders table. By adding the ORDER BY clause, you can easily spot those customers who haven't ordered anything. (The same statement works in the Access .mdb version of Northwind.) A similar statement in Oracle 9i would resemble the following:
 
SELECT Customers.CustomerID, Customers.CompanyName, Orders.OrderID
FROM Customers LEFT OUTER JOIN Orders
ORDER BY OrderID
 

Earlier versions would use the following:
 
SELECT Customers.CustomerID, Customers.CompanyName, Orders.OrderID
FROM Customers, Orders
WHERE Customers.CustomerID = Orders.CustomerID(+)
ORDER BY OrderID
 

Right outer
The right outer join is similar to the left outer join in that it retrieves all the records from one side of the relationship, but, this time, it's the right or many side. Only records where the primary/foreign keys match are retrieved from the left or one side of the relationship.

Our last example relates all the customers to the current orders and, by doing so, exposes customers who haven't ordered. You could run the same statement with a right outer join to exclude the two null-valued records, but an inner join would do the same. If the Orders table contained any orphans, a right outer join would find them. The Orders table doesn't contain orphans (which is good for the database, but bad for our examples), so we'll just have to suppose it does. For instance, if you deleted the first Customers record in our Northwind Customers table— Alfreds Futterkiste—the following right outer join will turn up a few orphans:
 
SELECT CustomersCOPY.CustomerID, CustomersCOPY.CompanyName, Orders.OrderID
FROM CustomersCOPY RIGHT JOIN Orders
  ON CustomersCOPY.CustomerID = Orders.CustomerID
ORDER BY CustomersCOPY.CustomerID
 

Review the complete recordset and you'll discover that the right outer join eliminates the two null-valued records from our earlier example—PARIS and FISSA. These were the two inactive customers. Remember, the right outer join retrieves all the records from the many table, which, in this case, is the Orders table. There are no orders for either of the above companies in the Orders table, and the join can't show data that doesn't exist. However, the orphans do have orders in the Orders table, even though the join can't match them to a customer in the CustomersCOPY table. The right outer join includes the orphans despite the relationship's inability to match each orphan's foreign key value to a primary key value in CustomersCOPY.

In a similar situation, in Oracle 9i, the statement might resemble the following:
 
SELECT CustomersCOPY.CustomerID, CustomersCOPY.CompanyName, Orders.OrderID
FROM CustomersCOPY RIGHT OUTER JOIN Orders
ORDER BY CustomersCOPY.CustomerID
 

Earlier versions would use the (+) syntax in the form:
 
SELECT CustomersCOPY.CustomerID, CustomersCOPY.CompanyName, Orders.OrderID
FROM CustomersCOPY, Orders
WHERE CustomersCOPY.CustomerID(+) = Orders.CustomerID
ORDER BY CustomersCOPY.CustomerID
 

Experimenting with these two joins may convince you that they are interchangeable, but they aren't. Remember, the left join retrieves all the records from the one table and the right join retrieves all the records from the many table. Keeping that in perspective should help you decide which join you need.

Full outer
Now that you know the nature of the left outer and right outer joins, you might be able to anticipate what a full outer join does. The full outer join retrieves all records from both the left and the right table. Most likely, you won't run into a need for this join very often. Nonetheless, an Oracle 9i statement might resemble the following:
 
SELECT Customers.CustomerID, Customers.CompanyName, Orders.OrderID
FROM Customers FULL OUTER JOIN Orders
ORDER BY Customers.CustomerID
 

Note
There isn't an equivalent in earlier versions of Oracle.

The cross join
A cross join returns what's known as a Cartesian product. What that means is the join combines every row from the left table with every row in the right table. As you can imagine, sometimes this produces a mess, but, under the right circumstances, it can be very useful. For instance, let's suppose you want to combine each product with a pricing table so you can analyze each product at each price. In one table, you have the current price of each product. In a second table, you have a list of increasing values such as .05 percent, .06 percent, .10 percent, and so on. In this case, a cross join combines each product price with each increase value. The following statement creates such a matrix between the Products table and a temporary table named Amount:
 
SELECT Products.ProductName, Products.UnitPrice, Temporary.Amount
FROM Products, Temporary
ORDER BY ProductName, Amount
 

Oracle 9i uses a statement such as the following:
 
SELECT Customers.CustomerID, Customers.CompanyName, Orders.OrderID
FROM Customers CROSS JOIN Orders
ORDER BY OrderID
 

Earlier Oracle versions would use a statement similar to the following:
 
SELECT Customers.CustomerID, Customers.CompanyName, Orders.OrderID
FROM Customers, Orders
ORDER BY OrderID
 

The self join
There's a special type of relationship called a self join that's really not like the other joins because you can apply it to any join type. A self join is rather unique in that it involves a relationship with only one table. Instead of relating a table to a second table, the join is based on a relationship based on the same table. You simply reference the table twice and use an alias to avoid confusion. The common example used here is when you have a hierarchical reporting structure whereby one member of staff reports to another. In this case, the employees in the employee table are related to one another in order to find out who manages whom. For example, this returns all records including those employees without a manager:
 
SELECT Employees.LastName, Employees.FirstName, Supervisors.LastName,
     Supervisors.FirstName
FROM Employees LEFT JOIN Employees AS Supervisors
  ON Employees.EmployeeID = Supervisors.ReportsTo
 

where Supervisors is an alias to the Employees table. The results list each employee and the employees who report to each employee listed in the Employees table.

 

Oracle 9i syntax would resemble the following:
 
SELECT Employees.LastName, Employees, FirstName, Supervisors.LastName,
    Supervisors.FirstName
FROM Employees LEFT OUTJOIN Employees Supervisors
  ON (Employees.EmployeeID = Supervisors.ReportsTo)
 

If you're using an earlier Oracle version, you'd use syntax similar to the following:
 
SELECT Employees.LastName, Employees.FirstName, Supervisors.LastName,
    Supervisors.FirstName
FROM Employees, Employees Supervisors
WHERE Employees.EmployeeID = Supervisors.ReportsTo(+)

Not every system is alike
It would be impossible to review how each relational database system uses each of the SQL joins. Throughout this article, we've provided the syntax for Jet SQL, Transact-SQL, and Oracle SQL. Table A identifies which systems support each of the joins.
Table A
 

Transact-SQL

Jet SQL

Oracle 9i SQL

Oracle 8i SQL

inner

outer left

outer right

outer full

Cross

System supported joins

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

Editor's Picks

Free Newsletters, In your Inbox