Selecting Data from Multiple Tables
You can retrieve data from multiple tables in a single SELECT statement if the tables are joined together in that statement. Tables in associated main and private indexspaces (as specified in sysIQ_logins) may be joined in any statement that supports ad hoc joins (including the SELECT statement and the INSERT...SELECT statement).
Note: You must create and preallocate a private indexspace with the IQ CREATE INDEXSPACE command and associate it with the main indexspace in sysIQ_logins using the sp_IQAddLogin stored procedure.
As with a standard SQL join, a Sybase IQ join combines rows from two or more tables. Each combination is based on the join condition between columns specified in the WHERE clause. Chapter 4, "Retrieving Data from Multiple Tables," describes joins in detail.
Compliance with Transact-SQL and SQL-92
- The SELECT command used in Sybase IQ is a subset of the SQL-92 standard select command, except for the following unsupported features:
- Sybase IQ does not support the SQL-92 join operation keywords inner join, left outer join and right outer join. Sybase IQ supports Transact-SQL outer join syntax and semantics.
- Restrictions on SELECT statements in subqueries, as described in "Using Subqueries".
- Sybase IQ does not support except [all], intersect [all], and corresponding clauses in query expressions.
- SQL-92 row value constructors. Row value constructors are parenthesized lists of column values that let you manipulate rows of data at a time. For example:
WHERE (COL1, COL2, COL3) = (COL4, COL5, COL6)
In Sybase IQ, you need to use the following syntax to perform such a query:
WHERE COL1 = COL4 AND COL2 = COL5 AND
COL3 = COL6
- The Sybase IQ SELECT command supports the same syntax as the Transact-SQL select command, except for the following unsupported features:
- compute clause
- holdlock clause
- for browse option
- text/image datatypes
- Transact-SQL extensions to the group by and having clauses