This chapter discusses the uses of 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.

Self-Joins

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 shown in Chapter 2, "Composing Simple SELECT Statements," in this manual and discussed in Chapter 1 of 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 3-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.

Query 3-9

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

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 rename at least one set of columns by assigning them display labels. Otherwise, the duplicate column names cause an error and the temporary table is not created.

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

Query 3-10

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

Query Result 3-10

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 3-11 creates a list of those items in the stock table that are supplied by three manufacturers. By including the last two conditions in the WHERE clause, it eliminates duplicate manufacturer codes in rows retrieved.

Query 3-11

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

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 3-12a shows.

Query 3-12a

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 3-12b

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

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".

Using Rowid Values

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 3-13 selects data twice from the cust_calls table, assigning it the table aliases x and y.

Query 3-13

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

You can write the last condition as Query 3-13 shows.

AND  x.rowid  !=  y.rowid



AND  NOT  x.rowid  =  y.rowid

Query 3-14

SELECT  x.customer_num,  x.call_dtime  

	FROM  cust_calls  x

	WHERE 1  <

		(SELECT  COUNT  (*)  FROM  cust_calls  y

			WHERE  x.customer_num  =  y.customer_num)

You can use the rowid, shown earlier in a self-join, to locate the internal record number that is associated with a row in a database table. The rowid is, in effect, a hidden column in every table. The sequential values of rowid have no special significance and can vary depending on the location of the physical data in the chunk. Your rowid might vary from the example shown. The use of rowid is discussed in detail in the INFORMIX-Universal Server Administrator's Guide.

Query 3-15 uses the rowid and the wildcard asterisk symbol (*) in the SELECT clause to retrieve every row in the manufact table and their corresponding rowids.

Query 3-15

SELECT  rowid,  *  FROM  manufact 





Query Result 3-15 






 

Query 3-16

SELECT  rowid,  manu_code  FROM  manufact

You can use the rowid in the WHERE clause to retrieve rows based on their internal record number. This method is handy when no other unique column exists in a table. Query 3-17 uses a rowid from Query 3-16.

Query 3-17

SELECT  *  FROM  manufact WHERE  rowid  =  263

Using the USER Function

Query 3-18 assigns the label username to the USER expression column and returns this information about the cust_calls table.

Query 3-18

SELECT  USER  username,  rowid  FROM  cust_calls

Query Result 3-18

You can also use the USER function in a WHERE clause when you select the rowid.

Query 3-19 returns the rowid for only those rows that are inserted or updated by the user who performs the query.

Query 3-19

SELECT  rowid  FROM  cust_calls  WHERE  user_id  =  USER      

Using the DBSERVERNAME Function

Query 3-20 finds the database server name and the user name as well as the rowid and the tabid, which is the serial-interval table identifier for system catalog tables.

Query 3-20

SELECT  DBSERVERNAME  server,  tabid,  rowid,  USER  username  

	FROM  systables

	WHERE  tabid  >=  105 OR  rowid  <=  260

	ORDER  BY  rowid

Never store a rowid in a permanent table or attempt to use it as a foreign key because the rowid can change. For example, if a table is dropped and then reloaded from external data, all the rowids are different.

USER and DBSERVERNAME are discussed in Chapter 2, "Composing Simple SELECT Statements."

Outer Joins

Outer joins occur in four basic types:

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

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. Rows from the dominant table that would otherwise be discarded are preserved, even though no matching row was found in the subservient table. The dominant-table rows that do not have a matching subservient-table row receive a row of nulls before the selected columns are projected.

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 often can get by with a simple join when you do not need supplemental information from other tables.

The examples in this section use table aliases for brevity. Table aliases are discussed in Chapter 2, "Composing Simple SELECT Statements."

Simple Join

Query 3-21

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

Simple Outer Join on Two Tables

Query 3-22

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

Query Result 3-22

Outer Join for a Simple Join to a Third Table

Query 3-23

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

Outer Join for an Outer Join to a Third Table

Query 3-24

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 Result 3-24

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.

Outer Join of Two Tables to a Third Table

Query 3-25

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 Result 3-25

If Query 3-25 had tried to create a join condition between the two subservient tables o and x, as Query 3-26 shows, an error message would have indicated the creation of a two-sided outer join.

Query 3-26

	WHERE o.customer_num = x.customer_num