Skip to main content

FROM (SQL)

A SELECT clause that specifies one or more tables to query.

Synopsis

SELECT ... FROM [optimize-option] table-ref
    [ [AS] t-alias ]
    [, [LATERAL] table-ref [ [AS] t-alias] ] [,...]

Arguments

Argument Description
optimize-hint Optional — A single keyword, or a series of keywords separated by spaces, that specify query optimization options. See Specify Optimization Hints in Queries for more information.
table-ref One or more tables, views, table-valued functions, or subqueries from which data is being retrieved, specified as a comma-separated list or with JOIN syntax. Some restrictions apply on using views with JOIN syntax. You can specify a subquery, enclosed in parentheses.
AS t-alias Optional — An alias for the table name. Must be a valid identifier. Can be specified with or without the optional AS keyword.
LATERAL Optional — Enables lateral references to earlier tables in the FROM clause. See LATERAL Keyword for more information.

Description

The FROM clause specifies one or more tables (or views, or subqueries) from which data is queried within a SELECT statement. If no table data is being queried, the FROM clause is optional, as described below.

Multiple tables are specified as a comma-separated list, or a list separated by other JOIN syntax. Each table name can optionally be supplied an alias.

Table name aliases are used when specifying field names for multiple tables in the SELECT statement. If two (or more) tables are specified in the FROM clause, you indicate which table’s field you want by specifying tablename.fieldname for each field in the SELECT select-item clause. Because table names are often long names, a short table name alias is useful in this context (t-alias.fieldname).

The following example show the use of table name aliases:

SELECT e.Name,c.Name
FROM Sample.Company AS c,Sample.Employee AS e

The AS keyword can be omitted. It is provided for compatibility and clarity.

Supplying a Schema Name to a Table Reference

A table-ref name is either qualified (schema.tablename) or unqualified (tablename). The schema name for an unqualified table name (or view name) is supplied using a schema search path or the system-wide default schema name:

  1. If a schema search path is provided, InterSystems IRIS searches the specifiedschemas for a matching table name.

  2. If a schema search path is not provided, or the schema search path does not produce a match, InterSystems IRIS uses the system-wide default schema name.

Table Joins

When you specify multiple table names in a FROM clause, InterSystems SQL performs join operations on those tables. The type of join performed is specified by a join keyword phrase or symbol between each pair of table names. When two table names are separated by a comma, a cross join is performed. For further details on the different types of joins and their syntax, refer to JOIN.

The sequence in which joins are performed is automatically determined by the SQL query optimizer and is not based on the sequence that the tables are listed in the query. If desired, you can control the sequence in which joins are performed by specifying a query optimization option.

The first two SELECT statements show the row counts for two individual tables, and the third example shows the row count for a SELECT specifying both tables. This latter results in a much larger table, a Cartesian product, where every row in the first table is matched with every row of the second table, an operation known as a Cross Join.

SELECT COUNT(*)
FROM Sample.Company
SELECT COUNT(*)
FROM Sample.Vendor
SELECT COUNT(*)
FROM Sample.Company,Sample.Vendor

You can perform the same operation using explicit CROSS JOIN syntax:

SELECT COUNT(*)
FROM Sample.Company CROSS JOIN Sample.Vendor

In most cases, the extensive data duplication of a cross join is not desirable, and some other type of join is preferable.

If you specify a WHERE clause in the SELECT statement, the cross join is performed, then the WHERE clause predicate(s) determine the result set. This is equivalent to performing an INNER JOIN with an ON clause. Thus the following two examples return identical results:

SELECT p.Name,p.Home_State,em.Name,em.Office_State
FROM Sample.Person AS p, Sample.Employee AS em
WHERE p.Name %STARTSWITH 'E' AND em.Name %STARTSWITH 'E'
SELECT p.Name,p.Home_State,em.Name,em.Office_State
FROM Sample.Person AS p INNER JOIN Sample.Employee AS em
ON p.Name %STARTSWITH 'E' AND em.Name %STARTSWITH 'E'

You can specify explicit join syntax (rather than using commas) in the FROM table-ref list to perform other types of join operations. For further details, refer to JOIN.

