Informix Select

Introduction

The SELECT statement is constructed of clauses that let you look at data in a relational database.

Selection is implemented through the WHERE clause of a SELECT statement.

SELECT * FROM customer
WHERE state = 'NJ'

Projection

Projection is defined as taking a vertical subset from the columns of a single table that retains the unique rows.

SELECT UNIQUE city, state, zipcode
FROM customer

The most common kind of SELECT statement uses both selection and projection.

SELECT UNIQUE city, state, zipcode
FROM customer
WHERE state = 'NJ'

Join

A join occurs when two or more tables are connected by one or more columns in common, creating a new table of results.

SELECT UNIQUE city, state, zipcode, sname
FROM customer, state
WHERE customer.state = state.code

Select all columns

Using the Asterisk Symbol (*). The following query specifies all the columns in the manufact table in a select list. A select list is a list of the column names or expressions that you want to project from a table.

SELECT  *  FROM  manufact

Reordering the Columns

SELECT  manu_name,  manu_code,  lead_time
FROM  manufact

Sorting the Rows

You can add an ORDER BY clause to your SELECT statement to direct the system to sort the data in a specific order.You must include the columns that you want to use in the ORDER BY clause in the select list.

SELECT  manu_code,  manu_name,  lead_time
FROM  manufact
ORDER  BY  lead_time [ASCE | DESC]

You can also ORDER BY two or more columns, creating a nested sort. The default is still ascending, and the column that is listed first in the ORDER BY clause takes precedence.

SELECT  *  FROM  stock
ORDER  BY  manu_code,  unit_price

Using substrings

To select part of the value of a CHARACTER column, include a substring in the select list. The following query selects the first three characters of the zipcode column

SELECT  zipcode[1,3],  customer_num  
FROM  customer
ORDER  BY  zipcode

Using the WHERE clause.

SELECT  customer_num,  call_code,  call_dtime,  res_dtime
FROM  cust_calls
WHERE  user_id  =  'maryj'

Specify rows in a WHERE clause.

SELECT  catalog_num,  stock_num,  manu_code,  cat_advert
FROM  catalog
WHERE  catalog_num  BETWEEN  10005  AND  10008

the same as

SELECT  catalog_num,  stock_num,  manu_code,  cat_advert
FROM  catalog
WHERE  catalog_num  >=  10005  AND  catalog_num  <=  10008

The following query uses the keywords NOT BETWEEN to exclude specific rows.

SELECT  fname,  lname,  company,  city,  state
FROM  customer
WHERE  zipcode  NOT  BETWEEN  '94000'  AND  '94999'
ORDER  BY  state

Null Values

Use the IS NULL or IS NOT NULL option to check for null values. A null value represents either the absence of data or an unknown value.

SELECT  order_num,  customer_num,  po_num,  ship_date
FROM  orders
WHERE  paid_date  IS  NULL
ORDER  BY  customer_num

Using Boolean expressions.

SELECT  order_num,  customer_num,  po_num,  ship_date
FROM  orders
WHERE  paid_date  IS  NULL
AND  ship_date  IS  NOT  NULL
ORDER  BY  customer_num

Using Variable-Text Searches

LIKE for variable-text queries

SELECT  *
FROM  orders
WHERE ship_instruct LIKE('express')
SELECT  *
FROM  orders
WHERE ship_instruct LIKE '%xp%'

MATCHES is an Informix extension.

SELECT  *
FROM  orders
WHERE ship_instruct MATCHES('express')

Wildcards Table

Symbol Meaning
LIKE
% Evaluates to zero or more characters
_ Evaluates to a single character
\ Escapes special significance of next character
MATCHES
* Evaluates to zero or more characters
? Evaluates to a single character (except null)
[] Evaluates to a single character or range of values
\ Escapes special significance of next character

Comparing for Special Characters

The following query uses the keyword ESCAPE with LIKE or MATCHES so you can protect a special character from misinterpretation as a wildcard symbol.

SELECT  *  FROM  cust_calls
WHERE  res_descr  LIKE  '%!%%'  ESCAPE  '!'

The following query specifies a range of characters in a column.

