Specifying a Join in the WHERE Clause

You join two tables by creating a relationship in the WHERE clause between at least one column from one table and at least one column from another. The join creates a temporary composite table where each pair of rows (one from each table) that satisfies the join condition is linked to form a single row.

Join:
 
                                                        (1)
|--+-----------------+--column--| Relational Operator |--------->
   '-| Data Source |-'
 
>--+-----------------+--column----------------------------------|
   '-| Data Source |-'
 
Data Source:
 
|--+-alias .-----------+----------------------------------------|
   +-table .-----------+
   +-view .------------+
   +-synonym .---------+
   |  (2)              |
   '--------external .-'
 

1. See Relational Operator
2. Extended Parallel Server only

Rows from the tables or views are joined when there is a match between the values of specified columns. When the columns to be joined have the same name, you must qualify each column name with its data source.

Two-Table Joins

You can create two-table joins, multiple-table joins, self-joins, and outer joins (Informix-extension syntax). The following example shows a two-table join:

SELECT order_num, lname, fname FROM customer, orders
   WHERE customer.customer_num = orders.customer_num

Multiple-Table Joins

A multiple-table join is a join of more than two tables. Its structure is similar to the structure of a two-table join, except that you have a join condition for more than one pair of tables in the WHERE clause. When columns from different tables have the same name, you must qualify the column name with its associated table or table alias, as in table.column. For the full syntax of a table name, see Database Object Name.

The following multiple-table join yields the company name of the customer who ordered an item as well as its stock number and manufacturer code:

SELECT DISTINCT company, stock_num, manu_code
   FROM customer c, orders o, items i
   WHERE c.customer_num = o.customer_num
      AND o.order_num = i.order_num

Self-Joins

You can join a table to itself. To do so, you must list the table name twice in the FROM clause and assign it two different table aliases. Use the aliases to refer to each of the two tables in the WHERE clause. The next example is a self-join on the stock table. It finds pairs of stock items whose unit prices differ by a factor greater than 2.5. The letters x and y are each aliases for the stock table.

SELECT x.stock_num, x.manu_code, y.stock_num, y.manu_code
   FROM stock x, stock y WHERE x.unit_price > 2.5 * y.unit_price

Extended Parallel Server does not support self-joins with an external table.

Informix-Extension Outer Joins

The next outer join lists the company name of the customer and all associated order numbers, if the customer has placed an order. If not, the company name is still listed, and a NULL value is returned for the order number.

SELECT company, order_num FROM customer c, OUTER orders o
   WHERE c.customer_num = o.customer_num

In Extended Parallel Server, you cannot use an external table as the outer table in an outer join.

For more information about outer joins, see the IBM Informix Guide to SQL: Tutorial.

GROUP BY Clause

GROUP BY Clause:
 
             .-,------------------------------------.
             V                                      |
|--GROUP BY----+-+----------------------+--column-+-+-----------|
               | |                 (1)  |         |
               | '-| Data Source |------'         |
               |  (2)                             |
               '--------select_number-------------'