SELECT Command
Previous  Top  Next



SELECT is used to retrieve rows selected from one or more tables.
Correlated and uncorrelated subqueries are supported.

Brief Syntax

SELECT
[ ALL | DISTINCT ]  
[ TOP row_count [, offset ] ]  
* | column_reference | select_expr [ [ AS ] column_alias ] [, ...]  
[ INTO output_table ]  
FROM from_item [, ...]  
[ WHERE condition ]  
[ GROUP BY { column_name | expression } [, ...] ]  
[ HAVING condition ]  
[ { UNION | INTERSECT | EXCEPT } [ ALL ] [ CORRESPONDING [ BY (column_list) ] ] SELECT ... ]  
[ ORDER BY { unsigned_integer | column_name } [ ASC | DESC ] [, ...] ]  

where from_item can be one of:

[ MEMORY ] ["database_file_name".] table_name [ [ AS ] table_alias ] [ PASSWORD database_password ]  
select ... [ [ AS ] table_alias ]  
from_item [ NATURAL ] join_type from_item [ ON join_condition | USING ( join_column [, ...] ) ] [ [ AS ] table_alias ]  

Description

SELECT retrieves rows from one or more tables.

Using the ALL keyword allows duplicate records to appear in the results of a SELECT query. This is the default.
Using the DISTINCT keyword prevents duplicate records from appearing in the results of a SELECT query.

The TOP clause can be used to constrain the number of rows returned by the SELECT statement. TOP takes one or two integer arguments. The first argument specifies the maximum number of rows to return, and the second specifies the offset of the first row to return. The offset of the initial row is 1 (not 0):

SELECT TOP 10,20 * FROM employee /* returns rows 20-29 */  
 
The asterisk keyword (*) is used when you want to select all of the columns in the table(s) specified in the FROM clause.
Coulmn references list can be used if you want to retreive some table columns:

SELECT FirstName, LastName FROM employee  
 
You can also retrieve column expressions:

SELECT Price * Quantity AS Total FROM orders  
 
The INTO keyword is used when you want the records returned by the SELECT query to be placed in a new table.

SELECT DISTINCT FirstName INTO names FROM employee  

FROM Clause

The FROM clause is used to specify from which tables, joined tables, and subqueries the desired records are to be retrieved.
If multiple sources for SELECT are specified, the result is the Cartesian product (cross join) of all the sources. But usually qualification conditions are added to restrict the returned rows to a small subset of the Cartesian product.

The FROM clause can contain the following elements:

MEMORY
If MEMORY keyword is specified before the table_name then an in-memory table is referenced, not a disk one  

database_file_name
The database file name which must be specified only if SELECT operates with the tables from multiple databases.  

table_name
The name of an existing table.  

table_alias
A substitute name for the FROM item containing the alias. An alias is used for brevity or to eliminate ambiguity for self-joins (where the same table is scanned multiple times). When an alias is provided, it completely hides the actual name of the table or function; for example given FROM foo AS f, the remainder of the SELECT must refer to this FROM item as f not foo. If an alias is written, a column alias list can also be written to provide substitute names for one or more columns of the table.  

PASSWORD database_password
The password to open encrypted external database specified by database_file_name.  

select
A sub-SELECT can appear in the FROM clause. This acts as though its output were created as a temporary table for the duration of this single SELECT command. Note that the sub-SELECT must be surrounded by parentheses.  

join_type
One of  
·[ INNER ] JOIN  
·LEFT [ OUTER ] JOIN  
·RIGHT [ OUTER ] JOIN  
·FULL [ OUTER ] JOIN  
·CROSS JOIN  
 
For the INNER and OUTER join types, a join condition must be specified, namely exactly one of NATURAL, ON join_condition, or USING (join_column [, ...]). See below for the meaning. For CROSS JOIN, none of these clauses may appear.  
 
A JOIN clause combines two FROM items. Use parentheses if necessary to determine the order of nesting. In the absence of parentheses, JOINs nest left-to-right. In any case JOIN binds more tightly than the commas separating FROM items.  
 
CROSS JOIN and INNER JOIN produce a simple Cartesian product, the same result as you get from listing the two items at the top level of FROM, but restricted by the join condition (if any). CROSS JOIN is equivalent to INNER JOIN ON (TRUE), that is, no rows are removed by qualification. These join types are just a notational convenience, since they do nothing you couldn't do with plain FROM and WHERE.  
 
LEFT OUTER JOIN returns all rows in the qualified Cartesian product (i.e., all combined rows that pass its join condition), plus one copy of each row in the left-hand table for which there was no right-hand row that passed the join condition. This left-hand row is extended to the full width of the joined table by inserting null values for the right-hand columns. Note that only the JOIN clause's own condition is considered while deciding which rows have matches. Outer conditions are applied afterwards.  
 