Query Optimization Options

By default, the InterSystems SQL query optimizer uses sophisticated and flexible algorithms to optimize the performance of complex queries involving join operations and/or multiple indexes. In most cases, these defaults provide optimal performance. However, after consultation with the InterSystems Worldwide Response Center (WRC), you may be instructed to give “hints” to the query optimizer, specifying one or more aspects of query optimization. The optimize-hint argument in the FROM is used to specify these hints. You can specify multiple optimization hints in any order, separated by blank spaces. For further details, refer to Specify Optimization Hints in Queries.

You can use optimize-hint FROM clause keywords in a simple SELECT statement, in a CREATE VIEW view definition SELECT statement, or in a subquery SELECT statement within the FROM clause.

Table-Valued Functions in the FROM Clause

SELECT Name,DOB FROM Sample.SP_Sample_By_Name('A')

The following Dynamic SQL example specifies the same table-valued function. It uses the %Execute() method to supply parameter values to the ? input parameter:

  SET myquery="SELECT Name,DOB FROM Sample.SP_Sample_By_Name(?)"
  SET tStatement = ##class(%SQL.Statement).%New()
  SET qStatus = tStatement.%Prepare(myquery)
    IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
  SET rset = tStatement.%Execute("A")
  DO rset.%Display()
  WRITE !,"End of A data",!!
  SET rset = tStatement.%Execute("B")
  DO rset.%Display()
  WRITE !,"End of B data"

A table-valued function can only be used in the FROM clause of either a SELECT statement or a DECLARE statement. A table-valued function name can be qualified with a schema name or unqualified (without a schema name); an unqualified name uses the default schema. In a SELECT statement FROM clause, a table-valued function can be used wherever a table name can be used. It can be used in a view or a subquery, and can be joined to other table-ref items using a comma-separated list or explicit JOIN syntax.

A table-valued function cannot be directly used in an INSERT, UPDATE, or DELETE statement. You can, however, specify a subquery for these commands that specifies a table-valued function.

InterSystems SQL does not define the EXTENTSIZE for a table-valued function, or the SELECTIVITY for table-valued function columns.

Subqueries in the FROM Clause

You can specify a subquery in the FROM clause. This is known as a streamed subquery. The subquery is treated the same as a table, including its use in JOIN syntax and the optional assignment of an alias using the AS keyword. A FROM clause can contain multiple tables, views, and subqueries in any combination, subject to the restrictions of the JOIN syntax, as described in JOIN.

A subquery is enclosed in parentheses. The following example shows a subquery in a FROM clause:

SELECT name,region
FROM (SELECT t1.name,t1.state,t2.region
      FROM Employees AS t1 LEFT OUTER JOIN Regions AS t2
      ON t1.state=t2.state)
GROUP BY region

A subquery can specify a TOP clause. A subquery can contain an ORDER BY clause when paired with a TOP clause.

A subquery can use SELECT * syntax, subject to the following restriction: because a FROM clause results in a value expression, a subquery containing SELECT * must yield only one column.

A join within a subquery cannot be a NATURAL join or take a USING clause.

FROM Subqueries and %VID

When a FROM subquery is invoked, it returns a %VID for each subquery row returned. A %VID is an integer counter field; its values are system-assigned, unique, non-null, non-zero, and non-modifiable. The %VID is only returned when explicitly specified. It is returned as data type INTEGER. Because %VID values are sequential integers, they are far more meaningful if the subquery returns ordered data; a subquery can only use an ORDER BY clause when it is paired with a TOP clause.

Because the %VID is a sequential integer, it can be used to determine the ranking of items in a subquery with an ORDER BY clause. In the following example, the 10 newest records are listed in Name order, but their timestamp ranking is easily seen using the %VID values:

SELECT Name,%VID,TimeStamp FROM
   (SELECT TOP 10 * FROM MyTable ORDER BY TimeStamp DESC)
ORDER BY Name 

One common use of the %VID is to “window” the result set, dividing execution into sequential subsets that fit the number of lines available in a display window. For example, display 20 records, then wait for the user to press Enter, then display the next 20 records.

