Informix Guide to SQL: Tutorial Composing Advanced SELECT Statements

The section Creating a Join shows how to include a WHERE clause in a SELECT statement to join two or more tables on one or more columns. It illustrates natural joins and equi-joins.

This chapter discusses how to use two more complex kinds of joins, self-joins and outer joins. As described for simple joins, you can define aliases for tables and assign display labels to expressions to shorten your multiple-table queries. You can also issue a SELECT statement with an ORDER BY clause that sorts data into a temporary table.

A join does not always have to involve two different tables. You can join a table to itself, creating a self-join. Joining a table to itself can be useful when you want to compare values in a column to other values in the same column.

To create a self-join, list a table twice in the FROM clause, and assign it a different alias each time. Use the aliases to refer to the table in the SELECT and WHERE clauses as if it were two separate tables. (Aliases in SELECT statements are discussed in Using Aliases and in the Informix Guide to SQL: Syntax.)

Just as in joins between tables, you can use arithmetic expressions in self-joins. You can test for null values, and you can use an ORDER BY clause to sort the values in a specified column in ascending or descending order.

Query 5-9 finds pairs of orders where the ship_weight differs by a factor of five or more and the ship_date is not null. The query then orders the data by
ship_date.

SELECT x.order_num,  x.ship_weight,  x.ship_date,

	  y.order_num,  y.ship_weight,  y.ship_date  

	FROM  orders  x,  orders  y

	WHERE  x.ship_weight  >=  5  *  y.ship_weight

		AND  x.ship_date  IS  NOT  NULL

		AND  y.ship_date  IS  NOT  NULL

	ORDER  BY  x.ship_date


 




  order_num  ship_weight  ship_date        order_num  ship_weight  ship_date    

  

            1004              95.80  05/30/1998                1011              10.40  07/03/1998

            1004              95.80  05/30/1998                1020              14.00  07/16/1998

            1004              95.80  05/30/1998                1022              15.00  07/30/1998

            1007            125.90  06/05/1998                1015              20.60  07/16/1998

            1007            125.90  06/05/1998                1020              14.00  07/16/1998

...



 Query Result 5-9 







 

If you want to store the results of a self-join into a temporary table, append an INTO TEMP clause to the SELECT statement and assign display labels to at least one set of columns to rename them. Otherwise, the duplicate column names cause an error and the temporary table is not created.

Query 5-10, which is similar to Query 5-9, labels all columns selected from the orders table and puts them in a temporary table called shipping.

SELECT x.order_num  orders1,  x.po_num  purch1,  

	   x.ship_date  ship1,  y.order_num  orders2,  

	   y.po_num  purch2,  y.ship_date  ship2

	FROM  orders  x,  orders  y

	WHERE  x.ship_weight  >=  5  *  y.ship_weight

		AND  x.ship_date  IS  NOT  NULL

		AND  y.ship_date  IS  NOT  NULL

	ORDER BY orders1, orders2

	INTO  TEMP  shipping

If you query with SELECT * from table shipping, you see the rows that Query Result 5-10 shows.

You can join a table to itself more than once. The maximum number of self-joins depends on the resources available to you.

The self-join in Query 5-11 creates a list of those items in the stock table that are supplied by three manufacturers. The self-join includes the last two conditions in the WHERE clause to eliminate duplicate manufacturer codes in rows that are retrieved.

SELECT s1.manu_code,  s2.manu_code,  s3.manu_code,  

	  s1.stock_num,  s1.description

	FROM  stock  s1,  stock  s2,  stock  s3

	WHERE  s1.stock_num  =  s2.stock_num

		AND  s2.stock_num  =  s3.stock_num

		AND  s1.manu_code  <  s2.manu_code

		AND  s2.manu_code  <  s3.manu_code

	ORDER  BY  stock_num


 




manu_code  manu_code  manu_code  stock_num  description          



HRO              HSK              SMT                              1  baseball  gloves

ANZ              NRG              SMT                              5  tennis  racquet  

