SELECT

Pervasive logo

SQL Engine Reference

SELECT

Retrieves specified information from a database. A SELECT statement creates a temporary view.

Syntax
query-specification [ [ UNION [ ALL ] query-specification ]... 
[ ORDER BY order-by-expression [ , order-by-expression ]... ] 
 
order-by-expression ::= expression [ CASE | COLLATE collation-name ] [ ASC | DESC ] 
 
query-specification ::=	  ( query-specification ) 
| SELECT [ ALL | DISTINCT ] [ top-clause ] select-list 
  FROM table-reference [ , table-reference ]... 
  [ WHERE search-condition ] 
  [ GROUP BY expression [ , expression ]... 
[ HAVING search-condition ] ] 


 
top-clause ::= TOP number 
 
select-list ::= * | select-item [ , select-item ]... 
 
select-item ::= expression [ [ AS ] alias-name ] | table-name . *  
 
table-reference ::= { OJ outer-join-definition } 
| [db-name.]table-name [ [ AS ] alias-name ] 
| join-definition 
| ( join-definition ) 

 
join-definition ::= table-reference INNER JOIN table-reference ON search-condition 
| table-reference CROSS JOIN table-reference 
| outer-join-definition 

 
outer-join-definition ::= table-reference outer-join-type JOIN table-reference 
ON search-condition 
 
outer-join-type ::= LEFT [ OUTER ] | RIGHT [ OUTER ] | FULL [ OUTER ] 
 
search-condition ::= search-condition AND search-condition 
| search-condition OR search-condition 
| NOT search-condition 
| ( search-condition ) 
| predicate 

 
predicate ::= expression [ NOT ] BETWEEN expression AND expression 
| expression-or-subquery comparison-operator expression-or-subquery 
| expression [ NOT ] IN ( query-specification ) 
| expression [ NOT ] IN ( value [ , value ]... ) 
| expression [ NOT ] LIKE value 
| expression IS [ NOT ] NULL 
| expression comparison-operator ANY ( query-specification ) 
| expression comparison-operator ALL ( query-specification ) 
| [ NOT ] EXISTS ( query-specification ) 

 
comparison-operator ::= < | > | <= | >= | = | <> 
 
expression-or-subquery ::= expression | ( query-specification ) 
 
value ::= literal | USER | ? 
 
expression ::= expression - expression 
| expression + expression 
| expression * expression 
| expression / expression 
| ( expression ) 
| -expression 
| +expression 
| column-name 
| ? 
| literal 
| set-function 
| scalar-function 
| { fn scalar-function } 
| USER 
| IF ( search-condition , expression , expression ) 
| SQLSTATE 
| : user-defined-name 
|@:IDENTITY 
|@:ROWCOUNT 
|@@IDENTITY 
|@@ROWCOUNT 

 
set-function ::= COUNT (*) 
| COUNT ( [ DISTINCT | ALL ] expression ) 
| SUM ( [ DISTINCT | ALL ] expression ) 
| AVG ( [ DISTINCT | ALL ] expression ) 
| MIN ( [ DISTINCT | ALL ] expression ) 
| MAX ( [ DISTINCT | ALL ] expression ) 

 
scalar-function ::= see Scalar Functions  
Remarks
In addition to supporting a GROUP BY on a column-list, as specified in the Microsoft ODBC Programmer's Reference, SRDE has extended the syntax to support a GROUP BY on an expression-list or on any expression in a GROUP BY expression-list. See GROUP BY for more information on GROUP BY extensions. HAVING is not supported without GROUP BY.

Result sets and stored views generated by executing SELECT statements with any of the following characteristics are read-only (they cannot be updated). That is, a positioned UPDATE, a positioned DELETE and an SQLSetPos call to add, alter or delete data is not allowed on the result set or stored view if:

SQL_CONCUR_READ_ONLY was specified as the SQL_CONCURRENCY type via SQLSetStmtOption
The selection-list contains an aggregate:
SELECT SUM(c1) FROM t1
The selection-list specifies DISTINCT:
SELECT DISTINCT c1 FROM t1
The view contains a GROUP BY clause:
SELECT SUM(c1), c2 FROM t1 GROUP BY c2
The view is a join (references multiple tables):
SELECT * FROM t1, t2
The view uses the UNION operator and UNION ALL is not specified or all SELECT statements do not reference the same table:
SELECT c1 FROM t1 UNION SELECT c1 FROM t1 SELECT c1 FROM t1 UNION ALL SELECT c1 FROM t2

Note that stored views do not allow the UNION operator.

The view contains a subquery that references a table other than the table in the outer query:
SELECT c1 FROM t1 WHERE c1 IN (SELECT c1 FROM t2)

Dynamic parameters (?) are not supported as SELECT items. You may use dynamic parameters in any SELECT statement if the dynamic parameter is part of the predicate. For example, SELECT * FROM faculty WHERE id = ? is valid because the dynamic parameter is part of the predicate.

Note that the SQLExecDirect statement does not support the use of dynamic parameters. For this reason, you cannot use SQL Data Manager to execute an SQL statement with a dynamic parameter in the predicate. (SQL Data Manager uses SQLExecDirect.)

You may use variables as SELECT items only within stored procedures. See CREATE PROCEDURE .

SUM and DECIMAL Precision
When using the SUM aggregate function to sum a field that is of type DECIMAL, the following rules apply:

The precision of the result is 74, while the scale is dependent on the column definition.

The result may cause an overflow error if a number with precision greater than 74 is calculated (a very large number indeed). If an overflow occurs, no value is returned, and SQLSTATE is set to 22003, indicating a numeric value is out of range.

Examples
This simple SELECT statement retrieves all the data from the Faculty table.
SELECT * FROM Faculty 
This statement retrieves the data from the person and the faculty table where the id column in the person table is the same as the id column in the faculty table.
SELECT Person.id, Faculty.salary FROM Person, Faculty WHERE Person.id = Faculty.id  
The following example retrieves student_id and sum of the amount_paid where it is greater than or equal to 100 from the billing table. It then groups the records by student_id.
SELECT Student_ID, SUM(Amount_Paid) 
FROM Billing 
GROUP BY Student_ID  
HAVING SUM(Amount_Paid) >=100.00 
If the expression is a positive integer literal, then that literal is interpreted as the number of the column in the result set and ordering is done on that column. No ordering is allowed on set functions or an expression that contains a set function.

Subqueries
The following types of subqueries are supported: comparison, quantified, in, exists, and correlated. ORDER BY clauses are not allowed in a subquery clause.

Correlated subquery predicates in the HAVING clause which contain references to grouped columns are not supported.

You may use a subquery on the left-hand side of an expression:
Expr-or-SubQuery CompareOp Expr-or-SubQuery 
where Expr is an expression, and CompareOp is one of:

<

>

<=

>=

=

<>

LIKE

IN

NOT IN

Examples
Left-hand subquery behavior has been optimized for IN, NOT IN, and =ANY in cases where the subquery is not correlated and any join condition is an outer join. Other conditions may not be optimized. Here is an example of a query that meets these conditions:
SELECT count(*) FROM person WHERE id IN 
        (SELECT faculty_id FROM class) 
A correlated subquery is a subquery that depends in part on a table or tables in the main query.

approximate-numeric-literal
Examples
SELECT * FROM results WHERE quotient =-4.5E-2 
INSERT INTO results (quotient) VALUES (+5E7) 
between-predicate
Remarks
The syntax expression1 BETWEEN expression2 and expression3 returns TRUE if expression1 >= expression2 and expression1<= expression3. FALSE is returned if expression1 >= expression3, or is expression1 <= expression2.

Expression2 and expression3 may be dynamic parameters (for example, SELECT * FROM emp WHERE emp_id BETWEEN ? AND ?)

Examples
The next example retrieves the first names from the person table whose ID fall between 10000 and 20000.
SELECT First_name FROM Person WHERE ID BETWEEN 10000 AND 20000 
correlation-name
Remarks
Both table and column correlation names are supported.

Examples
The following example selects data from both the person table and the faculty table using the aliases T1 and T2 to differentiate between the two tables.
SELECT * FROM Person T1, Faculty T2 WHERE T1.id = T2.id  
The correlation name for a table name can also be specified in using the FROM clause, as seen in the following example.
SELECT a.Name, b.Capacity FROM Class a, Room b  
WHERE a.Room_Number = b.Number 
exact-numeric-literal
Examples
SELECT car_num, price FROM cars WHERE car_num =49042 AND price=49999.99 
in-predicate
Examples
This selects the records from table Person table where the first names are Bill and Roosevelt.
SELECT * FROM  Person WHERE  First_name IN ('Roosevelt', 'Bill') 
set-function
Examples
The following example selects the minimum salary from the Faculty table.
SELECT MIN(salary) FROM Faculty 
MIN(expression), MAX(expression), SUM(expression), AVG(expression), COUNT(*), and COUNT(expression) are supported.

COUNT(expression) counts all non-NULL values for an expression across a predicate. COUNT(*) counts all values, including NULL values.

The following example counts all the rows in q where a+b does not equal NULL.
SELECT COUNT(a+b) FROM q 
date-literal
Remarks
Date constants may be expressed in SQL statements as a character string or embedded in a vendor string. SQL_CHAR and the vendor string representation are treated as a value of type SQL_DATE. This becomes important when conversions are attempted.

The SRDE partially supports extended SQL grammar, as outlined in this function.

Examples
The next two statements return all the classes whose start date is after 1995-06-05.
SELECT * FROM Class WHERE Start_Date > '1995-06-05' 
 
SELECT * FROM Class WHERE Start_Date > {d '1995-06-05'} 
The SRDE supports the following date literal format: 'YYYY-MM-DD'.

Dates may be in the range of year 0 to 9999.

time-literal
Examples
The following two statements retrieve records from the class table where the start time for the classes is 14:00:00.
SELECT * FROM Class WHERE Start_time = '14:00:00' 
 
SELECT * FROM Class WHERE Start_time = {t '14:00:00'} 
The SRDE supports the following time literal format: 'HH:MM:SS'.

Time constants may be expressed in SQL statements as a character string or embedded in a vendor string. Character string representation is treated as a string of type SQL_CHAR and the vendor string representation as a value of type SQL_TIME.

The SRDE partially supports extended SQL grammar, as outlined in this function.

timestamp-literal
Remarks
Timestamp constants may be expressed in SQL statements as a character string or embedded in a vendor string. The SRDE treats the character string representation as a string of type SQL_CHAR and the vendor string representation as a value of type SQL_TIMESTAMP. The SRDE partially supports extended SQL grammar, as outlined in this function.

Examples
The next two statements retrieve records from the Billing table where the start day and time for the log is 1996-03-28 at 17:40:49.
SELECT * FROM Billing WHERE log = '1996-03-28 17:40:49.0000000' 
SELECT * FROM Billing WHERE log = {ts '1996-03-28 17:40:49.0000000'} 
The SRDE supports the following time literal format: 'YYYY-MM-DD HH:MM:SS.SSSSSSS'. Note that the SRDE ignores the fractional seconds when processing Timestamp values, but the MKDE does not.

string-literal
Remarks
Literal strings are represented as single-quoted strings. If the string itself contains a single-quote or apostrophe, the character must be preceded by another single-quote.

Examples
SELECT * FROM t1 WHERE c1 = 'Roberta''s Restaurant' 
SELECT STREET FROM address WHERE city LIKE 'San%' 
date arithmetic
Examples
SELECT * FROM person P, Class C WHERE p.Date_Of_Birth < ' 1973-09-05' and c.Start_date >{d '1995-05-08'} + 30 
The SRDE supports adding or subtracting an integer from a date where the integer is the number of days to add or subtract, and the date is embedded in a vendor string. (This is equivalent to executing a convert on the date).

The SRDE also supports subtracting one date from another to yield a number of days.

IF
Remarks
The IF system scalar function provides conditional execution based on the truth value of a condition

Examples
This expression prints the column header as "Prime1" and amount owed as 2000 where the value of the column amount_owed is 2000 or it prints a 0 if the value of the amount_owed column is not equal to 2000.
SELECT Student_ID, Amount_Owed, 
IF (Amount_Owed = 2000, Amount_Owed, Convert(0, SQL_DECIMAL)) "Prime1" 
FROM Billing 
From table Class, the following example prints the value in the Section column if the section is equal to 001, else it prints "xxx" under column header Prime1

Under column header Prime2, it prints the value in the Section column if the value of the section column is equal to 002, or else it prints "yyy."
SELECT ID, Name, Section, 
IF (Section = '001', Section, 'xxx') "Prime1", 
IF (Section = '002', Section, 'yyy') "Prime2" 
FROM Class

You can combine header Prime1 and header Prime2 by using nested IF functions. Under column header Prime, the following query prints the value of the Section column if the value of the Section column is equal to 001 or 002. Otherwise, it print "xxx."
SELECT ID, Name, Section, 
IF (Section = '001', Section, IF(Section = '002', Section, 'xxx')) Prime 
FROM Class

Multi-database Join
When needed, a database name may be prepended to an aliased table name in the FROM clause, to distinguish among tables from two or more different databases that are used in a join.

All of the specified databases must be serviced by the same database engine. They do not need to reside on the same physical volume. The current database may be secure or non-secure, but all other databases in the join must be non-secure. With regard to Referential Integrity, all RI keys must exist within the same database.

Literal database names are not permitted in the select-list or in the WHERE clause. If you wish to refer to specific columns in the select-list or in the WHERE clause, you must use an alias for each specified table. See examples.

Examples
Assume two separate databases, "accounting" and "customers," exist on the same server. You can join tables from the two databases using table aliases and SQL syntax similar to the following example:
SELECT ord.account, inf.account, ord.balance, inf.address  
FROM accounting.orders ord, customers.info inf  
WHERE ord.account = inf.account 
In this example, the two separate databases are "acctdb" and "loandb." The table aliases are "a" and "b," respectively.
SELECT a.loan_number_a, b.account_no, a.current_bal, b.balance  
FROM acctdb.ds500_acct_master b LEFT OUTER JOIN loandb.ml502_loan_master a ON (a.loan_number_a = b.loan_number)  
WHERE a.current_bal <> (b.balance * -1)  
ORDER BY a.loan_number_a 
left outer join

Remarks
The following example shows how to access the "Person" and "Student" tables from the DEMODATA database to obtain the Last Name, First Initial of the First Name and GPA of students. With the LEFT OUTER JOIN, all rows in the "Person" table are fetched (the table to the left of LEFT OUTER JOIN). Since not all people have GPA's, some of the columns have NULL values for the results. This is how outer join works, returning non-matching rows from either table.

Examples
SELECT Last_Name,Left(First_Name,1) AS First_Initial,Cumulative_GPA AS GPA FROM "Person" 
LEFT OUTER JOIN "Student" ON Person.ID=Student.ID 
ORDER BY Cumulative_GPA DESC, Last_Name 
Assume that you want to know everyone with perfectly rounded GPA's and have them all ordered by the length of their last name. Using the MOD statement and the LENGTH scalar function, you can achieve this by adding the following to the query:
WHERE MOD(Cumulative_GPA,1)=0 ORDER BY LENGTH(Last_Name) 
right outer join

Remarks
The difference between LEFT and RIGHT OUTER JOIN is that all non matching rows show up for the table defined to the right of RIGHT OUTER JOIN. Change the query for LEFT OUTER JOIN to include a RIGHT OUTER JOIN instead. The difference is that the all non-matching rows from the right table, in this case "Student," show up even if no GPA is present. However, since all rows in the "Student" table have GPA's, all rows are fetched.

Examples
SELECT Last_Name,Left(First_Name,1) AS First_Initial,Cumulative_GPA AS GPA FROM "Person" 
RIGHT OUTER JOIN "Student" ON Person.ID=Student.ID 
ORDER BY Cumulative_GPA DESC, Last_Name 
Cartesian join

Remarks
A Cartesian join is the matrix of all possible combinations of the rows from each of the tables. The number of rows in the Cartesian product equals the number of rows in the first table times the number of rows in the second table.

Examples
Assume you have the following tables in your database:

Table 3-13 Addr Table

EmpID

Street

E1

101 Mem Lane

E2

14 Young St.

Table 3-14 Loc Table

LocID

Name

L1

PlanetX

L2

PlanetY

The following performs a Cartesian JOIN on these tables:
SELECT * FROM Addr,Loc 
This results in the following:

Table 3-15 SELECT Statement with Cartesian JOIN

EmpID

Street

LocID

Name

E1

101 Mem Lane

L1

PlanetX

E1

101 Mem Lane

L2

PlanetY

E2

14 Young St

L1

PlanetX

E2

14 Young St

L2

PlanetY

DISTINCT

Remarks
You can use DISTINCT with SUM, AVG, COUNT, MIN, and MAX (but it does not change results with MIN and MAX). DISTINCT eliminates duplicate values before calculating the sum, average or count.

Examples
Suppose you want to know the salaries for different departments including the minimum, maximum and salary, and you want to remove duplicate salaries. The following statement would do this, excluding the computer science department:
SELECT dept_name, MIN(salary), MAX(salary), AVG(DISTINCT salary) FROM faculty WHERE dept_name<>'computer science' GROUP BY dept_name 
If you wanted to include duplicate salaries, you would use:
SELECT dept_name, MIN(salary), MAX(salary), AVG(salary) FROM faculty WHERE dept_name<>'computer science' GROUP BY dept_name 
TOP
You may limit the number of rows returned by a single SELECT statement by using the keyword TOP in the statement and specifying a value for the maximum number of rows to return.

Remarks
The specified number of rows must be a literal positive number. It is defined as a 32-bit unsigned integer.

If a SELECT statement includes both TOP and an ORDER BY clause, the SRDE generates a temporary table and populates it with the query's entire result set. The rows in the temporary table are ordered as specified by the ORDER BY clause and the TOP number of rows in the ordered result set are returned.

Views that contain a TOP clause may not be joined with other tables or views.

The main difference between TOP and SET ROWCOUNT is that TOP affects only the current statement, while SET ROWCOUNT affects all subsequent statements issued during the current database session.

If both SET ROWCOUNT and TOP are applied to a given query, the query returns a number of rows equal to the lesser of the two values.

Cursor Types and TOP

A SELECT query with a TOP clause used in a cursor implicitly changes the cursor type under several circumstances. When using the table below, remember that any SELECT with ORDER BY on an unindexed column requires a temporary table. If the ORDER BY is on an indexed column, then a temporary table is not required.

Table 3-16 Effect of TOP Clause on Cursor Types

Original Cursor Type

Converted to This Type if SELECT query requires a temporary table:

Converted to This Type if SELECT query does not require a temporary table:

Dynamic

Forward-only

Static

Static

Forward-only

No change

Forward-only

No change

No change

Examples
SELECT TOP 10 * FROM person  
		-- returns 10 rows 
SET ROWCOUNT = 5; 
SELECT TOP 10 * FROM person; 
		-- returns 5 rows 
SET ROWCOUNT = 12; 
SELECT TOP 10 * FROM person ORDER BY id; 
		-- returns the first 10 rows of the full list ordered by column id. 
The following examples show a variety of behaviors when TOP is used in views, unions, or subqueries.
CREATE VIEW v1 (c1) AS SELECT TOP 10 id FROM person; 
CREATE VIEW v2 (d1) AS SELECT TOP 5 c1 FROM v1; 
 
SELECT * FROM v2  -- returns 5 rows 
SELECT TOP 10 * FROM v2  -- returns 5 rows 
SELECT TOP 0 * FROM v2  -- returns 0 rows 
SELECT TOP 2 * FROM v2  -- returns 2 rows 
 
SELECT TOP 10 id FROM person UNION SELECT TOP 13 faculty_id FROM class  
 		-- returns 14 rows 
 
SELECT TOP 10 id FROM person UNION ALL SELECT TOP 13 faculty_id FROM class  
 		-- returns 23 rows 
 
SELECT id FROM person WHERE id IN (SELECT TOP 10 faculty_id from class)  
		-- returns 4 rows 
 
SELECT id FROM person WHERE id >= any (SELECT TOP 10 faculty_id from class)  
		-- returns 1493 rows 
Incorrect Examples

The following SELECT statements generate error messages, because the view defined contains a TOP clause, and thus cannot be involved in a join.
CREATE VIEW v1 (c1) AS SELECT TOP 10 id FROM person  
 
SELECT * FROM v1 INNER JOIN person ON v1.c1 = person.id  
		-- returns an error message 
SELECT * FROM person V v1 ON person.c1 = v1.id  
		-- returns an error message 
SELECT * FROM person, v1  
		-- returns an error message 
Example
SELECT USER FROM course  
		-- returns 145 instances of Master (the table contains 145 rows) 
SELECT DISTINCT USER FROM course  
		-- returns 1 instance of Master  
See Also
Global Variables

JOIN

SET ROWCOUNT

<	>	<=	>=	=	<>
LIKE	IN	NOT IN

*Table 3-13 Addr Table*
EmpID	Street
E1	101 Mem Lane
E2	14 Young St.

*Table 3-14 Loc Table*
LocID	Name
L1	PlanetX
L2	PlanetY

*Table 3-15 SELECT Statement with Cartesian JOIN*
EmpID	Street	LocID	Name
E1	101 Mem Lane	L1	PlanetX
E1	101 Mem Lane	L2	PlanetY
E2	14 Young St	L1	PlanetX
E2	14 Young St	L2	PlanetY

*Table 3-16 Effect of TOP Clause on Cursor Types*
Original Cursor Type	Converted to This Type if SELECT query requires a temporary table:	Converted to This Type if SELECT query does not require a temporary table:
Dynamic	Forward-only	Static
Static	Forward-only	No change
Forward-only	No change	No change

Prev
SELECT (with into)

Contents
Up
Check for Revisions

Next
SET DECIMALSEPARATORCOMMA