The following example uses %VID to “window” the results into subsets of 10 records:

SELECT %VID,* FROM
   (SELECT TOP 60 Name, Age FROM Sample.Person WHERE Age > 55 ORDER BY Name)
WHERE %VID BETWEEN ? AND ? 

For details on using %VID, refer to Defining and Using Views.

LATERAL Keyword

The LATERAL keyword can be used to have more explicit control over the FROM processing order by allowing views and table-valued functions to reference field values from tables listed earlier in the FROM clause. These lateral references affect the rows that the subquery or table valued-function generate.

Within the subquery or the table-valued function that the LATERAL keyword is specified on, the laterally referenced fields are treated as given values. Laterally referenced fields always come from earlier FROM items in the same FROM clause.

When used to precede a FROM subquery, the LATERAL keyword indicates that the subquery may reference fields in FROM items that semantically precede it in the query. These laterally referenced fields will be processed before the FROM subquery that LATERAL was specified on.

When used to precede a table-valued function, the LATERAL keyword indicates that fields from the previous FROM items can be used within the table-valued function. In this context, the keyword is optional, and the lateral join will be applied implicitly if such references are used within the table-valued function.

Optional FROM Clause

If no table data is referenced (directly or indirectly) by the SELECT item list, the FROM clause is optional. This kind of SELECT may be used to return data from functions, operator expressions, constants, or host variables. For a query that references no table data:

  • If the FROM clause is omitted, a maximum of one row of data is returned, regardless of the TOP keyword value; TOP 0 returns no data. The DISTINCT clause is ignored. No privileges are required.

  • If the FROM clause is specified, it must specify an existing table in the current namespace. You must have SELECT privilege for that table, even though the table is not referenced. The number of identical rows of data returned is equal to the number of rows in the specified table, unless you specify a TOP or DISTINCT clause, or limit it with a WHERE or HAVING clause. Specifying a DISTINCT clause limits the output to a single row of data. The TOP keyword limits the output to the number of rows specified by the TOP value; TOP 0 returns no data.

With or without a FROM clause, subsequent clauses (WHERE, GROUP BY, HAVING or ORDER BY) may be specified. A WHERE or HAVING clause may be used to determine whether or not to return results, or how many identical rows of results to return. These clauses may reference a table, even if no FROM clause is specified. A GROUP BY or ORDER BY clause may be specified, but these clauses are not meaningful.

The following are examples of SELECT statements that reference no table data. Both examples return one row of information.

The following example omits the FROM clause. The DISTINCT keyword is not needed, but may be specified. No SELECT clauses are permitted.

SELECT 3+4 AS Arith,
      {fn NOW} AS NowDateTime,
      {fn DAYNAME({fn NOW})} AS NowDayName,
      UPPER('MixEd cASe EXPreSSioN') AS UpCase,
      {fn PI} AS PiConstant   

The following example includes a FROM clause. The DISTINCT keyword is used to return a single row of data. The FROM clause table reference must be a valid table. The ORDER BY clause is permitted here, but is meaningless. Note that the ORDER BY clause must specify a valid select item alias:

SELECT DISTINCT 3+4 AS Arith,
    {fn NOW} AS NowDateTime,
    {fn DAYNAME({fn NOW})} AS NowDayName,
    UPPER('MixEd cASe EXPreSSioN')  AS UpCase,
    {fn PI} AS PiConstant
FROM Sample.Person
ORDER BY NowDateTime  

The following examples both use a WHERE clause to determine whether or not to return results. The first includes a FROM clause and uses the DISTINCT keyword is to return a single row of data. The second omits the FROM clause, and therefore returns at most a single row of data. In both cases, the WHERE clause table reference must be a valid table for which you have SELECT privilege:

SELECT DISTINCT
   {fn NOW} AS DataOKDate
FROM Sample.Person
WHERE FOR SOME (Sample.Person)(Name %STARTSWITH 'A')  
SELECT {fn NOW} AS DataOKDate
WHERE FOR SOME (Sample.Person)(Name %STARTSWITH 'A')
FeedbackOpens in a new tab