The FROM clause is required in every SELECT statement involving data from tables, views, or stored procedures.
The FROM clause can include JOIN conditions linking two or more tables, and can include joins to other queries (derived tables). For information about these features, see Joins: Retrieving data from several tables.
In the FROM clause, the full naming syntax for tables and views is always permitted, such as:
SELECT select-list FROM owner.table-name;
Qualifying table, view, and procedure names is necessary only when the object is owned by a user ID that is different from the user ID of the current connection, or if the user ID of the owner is not the name of a group to which the user ID of the current connection belongs.
You can give a table name a correlation name to improve readability, and to save entering the full table name each place it is referenced. You assign the correlation name in the FROM clause by entering it after the table name, like this:
SELECT d.DepartmentID, d.DepartmentName FROM Departments d;
When a correlation name is used, all other references to the table, for example in a WHERE clause, must use the correlation name, rather than the table name. Correlation names must conform to the rules for valid identifiers.
See FROM clause.
A derived table is a table derived directly, or indirectly, from one or more tables by the evaluation of a query expression. Derived tables are defined in the FROM clause of a SELECT statement.
Querying a derived table works the same as querying a view. That is, the values of a derived table are determined at the time the derived table definition is evaluated. Derived tables differ from views, however, in that the definition for a derived table is not stored in the database. Derived tables differ from base and temporary tables in that they are not materialized and they cannot be referred to from outside the query in which they are defined.
The following query uses a derived table (my_drv_tbl) to hold the maximum salary in each department. The data in the derived table is then joined to the Employees table to get the surnames of the employee earning the salaries.
SELECT Surname, my_drv_tbl.max_sal AS Salary, my_drv_tbl.DepartmentID FROM Employees e, ( SELECT MAX( Salary ) AS max_sal, DepartmentID FROM Employees GROUP BY DepartmentID ) my_drv_tbl WHERE e.Salary = my_drv_tbl.max_sal AND e.DepartmentID = my_drv_tbl.DepartmentID ORDER BY Salary DESC;
The following example creates a derived table (MyDerivedTable) that ranks the items in the Products table, and then queries the derived table to return the three least expensive items:
SELECT TOP 3 * FROM ( SELECT Description, Quantity, UnitPrice, RANK() OVER ( ORDER BY UnitPrice ASC ) AS Rank FROM Products ) AS MyDerivedTable ORDER BY Rank;
See also: FROM clause.
The most common elements in a FROM clause are table names. However, it is also possible to query rows from other database objects that have a table-like structure—that is, a well-defined set of rows and columns. For example, you can query views, or query stored procedures that return result sets.
For example, the following statement queries the result set of a stored procedure called ShowCustomerProducts.
SELECT * FROM ShowCustomerProducts( 149 );
|Discuss this page in DocCommentXchange.
Send feedback about this page using email.
|Copyright © 2009, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.1|