ANZ              HRO              HSK                          110  helmet                  

ANZ              HRO              PRC                          110  helmet                  

ANZ              HRO              SHM                          110  helmet                  

ANZ              HSK              PRC                          110  helmet                  

ANZ              HSK              SHM                          110  helmet                  

ANZ              PRC              SHM                          110  helmet                  

HRO              HSK              PRC                          110  helmet                  

HRO              HSK              SHM                          110  helmet                  

HRO              PRC              SHM                          110  helmet                  

...
KAR              NKL              PRC                          301  running  shoes    

KAR              NKL              SHM                          301  running  shoes    

KAR              PRC              SHM                          301  running  shoes    

NKL              PRC              SHM                          301  running  shoes    



 Query Result 5-11 







 

If you want to select rows from a payroll table to determine which employees earn more than their manager, you can construct the self-join that Query 5-12a shows.

SELECT emp.employee_num,  emp.gross_pay,  emp.level,  

	  emp.dept_num,  mgr.employee_num,  mgr.gross_pay,  

	  mgr.dept_num, mgr.level

	FROM  payroll  emp,  payroll  mgr

	WHERE  emp.gross_pay  >  mgr.gross_pay  

		AND  emp.level  <  mgr.level

		AND emp.dept_num = mgr.dept_num

	ORDER  BY  4

Query 5-12b uses a correlated subquery to retrieve and list the 10 highest-priced items ordered.

SELECT  order_num,  total_price  

	FROM  items  a

	WHERE 10  >

		(SELECT  COUNT  (*)  

			FROM  items  b

			WHERE  b.total_price  <  a.total_price)

	ORDER BY total_price

Query 5-12b returns the 10 rows that Query Result 5-12 shows.

Query Result 5-12

You can create a similar query to find and list the 10 employees in the company who have the most seniority.

Correlated and uncorrelated subqueries are described in Subqueries in SELECT Statements.

You can use the hidden rowid column in a self-join to locate duplicate values in a table. In the following example, the condition x.rowid != y.rowid is equivalent to saying "row x is not the same row as row y."

Query 5-13 selects data twice from the cust_calls table, assigning it the table aliases x and y.

SELECT  x.rowid,  x.customer_num

	FROM  cust_calls  x,  cust_calls  y

	WHERE  x.customer_num  =  y.customer_num

		AND  x.rowid  !=  y.rowid

Query 5-13 searches for duplicate values in the customer_num column and for their rowids, finding the pair that Query Result 5-13 shows.

Query Result 5-13

For information about rowids, see Using Rowid Values In SELECT Statements.

The section Creating a Join shows how to create and use some simple joins. Whereas a simple join treats two or more joined tables equally, an outer join treats two or more joined tables asymmetrically. An outer join makes one of the tables dominant (also called preserved) over the other subservient tables.

The database server supports the following three basic types of outer joins:

• A simple outer join on two tables
• An outer join for a simple join to a third table
• An outer join of two tables to a third table

This section discusses these types of outer joins. For more information on the syntax, use, and logic of outer joins, see the Informix Guide to SQL: Syntax.

In a simple join, the result contains only the combinations of rows from the tables that satisfy the join conditions. Rows that do not satisfy the join conditions are discarded.

In an outer join, the result contains the combinations of rows from the tables that satisfy the join conditions. In addition, the result preserves rows from the dominant table that would otherwise be discarded because no matching row was found in the subservient table. The dominant-table rows that do not have a matching subservient-table row receive nulls for the columns of the subservient table.

An outer join applies conditions to the subservient table while it sequentially applies the join conditions to the rows of the dominant table. The conditions are expressed in a WHERE clause.

An outer join must have a SELECT clause, a FROM clause, and a WHERE clause. To transform a simple join into an outer join, insert the keyword OUTER directly before the name of the subservient tables in the FROM clause. As shown later in this section, you can include the OUTER keyword more than once in your query.

Before you use outer joins heavily, determine whether one or more simple joins can work. You can often use a simple join when you do not need supplemental information from other tables.

