User's Guide
   PART 1. Working with Databases
     CHAPTER 6. Joins: Retrieving Data from Several Tables       

Inner, left-outer, and right-outer joins

Inner joins and outer joins differ in their treatment of rows that have no match in the other table: rows appear in an inner join only if both tables contain at least one row that satisfies the join condition.

Because inner joins are the default, you do not need to specify the INNER keyword explicitly. Should you wish to use it for clarity, place it immediately before the JOIN keyword.

For example, each row of

SELECT fname, lname, order_date
FROM customer
   KEY INNER JOIN sales_order
ORDER BY order_date

contains the information from one customer row and one sales_order row. If a particular customer has placed no orders, the join will contain no information about that customer.

fname	lname	order_date
Hardy	Mums	1993-01-02
Tommie	Wooten	1993-01-03
Aram	Najarian	1993-01-03
Alfredo	Margolis	1993-01-06
Elmo	Smythe	1993-01-06
Malcolm	Naddem	1993-01-07

Because inner joins are the default, you obtain the same result using the following clause.

FROM customer JOIN sales_order

By contrast, an outer join contains rows whether or not a row appears in the opposite table to satisfy the join condition. Use the keywords LEFT or RIGHT to identify the table that is to appear in its entirety.

• A LEFT OUTER JOIN contains every row in the left-hand table.

• A RIGHT OUTER JOIN contains every row in the right-hand table.

For example, the outer join

SELECT fname, lname, order_date
FROM customer
   KEY LEFT OUTER JOIN sales_order
ORDER BY order_date

includes all customers, whether or not they have placed an order. If a particular customer has placed no orders, each column in the join corresponding to order information will contain the NULL value.

fname	lname	order_date
Lewis N.	Clark	(NULL)
Jack	Johnson	(NULL)
Jane	Doe	(NULL)
John	Glenn	(NULL)
Dominic	Johansen	(NULL)
Stanley	Jue	(NULL)
Harry	Jones	(NULL)
Marie	Curie	(NULL)
Elizibeth	Bordon	(NULL)
Len	Manager	(NULL)
Tony	Antolini	(NULL)
Tom	Cruz	(NULL)
Janice	O Toole	(NULL)
Stevie	Nickolas	(NULL)
Philipe	Fernandez	(NULL)
Jennifer	Stutzman	(NULL)
William	Thompson	(NULL)
Hardy	Mums	1993-01-02
Tommie	Wooten	1993-01-03
Aram	Najarian	1993-01-03

The keywords INNER, LEFT OUTER, and RIGHT OUTER may appear as modifiers in key joins, natural joins, and joins that using a comparison. These modifiers do not apply to cross joins.

  Outer joins and join conditions

A common mistake is to place a join condition, which should appear in an ON phrase, in a WHERE clause. Here, the same condition often produces different results. This difference is best explained through a conceptual explanation of the way that Adaptive Server Anywhere processes a select statement.

1. First, Adaptive Server Anywhere logically completes all joins. When doing so, it uses only conditions placed within an ON phrase. When the values in one table are missing or null-valued, the behavior depends upon the type of join: inner, left-outer, or right-outer.

2. Once the join is complete, Adaptive Server Anywhere logically deletes those rows for which the condition within the WHERE clause evaluates to either FALSE or UNKNOWN.

Because conditions are treated differently, the effect of moving a condition from an ON phrase to a WHERE clause is usually to convert the join to an inner join, regardless of the type of join specified.

With INNER JOINS, specifying a join condition is equivalent to adding the join condition to the WHERE clause. However, the same is not true for OUTER JOINS.

For example, the following statement causes a left-outer join.

SELECT *
FROM customer LEFT OUTER JOIN sales_order
   ON customer.id = sales_order.cust_id

In contrast, the following two statements both create inner joins and select the same set of rows.

SELECT *
FROM customer LEFT OUTER JOIN sales_order
   WHERE customer.id = sales_order.cust_id
SELECT *
FROM customer INNER JOIN sales_order
   ON customer.id = sales_order.cust_id

The first of these two statements can be thought of as follows: First, left-outer join the customer table to the sales_order table. For those customers who have not yet placed an order, fill the sales order fields with nulls. Next, select those rows in which the customer id values are equal. For those customers who have not placed orders, these values will be NULL. Since comparing any value to NULL results in the special value UNKOWN, these rows are eliminated and the statement reduces to an inner join.

     This methodology describes the logical effect of the statements you type, not how Adaptive Server Anywhere goes about processing them. For further information, see How joins are processed.