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.

This syntax fragment is part of the WHERE clause of SELECT.

^{(explicit id specj002)
specj002}

Join

[ <Data Source> ]

column

<Relational Operator> ^[]

[ <Data Source> ]

column

Data Source

{ alias . | external . | table . | view . | synonym . }


Element	Description	Restrictions	Syntax
alias	Temporary alternative name declared in the FROM clause for a table or view	See the Self joins section; FROM Clause	Identifier
column	Column of a table or view to be joined	Must exist in the table or view	Identifier
external	External table from which to retrieve data	External table must exist. Must include appropriate qualifiers.	Database Object Name
synonym, table, view	Name of a synonym, table, or view to be joined in the query	Synonym and the table or view to which it points must exist. Must include appropriate qualifiers.	Database Object Name

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. You can create two-table joins, multiple-table joins, self-joins, and outer joins (Informix®-extension syntax).

Two-table joins

The following example shows a two-table join:

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

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

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;

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;

For the syntax of outer joins, see Informix-Extension Outer Joins.

¹ See Relational Operator