Multiple-Table SELECT Statements
To select data from two or more tables, name these tables in the FROM clause. Add a WHERE clause to create a join condition between at least one related column in each table. This WHERE clause creates a temporary composite table in which each pair of rows that satisfies the join condition is linked to form a single row.
A simple join combines information from two or more tables based on the relationship between one column in each table. A composite join is a join between two or more tables based on the relationship between two or more columns in each table.
To create a join, you must specify a relationship, called a join condition, between at least one column from each table. Because the columns are being compared, they must have compatible data types. When you join large tables, performance improves when you index the columns in the join condition.
Data types are described in Chapter 2 of the Informix Guide to SQL: Reference; indexing is discussed in detail in the administrator's guide for your database server.
Creating a Cartesian Product
When you perform a multiple-table query that does not explicitly state a join condition among the tables, you create a Cartesian product. A Cartesian product consists of every possible combination of rows from the tables. This result is usually very large and unwieldy, and the data is inaccurate.
Query 2-71 selects from two tables and produces a Cartesian product.
Query 2-71
Although only 52 rows exist in the state table and only 28 rows exist in the customer table, the effect of Query 2-71 is to multiply the rows of one table by the rows of the other and retrieve an impractical 1,456 rows. Query 2-71 shows the first record that DB-Access displays. To view each subsequent record, highlight the NEXT menu and press RETURN.
Some of the data that is displayed in the concatenated rows is inaccurate. For example, although the city and state from the customer table indicate an address in California, the code and sname from the state table might be for a different state.
Creating a Join
Conceptually, the first stage of any join is the creation of a Cartesian product. To refine or constrain this Cartesian product and eliminate meaningless rows of data, include a WHERE clause with a valid join condition in your SELECT statement.
This section illustrates equi-joins, natural joins, and multiple-table joins. Additional complex forms, such as self-joins and outer joins, are covered in Chapter 3, "Composing Advanced SELECT Statements."
Equi-Join
An equi-join is a join based on equality or matching values. This equality is indicated with an equal sign (=) in the comparison operation in the WHERE clause, as Query 2-72 shows.
Query 2-72
Query 2-72 joins the manufact and stock tables on the manu_code column. It retrieves only those rows for which the values for the two columns are equal, as Query Result 2-72 shows.
In this equi-join, Query Result 2-72 includes the manu_code column from both the manufact and stock tables because the select list requested every column.
You can also create an equi-join with additional constraints, one where the comparison condition is based on the inequality of values in the joined columns. These joins use a relational operator other than the equal sign (=) in the comparison condition that is specified in the WHERE clause.
To join tables that contain columns with the same name, precede each column name with a period and its table name, as Query 2-73 shows.
Query 2-73
Query 2-73 joins on the customer_num column and then selects only those rows where the call_dtime in the cust_calls table is greater than or equal to the ship_date in the orders table. Query Result 2-73 shows the first row that DB-Access returns.
Natural Join
A natural join is structured so that the join column does not display data redundantly, as Query 2-74 shows.
Query 2-74
Like the example for equi-join, Query 2-74 joins the manufact and stock tables on the manu_code column. Because the select list is more closely defined, the manu_code is listed only once for each row retrieved, as Query Result 2-74 shows.
All joins are associative; that is, the order of the joining terms in the WHERE clause does not affect the meaning of the join.
Both of the statements in Query 2-75 create the same natural join.
Query 2-75
SELECT catalog.*, description, unit_price, unit, unit_descr
FROM catalog, stock
WHERE catalog.stock_num = stock.stock_num
AND catalog.manu_code = stock.manu_code
AND catalog_num = 10017
SELECT catalog.*, description, unit_price, unit, unit_descr
FROM catalog, stock
WHERE catalog_num = 10017
AND catalog.manu_code = stock.manu_code
AND catalog.stock_num = stock.stock_num
Each statement retrieves the row that Query Result 2-75 shows.
Query Result 2-75 includes a TEXT column, cat_descr; a BYTE column, cat_picture; and a VARCHAR column, cat_advert.
Multiple-Table Join
A multiple-table join connects more than two tables on one or more associated columns; it can be an equi-join or a natural join.
Query 2-76 creates an equi-join on the catalog, stock, and manufact tables and retrieves the following row:
Query 2-76
Query 2-76 retrieves the rows Query Result 2-76 shows.
The manu_code is repeated three times, once for each table, and stock_num is repeated twice.
Because of the considerable duplication of a multiple-table query in Query 2-76, define the SELECT statement more closely by including specific columns in the select list, as Query 2-77 shows.
Query 2-77
Query 2-77 uses a wildcard to select all columns from the table with the most columns and then specifies columns from the other two tables. Query Result 2-77 shows the natural join produced by Query 2-77. It displays the same information as the previous example, but without duplication.
Some Query Shortcuts
You can use aliases, the INTO TEMP clause, and display labels to speed your way through joins and multiple-table queries and to produce output for other uses.
Using Aliases
You can make multiple-table queries shorter and more readable by assigning aliases to the tables in a SELECT statement. An alias is a word that immediately follows the name of a table in the FROM clause. You can use it wherever the table name would be used, for instance, as a prefix to the column names in the other clauses.
Query 2-78a
SELECT s.stock_num, s.manu_code, s.description,
s.unit_price, s.unit, c.catalog_num,
c.cat_descr, c.cat_advert, m.lead_time
FROM stock s, catalog c, manufact m
WHERE s.stock_num = c.stock_num
AND s.manu_code = c.manu_code
AND s.manu_code = m.manu_code
AND s.manu_code IN ('HRO', 'HSK')
AND s.stock_num BETWEEN 100 AND 301
ORDER BY catalog_num
The associative nature of the SELECT statement allows you to use an alias before you define it. In Query 2-78a, the aliases s for the stock table, c for the catalog table, and m for the manufact table are specified in the FROM clause and used throughout the SELECT and WHERE clauses as column prefixes.
Compare the length of Query 2-78a with Query 2-78b, which does not use aliases.
Query 2-78b
SELECT stock.stock_num, stock.manu_code, stock.description,
stock.unit_price, stock.unit, catalog.catalog_num,
catalog.cat_descr, catalog.cat_advert,
manufact.lead_time
FROM stock, catalog, manufact
WHERE stock.stock_num = catalog.stock_num
AND stock.manu_code = catalog.manu_code
AND stock.manu_code = manufact.manu_code
AND stock.manu_code IN ('HRO', 'HSK')
AND stock.stock_num BETWEEN 100 AND 301
ORDER BY catalog_num
Query 2-78a and Query 2-78b are equivalent and retrieve the data that is shown in Query Result 2-78.
You cannot use the ORDER BY clause for the TEXT column cat_descr or the BYTE column cat_picture. You can also use aliases to shorten your queries on external tables that reside in external databases.
Query 2-79 joins columns from two tables that reside in different databases and systems, neither of which is the current database or system.
Query 2-79
By assigning the aliases c and o to the long database@system:table names, masterdb@central:customer and sales@western:orders, respectively, you can use the aliases to shorten the expression in the WHERE clause and retrieve the data as Query Result 2-79 shows.
For more information on external tables and external databases, see Chapter 1 in the Informix Guide to SQL: Syntax.
You can also use synonyms as shorthand references to the long names of external and current tables and views. For details on how to create and use synonyms, see the CREATE SYNONYM statement in Chapter 1 of the Informix Guide to SQL: Syntax.
The INTO TEMP Clause
By adding an INTO TEMP clause to your SELECT statement, you can temporarily save the results of a multiple-table query in a separate table that you can query or manipulate without modifying the database. Temporary tables are dropped when you end your SQL session or when your program or report terminates.
The following example creates a temporary table called stockman and stores the results of the query in it. Because all columns in a temporary table must have names, the alias adj_price is required.
You can now query the stockman table and join it with other tables, which avoids a multiple sort and lets you move more quickly through the database. Temporary tables are discussed at greater length in the INFORMIX-Universal Server Administrator's Guide. Query 2-80 shows how to view the contents of the stockman temporary table, which the preceding SELECT statement returns.
Query 2-80
SELECT * FROM stockman
|
