Statements that access data

xfODBC supports the following, which enable you to access data:

For links to more information on xfODBC’s support for SQL, see Appendix B: SQL Support.

SELECT

The SELECT command enables you to create queries (SQL statements that retrieve data from a database). 

SELECT [SKIP n] [TOP n] [ALL|DISTINCT] column_list
  FROM table_list
  [WHERE search_conditions]
  [GROUP BY column_id[, ...]
  [HAVING search_conditions]
  [ORDER BY sort_specification_1 [ASC|DESC][, sort_specification_2 [ASC|DESC], ...]]
  [UNION [ALL] sel_stmt] [...]

where

column_list

One or more column specifications. This can include column names, CASE statements, functions or other value expressions (which can be literals or calculations—e.g., or_qty * 3). A column name can be preceded by a table name or by an owner and table name: 

[[owner_name.]table_name.]column_name [column_alias]

For information on column aliases, see Column aliases.

table_list

One or more table or view names. An owner name can precede a table or view name.

[owner_name.]table_name [table_alias]

Note that you can also use inline views (see Creating subqueries and inline views). For information on table aliases, see Table aliases.

search_conditions

One or more search criteria for rows.

column_id

A column name, a column name preceded by a table name, or a column name preceded by an owner name and a table name:

[[owner_name.]table_name.]column_name

sort_specification_n

The column name or select list position number that will be used to sort the rows plus an optional ASC for ascending order (the default) or DESC for descending order.

sel_stmt

A SELECT statement whose results will be combined with other SELECT statements connected with the UNION operator.

When you create a SELECT statement, you specify which rows and columns you want the statement to retrieve. You can retrieve a subset of rows from one or more tables, you can retrieve a subset of columns from one or more tables, and you can link rows from two or more tables. SELECT statements can contain

You can also use

table_name.*

SELECT statements can be used in definitions of views and as subqueries. You can also use them to create derived tables (inline views) by including them in FROM clauses that are part of SQL92 outer joins. See Creating subqueries and inline views and FROM clauses in SQL92 outer joins.

Note the following: 

SELECT table1.col_1, table2.col_1 FROM table1, table2

SELECT "date", "order" FROM orders

See ODBC reserved words for a list of reserved words.

The following select one, several, or all columns from a table:

SELECT col_1 FROM my_table
SELECT col_1, col_2, col_3 FROM my_table
SELECT * FROM my_table
SELECT table1.* FROM table1
SELECT owner_name.table1.* FROM table1

The following are similar examples with double quotes around some identifiers:

SELECT "COL_1" FROM "MY_TABLE"
SELECT "COL_1", col_2 FROM my_table
SELECT "OWNER_NAME".table1.* FROM table1
SELECT "OWNER_NAME"."TABLE1".* FROM "TABLE1"

The following select one, several, or all columns from two tables:

SELECT col, col_1 FROM table1, table2
SELECT table1.col_1, table2.col_1 FROM table1, table2
SELECT owner_name.table1.col_1, owner_name.table2.col_1
    FROM table1, table2
SELECT * FROM table1, table2

SELECT ALL

SELECT ALL returns every row that meets a query’s criteria, even if some rows are duplicates. This is the default for SELECT statements, so you don’t need to include “ALL”. For example, the following statements produce identical results:

SELECT ALL or_vendor, or_edate FROM orders
SELECT or_vendor, or_edate FROM orders

SELECT DISTINCT

SELECT DISTINCT returns only one copy of duplicate rows. For example, if the orders table has two or more records whose or_vendor and or_edate columns are identical, the following returns only one row for those records. 

SELECT DISTINCT or_vendor, or_edate FROM orders

SKIP

SKIP n

SKIP is a sub-clause that specifies how many rows (n) to trim from the beginning of the result set for a query. If the result set (before SKIP is applied) is equal to or less than n, no rows will be returned. A SKIP clause must immediately follow the SELECT keyword.

SKIP can be used with TOP for paging, which is particularly useful for creating cached result sets for websites that use ADO.NET.

For example, the following returns all but the four tallest plants in the sample database:

SELECT SKIP 4 DISTINCT in_name 
    FROM plants 
    ORDER BY in_maxhigh DESC
Note
  • SKIP and TOP can be used only once in a query. For example, you cannot use SKIP or TOP in the main query and then use it again in a subquery.
  • If a query has both SKIP and TOP, SKIP is used first to trim the result set, and TOP is applied if any rows remain.
  • SKIP and TOP are evaluated after GROUP BY and ORDER BY clauses.

TOP

TOP n

TOP is a sub-clause that specifies the number of rows (n) to be returned. For example, the following returns the names of the four tallest plants in the sample database:

SELECT TOP 4 DISTINCT in_name 
    FROM plants 
    ORDER BY in_maxhigh DESC

If the result set (before TOP is applied) is equal to or less than n, all rows are returned. See SKIP for more information.

Creating subqueries and inline views

A subquery (i.e., a nested query) is a SELECT statement embedded within an SQL statement and enclosed in parentheses (except in INSERT statements). Like joins, subqueries enable you to query multiple tables, though there are other uses for subqueries. There are two basic types:

SELECT or_number FROM orders 
    WHERE or_price > (SELECT AVG(in_price) FROM plants)

SELECT cust_zip AS all_zips, cust_city AS all_city FROM (
    SELECT cust_zip, cust_city FROM customers UNION ALL 
    SELECT vend_zip, vend_city FROM vendors)

Note the following:

SELECT plants.in_name FROM plants 
    WHERE plants.in_itemid IN (SELECT or_item FROM orders)

However, using an IN in a WHERE clause limits optimization. This query is better written this way: 

SELECT plants.in_name FROM plants, (SELECT or_item FROM orders) oritem
    WHERE plants.in_itemid = oritem.or_item

For an example that includes GROUP BY clauses in inline views that are part of a UNION clause, see GROUP BY.

Example subqueries

The following retrieves the customers who currently have orders in the order table: 

SELECT customers.cust_name FROM customers, (SELECT or_customer FROM orders) orcust 
    WHERE customers.cust_key = orcust.or_customer

The next example, however, does not work because it has a subquery in the select list (which isn’t supported):

SELECT customer_id, customer_state, (SELECT SUM(quantity) FROM orders 
    WHERE orders.customer_number=customers.customer_id) 
        AS orders FROM customers

The next query is similar to the preceding query (although it returns rows with null in the second column), but in this case the subquery is in the FROM clause, which creates an inline view, so it is valid.

SELECT o.cust_key, o.cust_state, s.c
    FROM customers o, (SELECT or_customer, SUM(or_qty) c FROM orders
        GROUP BY or_customer) s
    WHERE o.cust_key = s.or_customer

The following example shows another use for subqueries. For this statement, the table subquery isn’t used to query multiple tables, but it is necessary because aggregate functions (e.g., AVG) can’t be used in WHEN clauses. (They can’t be used in WHERE or ORDER BY clauses either.)

SELECT in_maxhigh,
    CASE
        WHEN in_maxhigh > (SELECT AVG(in_maxhigh) FROM plants)
            THEN 'Tall'
        END 
    FROM plants

Joins

Joins are a way of returning records from two or more tables—in most cases, records that in some way match. For example, if you have a plant table and an order table, each with a field that stores vendor IDs, you can use a join to return records that have matching vendors. There are three types of join: inner, outer, and full.

Inner joins return only matching records. If a record in any table in the join doesn’t have a match in the other tables, the record is ignored. xfODBC supports SQL89 inner joins through the WHERE command and SQL92 inner joins through the INNER JOIN command. See SQL89 inner joins and SQL92 inner joins for examples.

Note

We recommend using SQL92 syntax rather than SQL89 syntax, and we don’t recommend combining the two forms (i.e., using both FROM and WHERE clauses to define the join and restriction criteria). This will generally reduce performance. See Avoid mixing SQL92 and SQL89 syntax.

Outer joins return all records from one table but only records with matches from the other table in the join. SQL89 syntax doesn’t support outer joins (though some databases have extensions to SQL89 syntax that enable you to create these), but SQL92 does support these through the LEFT [OUTER] JOIN and RIGHT [OUTER] JOIN commands.

With SQL92 syntax, you determine which table will supply a full set of records by where you specify the table in a left outer join or a right outer join:

You can also create full outer joins, which return all records from both tables in the join. SQL89 syntax doesn’t include any special keywords for this, but you can get similar results by using the UNION operator. SQL92 syntax, however, includes the FULL [OUTER] JOIN keyword. 

FULL [OUTER] JOIN table_name ON column1 = column2

where column1 is a column in table_name and column2 is a column in another FROM clause table. See SQL92 full joins for an example.

Note

Be careful nesting joins. This may reduce performance.

Note the following for joins:

{oj orders RIGHT JOIN vendors ON or_vendor = vend_key}
orders RIGHT OUTER JOIN vendors ON or_vendor = vend_key

ON a.f1=b.indexcol AND a.f1='5' AND b.f2 IN('d','e','f')

For multi-table joins, put filter criteria that apply to a leg of the join in the ON clause for that leg. This improves query optimization, and it makes the query easier to read. However, you may want to put top-level conditions (conditions for the first table in the join) in a WHERE clause. In the following, for example, filter conditions for columns in table2 and table3 are in ON clauses, while the condition for table1 is in the WHERE clause.

SELECT
    t1.id,
    t2.id,
    t3.id
FROM
    table1 t1
    INNER JOIN table2 t2
        ON t1.id = t2.id
        AND t2.num < 200
    INNER JOIN table3 t3
        ON t1.num = t3.num
        AND t3.num < 300
    INNER JOIN table4 t4
        ON t3.id = t4.id
WHERE t1.num < 1000

For information on optimizing join performance, see Creating efficient SQL statements.

SQL89 inner joins

xfODBC supports SQL89 inner joins through the WHERE command. Use a WHERE clause to specify which columns should match. The following example is a SQL89 inner join. It selects only those records from the m1 and m2 tables whose id and num columns match.

SELECT 
    m1.id,
    m1.num,
    m1.alpha,
    m2.id,
    m2.num,
    m2.alpha
FROM
    multiop  m1,
    multiop2 m2
WHERE
    m1.id = 5
    AND  m1.num = 50
    AND  m1.id = m2.id
    AND  m1.num = m2.num

SQL92 inner joins

xfODBC supports SQL92 inner joins through INNER JOIN clauses:

INNER JOIN table_name ON column1 = column2

where column1 is a column in table_name and column2 is a column in another FROM clause table.

INNER JOIN returns only records that match records in the other table of the join. For example, the following query returns only those records from the m1 and m2 tables whose id and num columns match:

SELECT 
    m1.id, 
    m1.num, 
    m1.alpha, 
    m2.id, 
    m2.num, 
    m2.alpha 
FROM 
    multiop m1
      INNER JOIN multiop2 m2 
        ON    m1.id = m2.id 
        AND   m1.num = m2.num

Here’s the same FROM clause using the ODBC escape sequence form:

FROM 
    multiop m1
      {oj INNER JOIN multiop2 m2 
        ON   m1.id = m2.id 
        AND  m1.num = m2.num}

SQL92 left outer joins

xfODBC supports SQL92 left outer joins through LEFT OUTER JOIN clauses:

LEFT [OUTER] JOIN table_name ON column1 = column2

where column1 is a column in table_name and column2 is a column in another FROM clause table.

LEFT [OUTER] JOIN returns all the records in the first table (the table that precedes “LEFT OUTER JOIN”) and matching records from the table on the right (the table that follows “LEFT OUTER JOIN”). For example, the following query returns the specified fields for all records in the vendors table and any records from the orders table that match the ON criteria.

SELECT 
    orders.or_item, 
    orders.or_number, 
    vendors.vend_name
FROM 
    {OJ vendors 
      LEFT OUTER JOIN orders
        ON vendors.vend_key = orders.or_vendor}

The following example returns all orders in the orders table and each corresponding plant name from the plant table:

SELECT 
    orders.or_item,
    orders.or_number,
    plants.in_name
FROM 
    {OJ orders 
      LEFT JOIN plants
        ON orders.or_item = plants.in_itemid}

SQL92 right outer joins

xfODBC supports SQL92 right outer joins through RIGHT OUTER JOIN clauses:

RIGHT [OUTER] JOIN table_name ON column1 = column2

where column1 is a column in table_name and column2 is a column in another FROM clause table.

RIGHT [OUTER] JOIN returns all the records in the second table (the table that follows “RIGHT OUTER JOIN”) and matching records from the first table (the table that precedes “RIGHT OUTER JOIN”). For example, the following query returns the specified fields for all records in the vendors table and any records in the orders table that match the ON criteria.

SELECT 
    orders.or_item, 
    orders.or_number, 
    vendors.vend_name
FROM 
    {OJ orders
      RIGHT JOIN vendors
        ON vendors.vend_key =  orders.or_vendor}

SQL92 full joins

xfODBC supports SQL92 full joins through FULL OUTER JOIN clauses:

FULL [OUTER] JOIN table_name ON column1 = column2

where column1 is a column in table_name and column2 is a column in another FROM clause table.

FULL [OUTER] JOIN returns all records from both the left and right table, whether or not there are matching values. For example, the following query returns the specified fields for all records in the vendors and orders tables:

SELECT 
    o.or_item, o.or_number, v.vend_name
FROM 
    orders o 
      FULL JOIN vendors v
        ON o.or_vendor = v.vend_key

ON clauses in SQL92 outer joins

Each ON clause for a SQL92 outer join should be placed immediately after the table qualifier it modifies. If this is not the case, as in the following example, you will get an error.

SELECT 
    o.or_terms, 
    o.or_odate, 
    o.or_qty, 
    p.in_name, 
    v.vend_name
FROM 
    { oj orders o
      LEFT JOIN vendors v 
      LEFT JOIN plants p
      ON o.or_vendor = v.vend_key 
      ON o.or_item = p.in_itemid}

The next example is correctly constructed. Each ON clause follows its qualifier.

SELECT 
    o.or_terms, 
    o.or_odate, 
    o.or_qty, 
    p.in_name, 
    v.vend_name
FROM 
    { oj orders o
      LEFT JOIN vendors v 
        ON o.or_vendor = v.vend_key 
      LEFT JOIN plants p
        ON o.or_item = p.in_itemid}

FROM clauses in SQL92 outer joins

FROM clauses in SQL92 outer joins can include SELECT statements, which can greatly optimize performance. If you include a SELECT statement in a FROM clause, you must enclose the entire SELECT statement in parentheses ( ), and you must include a table alias for the derived table. For example:

SELECT
    o.or_number, 
    p.in_itemid, 
    p.in_name 
FROM 
    (SELECT * FROM orders WHERE orders.or_item < 7) o
      INNER JOIN plants p ON o.or_item = p.in_itemid