SELECT

Retrieves data from a database.

SYNTAX

DESCRIPTION

The SELECT command allows you to find, retrieve, and display data. To execute the SELECT command on a table, you must be the table owner, have DBA or SYSADM security privileges, or have the SELECT privilege for that table.

The result of SELECT command is a set of rows known as the result set, which meets the conditions specified in the SELECT command. With the SELECT command, you can specify the tables or views in a database you want to query, the conditions data must meet to be returned in the result set, and the sequence in which the data in the result set is output. A SELECT statement can be a UNION of several single SELECT commands.

SELECT Clause

The SELECT clause contains the SELECT keyword and the list of database objects or expressions you want to include in the result set. You can use the ALL or DISTINCT keywords to indicate whether duplicate values should be returned. If you do not specify either the ALL or DISTINCT keywords, DBMaker will return all rows by default.

The value in the result list may be a column name, an expression, a constant, or an asterisk (*). An asterisk represents all columns from the source table. You can optionally prefix a source name in front of the column name or asterisk.

You can use any of the four basic types of expressions (column, constant, function, and aggregate functions) in the select item list. If you include a constant in the select list, the same value is returned for every row. An aggregate function returns one value for a set of rows. Aggregate functions are usually used in the GROUP BY clause.

You can use the OID associated with each row of a table as a column name by using the name "OID" in the column list. The OID is essentially a hidden column whose value uniquely identifies each row in a database. The OID values are not necessarily sequential.

You can use a display label to assign a temporary name to a column in the result set or to values generated by an expression that do not come from a column. You use the AS keyword to assign a display label to a column in the result set.

FROM Clause

The FROM clause lists the sources (i.e. tables and views) from which you want to select data. The source may be a table name, a view name, or a synonym name. It is used to identify where the column name comes from if there are ambiguities. A source may be a single source or an outer source which has the keyword OUTER followed by one or more single sources.

You can supply an alias for a table name. You can use the alias to refer to the table in other clauses of the SELECT statement, which may help make the statement more readable. Table aliases are especially useful with self-joins.

Use the keyword OUTER to form outer joins. There can only one OUTER keyword in a SELECT statement. All sources before the OUTER keyword must be dominant sources. Similarly all the sources after the OUTER keyword must be subservient sources which are enclosed in brackets if you have more than one subservient source.

WHERE Clause

Use the WHERE clause to specify the search condition and join criteria on the data you are selecting. If a row satisfies the search conditions, it is returned as part of the result set. Please refer the subquery topic to see how to use a SELECT statement (subquery) within a WHERE clause.

The predicate used in the WHERE clause may be a simple comparison using the following:

• Relational Operators - these may be one of >, >=, <=, <, =, and <>. The relational operator condition is satisfied when the expression on either side of the relational operator fulfills the relation set up by the operator.
• BETWEEN - this comparison takes the form x BETWEEN y AND z. The BETWEEN condition is satisfied when the value or expression to the left of the BETWEEN keyword lies in the inclusive range (denoted by the AND keyword) of the two expressions on the right of the keyword.
• IN - this comparison takes the form x IN (y, z, ...). The IN condition is satisfied when the value or expression to the left of the IN keyword is included in the list of values to the right of the keyword.
• IS NULL - this takes the form x IS NULL. The IS NULL condition is satisfied when the value or expression to the left of the IS NULL keywords is a NULL value.
• IS NOT NULL - this takes the form x IS NOT NULL. The IS NOT NULL condition is satisfied when the value or expression to the left of the IS NOT NULL keywords contains a value other than a NULL value.
• LIKE - this takes the form x LIKE 'y' ESCAPE 'z'. The LIKE condition is satisfied when the string value or expression to the left of the LIKE keyword meets the criteria specified in the case-sensitive quoted string to the right of the keyword. You can use % (to match zero or more characters) and _ (to match exactly one character) as wildcards in the quoted strings. The ESCAPE clause is optional and allows you to define an escape character so you can include the % and _ symbols in the quoted string without having them interpreted as wildcards. To include a single-quote character in a quoted string, use two consecutive single-quotes.
• MATCH - this takes the form x NOT CASE MATCH 'y'. The MATCH condition is satisfied when the quoted string to the right of the MATCH keyword matches the entire string value or expression to the left of the keyword. The NOT and CASE keywords are optional. The NOT keyword inverts the search results, and the CASE keyword makes the search case-sensitive.
• CONTAIN - this takes the form x NOT CASE CONTAIN 'y'. The CONTAIN condition is satisfied when the quoted string to the right of the CONTAIN keyword matches any part of the string value or expression to the left of the keyword. The NOT and CASE keywords are optional. The NOT keyword inverts the search results, and the CASE keyword makes the search case-sensitive.