SELECT  catalog_num,  stock_num,  manu_code,  cat_advert, cat_descr
FROM  catalog
WHERE  cat_advert[1,4]  =  'High'

You can use the SELECT clause of a SELECT statement to perform computations on column data and to display information derived from the contents of one or more columns.

SELECT  stock_num,  description,  unit,  unit_descr, unit_price,  
unit_price  *  1.07 TAXES
FROM  stock
WHERE  unit_price  >=  400

Functions

COUNT: The following query counts and displays the total number of rows in the stock table.

SELECT  COUNT(*)
FROM  stock

By including the keyword DISTINCT (or its synonym UNIQUE) and a column name , you can tally the number of different manufacturer codes in the stock table.

SELECT  COUNT(DISTINCT  manu_code)
FROM  stock

AVG: The following query computes the average unit_price of all rows in the stock table.

SELECT  AVG (unit_price)
FROM  stock

Multiple-Table SELECT Statements

A Cartesian product consists of every possible combination of rows from the tables.

SELECT  *  FROM  customer,  state

Equi-Join: An equi-join is a join based on equality or matching values.

SELECT  *  FROM  manufact,  stock
WHERE  manufact.manu_code  =  stock.manu_code

The following query joins tables that contain columns with the same name, precede each column name with a period and its table name.

SELECT  order_num,  order_date,  ship_date,  cust_calls.*
 
    FROM  orders,  cust_calls
 
    WHERE  call_dtime  >=  ship_date
 
        AND  cust_calls.customer_num  =  orders.customer_num
 
    ORDER  BY  customer_num

GROUP BY

The GROUP BY clause divides a table into sets.

The following query retrieves the number of items and the total price of all items for each order. The GROUP BY clause causes the rows of the items table to be collected into groups, each group composed of rows that have identical order_num values (that is, the items of each order are grouped together). After you form the groups, the aggregate functions COUNT and SUM are applied within each group.

SELECT  order_num,  COUNT  (*)  number,  SUM  (total_price)  price
 
    FROM  items
 
    GROUP  BY  order_num

HAVING Clause

The HAVING clause usually complements a GROUP BY clause by applying one or more qualifying conditions to groups after they are formed.

The following query returns the average total price per item on all orders that have more than two items.

SELECT  order_num,  COUNT(*)  number,  AVG  (total_price)  average
 
    FROM  items
 
    GROUP  BY  order_num
 
    HAVING  COUNT(*)  >  2

Self-Joins

Joining a table to itself can be useful when you want to compare values in a column to other values in the same column.
The following query 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.

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

Outer Joins

In an outer join, the result contains the combination 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.

Simple Join

The following query returns only those rows in which the customer has made a call to customer service.

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

The following query uses the same select list, tables, and comparison condition as the preceding example, but this time it creates a simple outer join. The addition of the keyword OUTER in front of the cust_calls table makes it the subservient table. An outer join causes the query to return information on all customers, whether or not they have made calls to customer service. All rows from the dominant customer table are retrieved, and null values are assigned to corresponding rows from the subservient cust_calls table.

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

Outer Join for a Simple Join to a Third Table

The following query shows an outer join that is the result of a simple join to a third table. This second type of outer join is known as a nested simple join.

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

The following query creates an outer join that is the result of an outer join to a third table. This third type is known as a nested outer join.

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

Outer Join of Two Tables to a Third Table

The following query shows an outer join that is the result of an outer join of each of two tables to a third table. In this fourth type of outer join, join relationships are possible only between the dominant table and the subservient tables.

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

Subqueries in SELECT Statements

Using ALL

Use the keyword ALL preceding a subquery to determine whether a comparison is true for every value returned. If the subquery returns no values, the search condition is true. (If it returns no values, the condition is true of all the zero values.)

The following query lists the following information for all orders that contain an item for which the total price is less than the total price on every item in order number 1023.

SELECT  order_num,  stock_num,  manu_code,  total_price
 
    FROM  items
 
    WHERE  total_price  <  ALL
 
        (SELECT  total_price  FROM  items  
 
            WHERE  order_num  =  1023)