Conversely, RIGHT OUTER JOIN returns all the joined rows, plus one row for each unmatched right-hand row (extended with nulls on the left). This is just a notational convenience, since you could convert it to a LEFT OUTER JOIN by switching the left and right inputs.  
 
FULL OUTER JOIN returns all the joined rows, plus one row for each unmatched left-hand row (extended with nulls on the right), plus one row for each unmatched right-hand row (extended with nulls on the left).  
 
ON join_condition
join_condition is an expression resulting in a value of type boolean (similar to a WHERE clause) that specifies which rows in a join are considered to match.  
 
USING (join_column [, ...])
A clause of the form USING ( a, b, ... ) is shorthand for ON left_table.a = right_table.a AND left_table.b = right_table.b .... Also, USING implies that only one of each pair of equivalent columns will be included in the join output, not both.  
 
NATURAL
NATURAL is shorthand for a USING list that mentions all columns in the two tables that have the same names.  

Example:

SELECT Event_Name, Venue FROM Events e INNER JOIN Venues v ON (e.VenueNo=v.VenueNo) ORDER BY Event_Name  

WHERE Clause

The WHERE clause is used to specify the conditions any records returned by a query must satisfy.

condition specifies the search conditions that will be used by the WHERE clause. It is composed of one or more predicate expressions connected by the AND, NOT, and OR logical operators.

Example:

SELECT * FROM events WHERE Event_Name LIKE '%men%'  

GROUP BY Clause

The GROUP BY clause segregates the output records into groups. It is commonly used with the aggregate functions which calculate a value, such as a sum, for each group.
When GROUP BY is present, it is not valid for the SELECT list expressions to refer to ungrouped columns except within aggregate functions, since there would be more than one possible value to return for an ungrouped column.

Example:

SELECT FirstName, COUNT(FirstName) FROM Employee GROUP BY FirstName  

HAVING Clause

The HAVING clause is primarily used to impose search conditions onto the records returned by a GROUP BY clause.
When not used in conjunction with a GROUP BY clause, HAVING behaves like a WHERE clause.

Example:

SELECT VenueNo, Avg(Ticket_Price) FROM events GROUP BY VenueNo HAVING Event_Date < NOW  

UNION Clause

The UNION clause allows you to combine two or more queries into one result set that contains all of the unique records in all of the queries belonging to the union.
The ALL keyword dictates that no duplicate records are removed, otherwise all duplicate records are removed.
In comparison, a JOIN combines fields while a UNION combines records.

Example:

SELECT Event_Name FROM events WHERE VenueNo < 3  
UNION  
SELECT Event_Name FROM events WHERE VenueNo > 8  

INTERSECT Clause

The INTERSECT operator computes the set intersection of the rows returned by the involved SELECT statements. A row is in the intersection of two result sets if it appears in both result sets.
The result of INTERSECT does not contain any duplicate rows unless the ALL option is specified. With ALL, a row that has m duplicates in the left table and n duplicates in the right table will appear min(m,n) times in the result set.
Multiple INTERSECT operators in the same SELECT statement are evaluated left to right, unless parentheses dictate otherwise. INTERSECT binds more tightly than UNION. That is, A UNION B INTERSECT C will be read as A UNION (B INTERSECT C).

Example:

SELECT VenueNo FROM Venues  
INTERSECT CORRESPONDING BY (VenueNo)  
SELECT VenueNo FROM Events  

EXCEPT Clause

The EXCEPT operator computes the set of rows that are in the result of the left SELECT statement but not in the result of the right one.
The result of EXCEPT does not contain any duplicate rows unless the ALL option is specified. With ALL, a row that has m duplicates in the left table and n duplicates in the right table will appear max(m-n,0) times in the result set.
Multiple EXCEPT operators in the same SELECT statement are evaluated left to right, unless parentheses dictate otherwise. EXCEPT binds at the same level as UNION.

Example:

SELECT VenueNo FROM Venues  
EXCEPT CORRESPONDING BY (VenueNo)  
SELECT VenueNo FROM Events  

ORDER BY Clause

The ORDER BY clause is used to sort the records returned by the SELECT query. The sort is based upon one or more selected fields contained in the records. Optionally one may add the key word ASC (ascending) or DESC (descending) after any expression in the ORDER BY clause. If not specified, ASC is assumed by default.
Subqueries cannot include an ORDER BY.

Example:

SELECT Event_Name, Venue, Ticket_Price FROM Events e, Venues v WHERE e.VenueNo = v.VenueNo ORDER BY Ticket_Price ASC, Venue DESC