Aliases

You can assign aliases to the tables in the FROM clause of a SELECT statement to make multiple-table queries shorter and more readable. You can use an alias wherever the table name would be used, for instance, as a prefix to the column names in the other clauses.

Figure 1. Query

SELECT s.stock_num, s.manu_code, s.description, 
       s.unit_price, c.catalog_num, 
       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 the query above, 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 Figure 1 with the following query, which does not use aliases.

Figure 2. Query

SELECT stock.stock_num, stock.manu_code, stock.description,
       stock.unit_price, catalog.catalog_num, 
       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;

Figure 1 and Figure 2 are equivalent and retrieve the data that the following query shows.

Figure 3. Query result

stock_num    110
manu_code    HRO
description  helmet
unit_price   $260.00
catalog_num  10033
cat_advert   Lightweight Plastic with Vents Assures Cool 
             Comfort Without Sacrificing Protection
lead_time       4 

stock_num    110
manu_code    HSK
description  helmet
unit_price   $308.00
catalog_num  10034
cat_advert   Teardrop Design Used by Yellow Jerseys; You 
             Can Time the Difference
lead_time       5
⋮

You cannot use the ORDER BY clause for the TEXT column cat_descr or the BYTE column cat_picture.

You can use aliases to shorten your queries on tables that are not in the current database.

The following query joins columns from two tables that reside in different databases and systems, neither of which is the current database or system.

Figure 4. Query

SELECT order_num, lname, fname, phone
FROM masterdb@central:customer c, sales@western:orders o
   WHERE c.customer_num = o.customer_num
      AND order_num <= 1010;

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 the result shows.

Figure 5. Query result

order_num lname           fname           phone

    1001 Higgins         Anthony         415-368-1100
    1002 Pauli           Ludwig          408-789-8075
    1003 Higgins         Anthony         415-368-1100
    1004 Watson          George          415-389-8789
    1005 Parmelee        Jean            415-534-8822
    1006 Lawson          Margaret        415-887-7235
    1007 Sipes           Arnold          415-245-4578
    1008 Jaeger          Roy             415-743-3611
    1009 Keyes           Frances         408-277-7245
    1010 Grant           Alfred          415-356-1123

For more information on how to access tables that are not in the current database, see Access other database servers and the HCL Informix® Guide to SQL: Syntax.

You can also use synonyms as shorthand references to the long names of tables that are not in the current database as well as current tables and views. For details on how to create and use synonyms, see the IBM Informix Database Design and Implementation Guide.