Compound Comparisons

You can combine simple conditions with the logical operators AND, OR, and NOT to form compound conditions. You can use the keyword AND to combine two search conditions which must be both true. You can use the keyword OR to combine two search conditions when one or the other (or both) must be true. Finally, you can use the keyword NOT to select rows where a search condition is false. The following examples contain compound conditions in their WHERE clauses:

SELECT * from Custerm 
       WHERE City NOT IN ('LA', 'NY') AND Age > 40;
SELECT * From Orders
       WHERE Price > 10,000 OR Ship_Date = TODAY;

Join Conditions

A join condition is a comparison (relational operators) on two columns while each column from a different table. For example (Orders.CusNum = Customer.CusNum)

You join two tables when you create a relationship with join condition in the WHERE clause between columns from the two tables. The effect of the join is to create a temporary composite table in which each pair of rows (one from each table) satisfying the join condition is linked to form a single row. You can have two table joins, multiple table joins, self-joins, and outer joins.

Two-Table Joins

A two table join combines two tables with join conditions. You do not have to select the columns on which the two tables are joined.

SELECT Emp_Name, Dept_Name FROM Employee, Department
                          WHERE Employee.Dept_id = Department.Dept_Id

Multiple-Table Joins

A multiple table join is a join of more than two tables with join conditions for pairs of table. A join condition is a comparison (relational operators) on two columns from each table. Following is a three table join which selects all the projects engaged by the employees in the Engineering department.

SELECT Dept_Name, Proj_Name FROM Department d, Project p, Employee e
                           WHERE d.Dept_id = e.Dept_Id AND
                                  p.Emp_Id = e.Emp_Id AND
                                 Dept_Name = 'Engineering'

Self-Joins

You can join a table to itself. To do so, you must list the table name twice in the FROM clause and assign it two different alias. Use the aliases to refer to each of the "two" tables in the WHERE clause. Suppose in the Employee table there is a field of Manager_Id which is an employee's manager's Id. Now you want to list all the employee's names together with their manager's name. You can join the Employee table with itself.

SELECT e.Emp_Name AS Emp, m.Emp_name AS Manager
                FROM Employee e, Employee m
               WHERE e.Manager_Id = m.Emp_Id

Outer-Joins

Please refer to the FROM clause

GROUP BY Clause

Use GROUP BY clause to produce summary data within a group. A group is a set of rows that have the same values of group by columns. A single row of aggregate results is produced for each group. The column you want to group results by is identified by its column name.

SELECT Dept_Id, AVG(Salary) FROM Employee 
                        GROUP BY Dept_Id;

Using a GROUP BY clause restricts what you can enter in the SELECT clause. A select item in a group by query must be one of the following:

• An aggregate function, which produces a single value summarizing the rows in the group.
• A grouping column which is listed in the GROUP BY clause.
• A constant.
• An expression involving a combination of the above.

In practice, a GROUP BY query will always include both a grouping column and an aggregate function. Each row that contains a null value in a column specified by a GROUP BY clause belongs to a single group (that is, all null values are grouped into one group).

HAVING Clause

Just as the WHERE clause is used to select and reject the individual rows that satisfy a query, the HAVING clause is used to select or reject a group. The following example shows the average sales amount for the departments whose total sale exceed one million dollars.

SELECT Dept_Name, AVG(Amount) FROM Sales 
                          GROUP BY Dept_Name
                            HAVING SUM(Amount) > 1000000

A subquery can appear in the having clause. Please see the SUBQUERY section for more information.

ORDER BY Clause

The result rows of a query are not arranged in any particular order. You can use the ORDER BY clause to sort query results by the values contained in one or more columns.