Using ANY

Use the keyword ANY (or its synonym SOME) preceding a subquery to determine whether a comparison is true for at least one of the values returned. If the subquery returns no values, the search condition is false. (Because no values exist, the condition cannot be true for one of them.)

The following query finds the order number of all orders that contain an item for which the total price is greater than the total price of any one of the items in order number 1005.

SELECT  DISTINCT  order_num
 
    FROM  items
 
    WHERE  total_price  >  ANY
 
        (SELECT  total_price  
 
            FROM  items
 
            WHERE  order_num  =  1005)

Single-Valued Subqueries

You do not need to include the keyword ALL or ANY if you know the subquery can return exactly one value to the outer-level query. A subquery that returns exactly one value can be treated like a function. This kind of subquery often uses an aggregate function because aggregate functions always return single values.

The following query uses the aggregate function MAX in a subquery to find the order_num for orders that include the maximum number of volleyball nets.

SELECT  order_num  FROM  items
 
    WHERE  stock_num  =  9  
 
        AND  quantity  =
 
            (SELECT  MAX  (quantity)  
 
                FROM  items
 
                WHERE  stock_num  =  9)

Correlated Subqueries

The following query is an example of a correlated subquery, which returns a list of the 10 earliest shipping dates in the orders table. It includes an ORDER BY clause after the subquery to order the results because you cannot include ORDER BY within a subquery.

SELECT  po_num,  ship_date  FROM  orders  main
 
    WHERE  10  >
 
        (SELECT  COUNT  (DISTINCT  ship_date)
 
            FROM  orders  sub
 
            WHERE  sub.ship_date  >  main.ship_date)
 
            AND ship_date IS NOT NULL
 
    ORDER  BY  ship_date,  po_num

Using EXISTS

The keyword EXISTS is known as an existential qualifier because the subquery is true only if the outer SELECT finds at least one row.

SELECT  UNIQUE  manu_name,  lead_time  
 
    FROM  manufact
 
    WHERE  EXISTS
 
        (SELECT  *  FROM  stock  
 
            WHERE  description  MATCHES  '*shoe*'
 
                AND  manufact.manu_code  =  stock.manu_code)

Set Operations

Union

The union operation uses the UNION keyword, or operator, to combine two queries into a single compound query. You can use the UNION keyword between two or more SELECT statements to unite them and produce a temporary table that contains rows that exist in any or all of the original tables.

The following query performs a union on the stock_num and manu_code columns in the stock and items tables.

SELECT  DISTINCT  stock_num,  manu_code
 
    FROM  stock
 
    WHERE  unit_price  <  25.00
 
UNION
 
SELECT  stock_num,  manu_code
 
    FROM  items
 
    WHERE  quantity  >  3

By default, the UNION keyword excludes duplicate rows. Add the optional keyword ALL, to retain the duplicate values.

SELECT  stock_num,  manu_code
    FROM  stock
    WHERE  unit_price  <  25.00
 
UNION  ALL
 
SELECT  stock_num,  manu_code
    FROM  items
    WHERE  quantity  >  3  
    ORDER  BY  2
    INTO  TEMP  stockitem

Intersection - Keyword EXISTS or IN

The intersection of two sets of rows produces a table containing rows that exist in both the original tables. Use the keyword EXISTS or IN to introduce subqueries that show the intersection of two sets.

The following query is an example of a nested SELECT statement that shows the intersection of the stock and items tables.

      SELECT stock_num, manu_code, unit_price
      FROM stock
      WHERE stock_num IN
      (SELECT stock_num FROM items)
      ORDER BY stock_num

Difference - Keyword NOT EXISTS or NOT IN

The difference between two sets of rows produces a table containing rows in the first set that are not also in the second set. Use the keywords NOT EXISTS or NOT IN to introduce subqueries that show the difference between two sets.

SELECT stock_num, manu_code, unit_price
    FROM stock
    WHERE stock_num NOT IN
        (SELECT stock_num FROM items)
    ORDER BY stock_num

Useful Topics

update statistics Informix

More Examples of Informix in the Following Books

Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-Share Alike 2.5 License.