The examples in this section use table aliases for brevity. The section Using Aliases discusses table aliases.

Query 5-14 is an example of a simple join on the customer and cust_calls tables.

SELECT c.customer_num,  c.lname,  c.company,  

	  c.phone,  u.call_dtime,  u.call_descr

	FROM  customer  c,  cust_calls  u

	WHERE  c.customer_num  =  u.customer_num

Query 5-14 returns only those rows in which the customer has made a call to customer service, as Query Result 5-14 shows.

Query Result 5-14

Query 5-15 uses the same select list, tables, and comparison condition as the preceding example, but this time it creates a simple outer join.

SELECT c.customer_num,  c.lname,  c.company,  

	  c.phone,  u.call_dtime,  u.call_descr

	FROM  customer  c,  OUTER  cust_calls  u

	WHERE  c.customer_num  =  u.customer_num

The addition of the keyword OUTER before the cust_calls table makes it the subservient table. An outer join causes the query to return information on all customers, whether or not they have made calls to customer service. All rows from the dominant customer table are retrieved, and null values are assigned to columns of the subservient cust_calls table, as Query Result 5-15 shows.

Query Result 5-15

Outer Join for a Simple Join to a Third Table

Query 5-16 shows an outer join that is the result of a simple join to a third table. This second type of outer join is known as a nested simple join.

SELECT c.customer_num,  c.lname,  o.order_num,  

	  i.stock_num,  i.manu_code,  i.quantity

	FROM  customer  c,  OUTER  (orders  o,  items  i)

	WHERE  c.customer_num  =  o.customer_num

		AND  o.order_num  =  i.order_num

		AND  manu_code  IN  ('KAR',  'SHM')

	ORDER  BY  lname

Query 5-16 first performs a simple join on the orders and items tables, retrieving information on all orders for items with a manu_code of KAR or SHM. It then performs an outer join to combine this information with data from the dominant customer table. An optional ORDER BY clause reorganizes the data into the form that Query Result 5-16 shows.

Query Result 5-16

Query 5-17 shows an outer join that is the result of an outer join of each of two tables to a third table. In this third type of outer join, join relationships are possible only between the dominant table and the subservient tables.

SELECT c.customer_num,  lname,  o.order_num,  

	  order_date,  call_dtime

	FROM  customer  c,  OUTER  orders  o,  OUTER  cust_calls  x

	WHERE  c.customer_num  =  o.customer_num

		AND  c.customer_num  =  x.customer_num

	ORDER BY lname

	INTO  TEMP  service

Query 5-17 individually joins the subservient tables orders and cust_calls to the dominant customer table; it does not join the two subservient tables. An INTO TEMP clause selects the results into a temporary table for further manipulation or queries, as Query Result 5-17 shows.

Query Result 5-17

If Query 5-17 had tried to create a join condition between the two subservient tables o and x, as Query 5-18 shows, an error message would indicate the creation of a two-sided outer join.

	WHERE o.customer_num = x.customer_num

To achieve multiple levels of nesting, you can create a join that employs any combination of the three types of outer joins. Query 5-19 creates a join that is the result of a combination of a simple outer join on two tables and a second outer join.

SELECT c.customer_num,  lname,  o.order_num,  

	  stock_num,  manu_code,  quantity

	FROM  customer  c,  OUTER  (orders  o,  OUTER  items  i)

	WHERE  c.customer_num  =  o.customer_num

		AND  o.order_num  =  i.order_num

		AND  manu_code  IN  ('KAR',  'SHM')

	ORDER  BY  lname

Query 5-19 first performs an outer join on the orders and items tables, retrieving information on all orders for items with a manu_code of KAR or SHM. It then performs a second outer join that combines this information with data from the dominant customer table.

Query Result 5-19

You can state the join conditions in two ways when you apply an outer join to the result of an outer join to a third table. The two subservient tables are joined, but you can join the dominant table to either subservient table without affecting the results if the dominant table and the subservient table share a common column.