http://publib.boulder.ibm.com/infocenter/rbhelp/v6r3/index.jsp?topic=%2Fcom.ibm.redbrick.doc6.3%2Fssg%2Fssg69.htm
Different ways to join tables
Question
How long did the Christmas special promotion run in 1998 and 1999? What were the total sales for products sold on that promotion in each year, and what was the average sales total per day in each year?
Example query 1
select promo_desc, year, sum(dollars) as sales, datediff(day, end_date, start_date)+1 as days_on_promo, string(sales/days_on_promo, 7, 2) as per_day from period natural join sales natural join promotion where promo_desc like 'Christmas%' and year in (1998, 1999) group by promo_desc, year, days_on_promo;
Example query 2
select promo_desc, year, sum(dollars) as sales, datediff(day, end_date, start_date)+1 as days_on_promo, string(sales/days_on_promo, 7, 2) as per_day from period join sales on period.perkey = sales.perkey join promotion on promotion.promokey = sales.promokey where promo_desc like 'Christmas%' and year in (1998, 1999) group by promo_desc, year, days_on_promo;
Example query 3
select promo_desc, year, sum(dollars) as sales, datediff(day, end_date, start_date)+1 as days_on_promo, string(sales/days_on_promo, 7, 2) as per_day from period join sales using(perkey) join promotion using(promokey) where promo_desc like 'Christmas%' and year in (1998, 1999) group by promo_desc, year, days_on_promo;
Three queries, same result
| Promo_Desc | Year | Sales | Days_on_promo | Per_day |
|---|---|---|---|---|
| Christmas special | 1999 | 1230.00 | 31 | 39.67 |
| Christmas special | 1998 | 690.00 | 31 | 22.25 |
Joins in the FROM clause
You can explicitly join tables in the FROM clause in three ways:
About the query
This query joins the Promotion, Period, and Sales tables over columns with identical names; therefore, it can be abbreviated with the NATURAL JOIN syntax, as shown in Query 1. Queries 2 and 3 show alternative methods of specifying inner equijoins in the FROM clause. The result set is the same in all three cases; however, the ON and USING join specifications retain both joining columns in their intermediate result sets, whereas the NATURAL JOIN specification combines each pair of joining columns into one column.
- The DATEDIFF function is used to calculate the duration of the Christmas promotion. This function is discussed in detail on page Using the DATEDIFF function..
- The STRING function is used to scale the Per_Day column values down to a precision of two decimal places; without this function, the following expression would return long-numeric values:
sales/promo_days
Usage notes
Natural joins operate on all pairs of columns that have identical names and should be used with caution; otherwise, tables might be inadvertently joined over columns that happen to have the same name but were not intended to participate in the join.
In the retail schema of the Aroma database, all the primary-key to foreign-key relationships are based on columns with the same name, so natural joins are effective for most queries that involve the Sales table and its dimensions.
For an example of a join over nonprimary-key and foreign-key columns, refer to Calculating elapsed days. For a complete discussion of join syntax, refer to theSQL Reference Guide.
Copyright IBM Corporation, 2003, 2004 
Last updated: March, 2004Self-joins
Question
Which products in the Product table have the same names but different types of packaging?
Example query
select a.prod_name as products, a.pkg_type from product a, product b where a.prod_name = b.prod_name and a.pkg_type <> b.pkg_type order by products, a.pkg_type;
Result
| Product | Pkg_type |
|---|---|
| Aroma Roma | No pkg |
| Aroma Roma | One-pound bag |
| Assam Gold Blend | No pkg |
| Assam Gold Blend | Qtr-pound bag |
| Assam Grade A | No pkg |
| Assam Grade A | Qtr-pound bag |
| Breakfast Blend | No pkg |
| Breakfast Blend | Qtr-pound bag |
| Cafe Au Lait | No pkg |
| Cafe Au Lait | One-pound bag |
| Colombiano | No pkg |
| Colombiano | One-pound bag |
| Darjeeling Number 1 | No pkg |
| Darjeeling Number 1 | Qtr-pound bag |
| Darjeeling Special | No pkg |
| Darjeeling Special | Qtr-pound bag |
| Demitasse Ms | No pkg |
| Demitasse Ms | One-pound bag |
| Earl Grey | No pkg |
| Earl Grey | Qtr-pound bag |
| English Breakfast | No pkg |
| English Breakfast | Qtr-pound bag |
| Expresso XO | No pkg |
| Expresso XO | One-pound bag |
| Gold Tips | No pkg |
| Gold Tips | Qtr-pound bag |
| Irish Breakfast | No pkg |
| Irish Breakfast | Qtr-pound bag |
| ... |
About the query
The tables being joined in a query do not need to be distinct; you can join any table to itself as long as you give each table reference a different name. Self-joins are useful for discovering relationships between different columns of data in the same table. This query joins the Product table to itself over the Prod_Name column, using the aliases a and b to distinguish the table references:
from product a, product b
The self-join compares Product table a to Product table b to find rows where the product names match but the package types differ:
where a.prod_name = b.prod_name and a.pkg_type <> b.pkg_type
The result set consists of a list of each pair of identically named products and their individual package types.
Copyright IBM Corporation, 2003, 2004 Last updated: March, 2004Outer join of two tables
Example query (left outer join)
select * from state left outer join region on state.city = region.city;
Result
| State:city | State:state | Region:city | Region:area |
|---|---|---|---|
| Jacksonville | FL | Jacksonville | South |
| Miami | FL | Miami | South |
| Nashville | TN | NULL | NULL |
Example query (right outer join)
select * from state right outer join region on state.city = region.city;
Result
| State:city | State:state | Region:city | Region:area |
|---|---|---|---|
| Jacksonville | FL | Jacksonville | South |
| Miami | FL | Miami | South |
| NULL | NULL | New Orleans | South |
Example query (full outer join)
select * from state full outer join region on state.city = region.city;
Result
| State:city | State:state | Region:city | Region:area |
|---|---|---|---|
| Jacksonville | FL | Jacksonville | South |
| Miami | FL | Miami | South |
| Nashville | TN | NULL | NULL |
| NULL | NULL | New Orleans | South |
Important:
These examples use the tables introduced on page Join of two tables.
Outer joins
In most cases, tables are joined according to search conditions that find only the rows with matching values; this type of join is known as an inner equijoin. In some cases, however, decision-support analysis requires outer joins, which retrieve both matching and non-matching rows, or non-equijoins, which express, for example, a greater-than or less-than relationship.
An outer join operation returns all the rows returned by an inner join plus all the rows from one table that do not match any row from the other table. An outer join can be left, right, or full, depending on whether rows from the left, right, or both tables are retained. The first table listed in the FROM clause is referred to as the left table and the second as the right table. For all three types of outer join, NULLs are used to represent empty columns in rows that do not match.
Syntax
As shown in the preceding examples, an outer join between two tables can be specified in the FROM clause with the OUTER JOIN keywords followed by the ONsubclause:
>>-FROM--table_1--+-LEFT--+--OUTER JOIN--table_2----------------> +-RIGHT-+ '-FULL--' >--ON--table_1.column--=--table_2.column-----------------------><
For details about other ways to specify outer join predicates in the FROM clause, refer to the SQL Reference Guide.
About the queries
- The result of the left outer join contains every row from the State table and all matching rows in the Region table. Rows found only in the Region table are not displayed.
- The result of the right outer join contains every row from the Region table and all matching rows from the State table. Rows found only in the State table are not displayed.
- The result of the full outer join contains those rows that are unique to each table, as well as those rows that are common to both tables.
Copyright IBM Corporation, 2003, 2004 Last updated: March, 2004