Statements that access data
xfODBC supports the following, which enable you to access data:
- SELECT statements
- Subqueries (see Creating subqueries and inline views)
- Joins
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
- the FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY, and FOR UPDATE OF clauses. See Notes on clauses, columns, and aliases for information.
- the UNION operator. See Notes on clauses, columns, and aliases for information.
- aggregate functions, which are functions that return a single value from a group of values (for example, SUM). See Aggregate functions for information.
- scalar functions, which are functions that return a value for each returned row. See Scalar functions for information.
- bitwise functions. See Bitwise functions for information.
- expressions, which can be used in the SELECT statement’s column list or in a WHERE clause.
- joins, including inner, outer, and full joins. See Joins for information.
- subqueries (including inline views). You can use SELECT statements within SELECT statements. See Creating subqueries and inline views for information.
You can also use
- the asterisk (*) wildcard in place of a column list. To select all the columns in the tables specified in table_list, use an asterisk (*). To select all the columns from one table in table_list, use the following:
table_name.*
- double quotation marks around identifiers. These instruct the driver to be case-sensitive when evaluating an identifier.
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:
- To use a SELECT statement, you must have access privileges to all tables accessed by the statement.
- The SELECT keyword can be preceded by spaces, tab, carriage return, and line feed, but not by any other character unless the SELECT statement is a subquery. See Creating subqueries and inline views.
- SELECT statement clauses are evaluated in the following order: FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY.
- Any select list item consisting of an expression, aggregate function, or scalar function that is not a single column in the database is assigned a column name as ‘EXPRn’, where n is the ordinal position of the select list item. You can use an alias to override this assigned column name.
- You must qualify a column when multiple tables are selected and the same column name is used in more than one table. For example:
SELECT table1.col_1, table2.col_1 FROM table1, table2
- If a column name is a reserved word, you must enclose the name in double quotes. For example:
SELECT "date", "order" FROM orders
See ODBC reserved words for a list of reserved words.
- If the SELECT list (column_list) includes a mix of aggregate functions and value functions or column names, the value functions and column names must be specified in a GROUP BY clause.
- We recommend always using an ORDER BY clause. Without it, the order of result sets may change when you update to a new version of Connectivity Series or even apply a Connectivity Series patch.
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 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
|
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:
- A scalar subquery returns no more than one column from one row and can be used anywhere that a scalar value can be used in a WHERE, WHEN (for CASE), or FROM clause. For example, the subquery in the following returns order numbers for orders where the order price is greater than the average price for plants in the sample database:
SELECT or_number FROM orders WHERE or_price > (SELECT AVG(in_price) FROM plants)
- A table subquery can return multiple rows and columns and can be used anywhere a table or view can be used in a FROM clause. A table subquery in a FROM clause is known as an inline view. A table subquery can also be used in a WHERE or WHEN clause, in certain conditions (discussed below), and it can be used in an INSERT statement (see INSERT) and in a SET clause for UPDATE (see UPDATE). For example, the following uses an inline view to create a combined list of zip codes for customers and vendors:
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:
- xfODBC doesn’t support SELECT ROW, so row subqueries (a type of scalar subquery that returns only one row) are not supported.
- ORDER BY clauses and aggregates in the select list are supported for inline views, but not for other subqueries.
- A table subquery can be used in a WHERE or WHEN clause only if it’s preceded by IN or [NOT] EXISTS or a comparison operator used with SOME, ALL, or ANY. For example, the following statement uses a table subquery with IN to retrieve the name of every plant currently on order:
SELECT plants.in_name FROM plants WHERE plants.in_itemid IN (SELECT or_item FROM orders)
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 WHERE customers.cust_key IN (SELECT or_customer FROM orders)
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.
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:
- Left outer joins return all the records in the first table of the join and only matching records from the second table. See SQL92 left outer joins for examples.
- Right outer joins return all records in the second table specified in the join and only matching records from the first table. See SQL92 right outer joins for examples.
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.
Be careful nesting joins. This may reduce performance. |
Note the following for joins:
- The join parser doesn’t require SQL92 joins to be enclosed in the ODBC escape sequence. For example, both of the following are acceptable:
{oj orders RIGHT JOIN vendors ON or_vendor = vend_key} orders RIGHT OUTER JOIN vendors ON or_vendor = vend_key
- If any field specified in a join is not part of an index segment, the xfODBC driver may create a temporary index that includes all the segments in the join. See How xfODBC uses keys for more information.
- All clauses, operators, etc., that are supported for WHERE clauses are supported for ON clauses. (See WHERE.) For example, you can use AND and IN in an ON clause:
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.
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
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}
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}
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}
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