The ASC/DESC keywords specify the sort order of the results as ascending (smallest value first) or descending order. The default order is ascending. NULL values are treated as larger that non-null values for sorting purposes. Using the ASC keyword to specify sort order, NULL values would come after the any non-null values.

The following example sort the result according to name in default ascending order and age in descending order.

SELECT Name, Address, Age FROM Customer
                      ORDER BY Name, Age DESC

The next example uses a column number and display label in the ORDER BY clause.

SELECT Dept_Id, Salary + Bounce AS Total_Com, Emp_Name
                              FROM Employee
                          ORDER BY 1, Total_Com

UNION Operator

Use the UNION operator to combine the results of two or more queries into one single query result. There are several restrictions on the results that can be combined by a UNION operator:

• The two results must contain the same number of columns.
• The corresponding items in each result must have compatible data types. They don't have to have the same column names. The column name of the first result becomes the column name of the combined result.
• If you use an ORDER BY clause, it must follow the last SELECT clause and you must refer to the ordered column by its position in the SELECT list (column number).

The following example shows the use of the UNION clause in a SELECT statement.

SELECT C1, C2 FROM T1
       UNION
SELECT C3, C4 FROM T2
          ORDER BY 2

Duplicate rows are removed from the combined results when you use the UNION operator, and the combined results have distinct values for each row. If you are sure there are no duplicate rows in individual results, or you want to keep duplicate rows, you can use the UNION ALL keywords. UNION ALL keeps all the rows from individual result sets and is faster than the UNION operator. The following example shows the use of the UNION ALL clause in a SELECT statement.

SELECT 'MOVIE', Event FROM Entertainment WHERE Type = 'MOVIE'
       UNION ALL
SELECT 'BOOK', Name FROM MyBook

Subqueries

A subquery is a query that appears within the WHERE or HAVING clause of another SQL statement. A subquery is always enclosed in parentheses, but otherwise it has the same form of a SELECT statement.

Following is a subquery which selects those employees whose salary is greater than the average salary.

SELECT Name FROM Employee
           WHERE Salary > (SELECT AVG(Salary) FROM Employee)

Note that a subquery must produce a single column of data as its query result. Also when the query result is used in a simple relational operator comparison, the subquery must create a single row value only.

IN Subquery

The IN subquery is a membership test. It is true if the value of the expression matches one or more of the values selected by the subquery. In the IN membership test the subquery may return more than one row of one column data. The following example selects all the employees whose department is located in NY.

SELECT Name FROM Employee
           WHERE Dept_Id
              IN (SELECT Dept_Id FROM Department WHERE City = 'NY'')

EXISTS Subquery

The existence test checks whether a subquery produces any rows. The following example list all the departments for which there is at least one employee in that department has salary exceeds 500000.

SELECT Dept_Name FROM Department d
       WHERE EXISTS 
            (SELECT Dept_Id FROM EMPLOYEE e
                    WHERE e.Salary > 500000 AND d.Dept_Id = e.Dept_Id)

In a subquery, sometimes it is necessary to refer to the value of a column in the "current" row of the main query. This is called an Outer Reference. The d.Dept_id column in the above example is an outer reference. You can have multiple levels of subqueries, and the outer reference can refer to the columns of tables in any outer level subquery.

ANY/ALL/SOME Subquery

You can use the ALL keyword in a subquery. The search condition is true if the comparison is true for every value returned by the subquery. If the subquery returns no value (empty set), the condition is true. If there is a NULL in the returning set, the condition is false.

You can use the ANY keyword in a subquery. The search condition is true if the comparison is true for at least one of the value returned. If the subquery returns no value, the condition is false.

The following example selects non-manager employees whose salary is greater than at least one manager.

SELECT Emp_Name FROM Employee
       WHERE Manager = 'N' AND Salary > ANY
            (SELECT Salary FROM EMPLOYEE WHERE Manager = 'Y')

FOR BROWSE Clause

The FOR BROWSE keywords designate that the browse mode is used in the selection. In browse mode, no locks are acquired so the select will not be blocked by other users. Since no locks are acquired the read is not guaranteed to be repeatable. Browse mode is useful for browsing data or producing reports.

RELATED COMMANDS

• DELETE
• INSERT
• UPDATE

< SAVEPOINT | Contents | START DB >