Skip to main content

Querying the Database

This page discusses how to query data on InterSystems IRIS® data platform.

Types of Queries

A query is a statement which performs data retrieval and generates a result set. A query can consist of any of the following:

  • A simple SELECT statement that accesses the data in a specified table or view.

  • A SELECT statement with JOIN syntax that accesses the data from several tables or views.

  • A UNION statement that combines the results of multiple SELECT statements.

  • A subquery that uses a SELECT statement to supply a single data item to an enclosing SELECT query.

  • In Embedded SQL, a SELECT statement that uses an SQL cursor to access multiple rows of data using a FETCH statement.

Using a SELECT Statement

A SELECT statement selects one or more rows of data from one or more tables or views. A simple SELECT is shown in the following example:


In this example, Name and DOB are columns (data fields) in the Sample.Person table.

The order that clauses must be specified in a SELECT statement is: SELECT DISTINCT TOP ... selectItems INTO ... FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY. This is the command syntax order. All of these clauses are optional, except SELECT selectItems. (The optional FROM clause is required to perform any operations on stored data, and therefore is almost always required in a query.) Refer to the SELECT statement syntax for details on the required order for specifying SELECT clauses.

SELECT Clause Order of Execution

The operation of a SELECT statement can be understood by noting its semantic processing order (which is not the same as the SELECT syntax order). The clauses of a SELECT are processed in the following order:

  1. FROM clause — specifies a table, a view, multiple tables or views using JOIN syntax, or a subquery.

  2. WHERE clause — restricts what data is selected using various criteria.

  3. GROUP BY clause — organizes the selected data into subsets with matching values; only one record is returned for each value.

  4. HAVING clause — restricts what data is selected from groups using various criteria.

  5. selectItem — selects a data fields from the specified table or view. A selectItem can also be an expression which may or may not reference a specific data field.

  6. DISTINCT clause — applied to the SELECT result set, it limits the rows returned to those that contain a distinct (non-duplicate) value.

  7. ORDER BY clause — applied to the SELECT result set, it sorts the rows returned in collation order by the specified field(s).

This semantic order shows that a table alias (which is defined in the FROM clause) can be recognized by all clauses, but a column alias (which is defined in the SELECT selectItems) can only be recognized by the ORDER BY clause.

To use a column alias in other SELECT clauses you can use a subquery, as shown in the following example:

      (SELECT Name AS Interns FROM Sample.Employee WHERE Age<21) 

In this example, Name and Age are columns (data fields) in the Sample.Person table, and Interns is a column alias for Name.

Selecting Fields

When you issue a SELECT, InterSystems SQL attempts to match each specified selectItem field name to a property defined in the class corresponding to the specified table. Each class property has both a property name and a SqlFieldName. If you defined the table using SQL, the field name specified in the CREATE TABLE command is the SqlFieldName, and InterSystems IRIS generated the property name from the SqlFieldName.

Field names, class property names, and SqlFieldName names have different naming conventions:

  • Field names in a SELECT statement are not case-sensitive. SqlFieldName names and property names are case-sensitive.

  • Field names in a SELECT statement and SqlFieldName names can contain certain non-alphanumeric characters following identifier naming conventions. Property names can only contain alphanumeric characters. When generating a property name, InterSystems IRIS strips out non-alphanumeric characters. InterSystems IRIS may have to append a character to create a unique property name.

The translation between these three names for a field determine several aspects of query behavior. You can specify a selectItem field name using any combination of letter case and InterSystems SQL will identify the appropriate corresponding property. The data column header name in the result set display is the SqlFieldName, not the field name specified in the selectItem. This is why the letter case of the data column header may differ from the selectItem field name.

You can specify a column alias for a selectItem field. A column alias can be in any mix of letter case, and can contain non-alphanumeric characters, following identifier naming conventions. A column alias can be referenced using any combination of letter case (for example, in the ORDER BY clause) and InterSystems SQL resolves to the letter case specified in the selectItem field. InterSystems IRIS always attempts to match to the list of column aliases before attempting to match to the list of properties corresponding to defined fields. If you have defined a column alias, the data column header name in the result set display is the column alias in the specified letter case, not the SqlFieldName.

When a SELECT query completes successfully, InterSystems SQL generates a result set class for that query. The result set class contains a property corresponding to each selected field. If a SELECT query contains duplicate field names, the system generates unique property names for each instance of the field in the query by appending a character. For this reason, you cannot include more than 36 instances of the same field in a query.

The generated result set class for a query also contains properties for column aliases. To avoid the performance cost of letter case resolution, you should use the same letter case when referencing a column alias as the letter case used when specifying the column alias in the SELECT statement.

In addition to user-specified column aliases, InterSystems SQL also automatically generates up to three aliases for each field name, aliases which correspond to common letter case variants of the field name. These generated aliases are invisible to the user. They are provided for performance reasons, because accessing a property through an alias is faster than resolving letter case through letter case translation. For example, if SELECT specifies FAMILYNAME and the corresponding property is familyname, InterSystems SQL resolves letter case using a generated alias (FAMILYNAME AS familyname). However, if SELECT specifies fAmILyNaMe and the corresponding property is familyname, InterSystems SQL must resolves letter case using the slower letter case translation process.

A selectItem item can also be an expression, an aggregate function, a subquery, a user-defined function, as asterisk, or some other value. For further details on selectItem items other than field names, refer to the selectItem argument of the SELECT command reference page.

The JOIN Operation

A JOIN provides a way to link data in one table with data in another table and are frequently used in defining reports and queries. Within SQL, a JOIN is an operation that combines data from two tables to produce a third, subject to a restrictive condition. Every row of the resulting table must satisfy the restrictive condition.

InterSystems SQL supports five types of joins (some with multiple syntactic forms): CROSS JOIN, INNER JOIN, LEFT OUTER JOIN, RIGHT OUTER JOIN, and FULL OUTER JOIN. Outer joins support the ON clause with a full range of conditional expression predicates and logical operators. There is partial support for NATURAL outer joins and outer joins with a USING clause. For definitions of these join types and further details, see JOIN.

If a query contains a join, all of the field references within that query must have an appended table alias. Because InterSystems IRIS does not include the table alias in the data column header name, you may wish to provide column aliases for selectItem fields to clarify which table is the source of the data.

The following example uses a join operation to match the “fake” (randomly-assigned) zip codes in Sample.Person with the real zip codes and city names in Sample.USZipCode. A WHERE clause is provided because USZipCode does not include all possible 5-digit zip codes:

SELECT P.Home_City,P.Home_Zip AS FakeZip,Z.ZipCode,Z.City AS ZipCity,Z.State
FROM Sample.Person AS P LEFT OUTER JOIN Sample.USZipCode AS Z 
ON P.Home_Zip=Z.ZipCode
ORDER BY P.Home_City

Queries Selecting Large Numbers of Fields

A query cannot select more than 1,000 selectItem fields.

A query selecting more than 150 selectItem fields may have the following performance consideration. InterSystems IRIS automatically generates result set column aliases. These generated aliases are provided for field names without user-defined aliases to enable rapid resolution of letter case variations. Letter case resolution using an alias is significantly faster than letter case resolution by letter case translation. However, the number of generated result set column aliases is limited to 500. Because commonly InterSystems IRIS generates three of these aliases (for the three most common letter case variations) for each field, the system generates aliases for roughly the first 150 specified fields in the query. Therefore, a query referencing less than 150 fields commonly has better result set performance than a query referencing significantly more fields. This performance issue can be avoided by specifying an exact column alias for each field selectItem in a very large query (for example, SELECT FamilyName AS FamilyName) and then making sure that you use the same letter case when referencing the result set item by column alias.

Defining and Executing Named Queries

You can define and execute a named query as follows:


You can define a query using CREATE QUERY, and then execute it by name using CALL. In the following example, the first is an SQL program that defines the query AgeQuery, the second is Dynamic SQL that executes the query:

CREATE QUERY Sample.AgeQuery(IN topnum INT DEFAULT 10,IN minage INT 20)
   SELECT TOP :topnum Name,Age FROM Sample.Person
   WHERE Age > :minage 
   ORDER BY Age ;
  SET mycall = "CALL Sample.AgeQuery(11,65)"
  SET tStatement = ##class(%SQL.Statement).%New()
  SET qStatus = tStatement.%Prepare(mycall)
    IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
  SET rset = tStatement.%Execute()
  DO rset.%Display()
DROP QUERY Sample.AgeQuery

Class Queries

You can define a query in a class. The class may be a %Persistent class, but does not have to be. This class query can reference data defined in the same class, or in another class in the same namespace. The tables, fields, and other data entities referred to in a class query must exist when the class that contains the query is compiled.

A class query is not compiled when the class that contains it is compiled. Instead, compilation of a class query occurs upon the first execution of the SQL code (runtime). This occurs when the query is prepared in Dynamic SQL using the %PrepareClassQuery() method. First execution defines an executable cached query.

The following class definition example defines a class query:

Class Sample.QClass Extends %Persistent [DdlAllowed]
  Query MyQ(Myval As %String) As %SQLQuery (CONTAINID=1,ROWSPEC="Name,Home_State") [SqlProc]
     SELECT Name,Home_State FROM Sample.Person 
     WHERE Home_State = :Myval  ORDER BY Name


The following example executes the MyQ query defined in the Sample.QClass in the previous example:

  SET Myval="NY"
  SET stmt=##class(%SQL.Statement).%New()
  SET status = stmt.%PrepareClassQuery("Sample.QClass","MyQ")
    IF status'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(status) QUIT}
  SET rset = stmt.%Execute(Myval)
  DO rset.%Display()
  WRITE !,"End of data"

The following Dynamic SQL example uses %SQL.StatementOpens in a new tab to execute the ByName query defined in the Sample.Person class, passing a string to limit the names returned to those that start with that string value:

  SET statemt=##class(%SQL.Statement).%New()
  SET cqStatus=statemt.%PrepareClassQuery("Sample.Person","ByName")
    IF cqStatus'=1 {WRITE "%PrepareClassQuery failed:" DO $System.Status.DisplayError(cqStatus) QUIT}
  SET rs=statemt.%Execute("L")
  DO rs.%Display()

For further details, refer to Defining and Using Class Queries.

Queries Invoking User-defined Functions

InterSystems SQL allows you to invoke class methods within SQL queries. This provides a powerful mechanism for extending the syntax of SQL.

To create a user-defined function, define a class method within a persistent InterSystems IRIS class. The method must have a literal (non-object) return value. This has to be a class method because there will not be an object instance within an SQL query on which to invoke an instance method. It also has to be defined as being an SQL stored procedure.

For example, we can define a Cube() method within the class MyApp.Person:

Class MyApp.Person Extends %Persistent [DdlAllowed]
/// Find the Cube of a number
ClassMethod Cube(val As %Integer) As %Integer [SqlProc]
    RETURN val * val * val

You can create SQL functions with the CREATE FUNCTION, CREATE METHOD or CREATE PROCEDURE statements.

To call an SQL function, specify the name of the SQL procedure. A SQL function may be invoked in SQL code anywhere where a scalar expression may be specified. The function name may be qualified with its schema name, or unqualified. Unqualified function names take either a user-supplied schema search path or the default schema name. A function name may be a delimited identifier.

An SQL function must have a parameter list, enclosed in parentheses. The parameter list may be empty, but the parentheses are mandatory. All specified parameters act as input parameters. Output parameters are not supported.

An SQL function must return a value.

For example, the following SQL query invokes a user-defined SQL function as a method, just as if it was a built-in SQL function:

SELECT %ID, Age, MyApp.Person_Cube(Age) FROM MyApp.Person

For each value of Age, this query will invoke the Cube() method and place its return value within the results.

SQL functions may be nested.

If the specified function is not found, InterSystems IRIS issues an SQLCODE -359 error. If the specified function name is ambiguous, InterSystems IRIS issues an SQLCODE -358 error.

Querying Serial Object Properties

A serial object property that is projected as a child table to SQL from a class using default storage (%Storage.Persistent) is also projected as a single column in the table projected by the class. The value of this column is the serialized value of the serial object properties. This single column property is projected as an SQL %List field.

For example, the column Home in Sample.Person is defined as Property Home As Sample.Address;. It is projected to Class Sample.Address Extends (%SerialObject), which contains the properties Street, City, State, and PostalCode. See Embedded Object (%SerialObject) for details on defining a serial object.

The following example returns values from individual serial object columns:

SELECT TOP 4 Name,Home_Street,Home_City,Home_State,Home_PostalCode
FROM Sample.Person

The following example returns the values for all of the serial object columns (in order) as a single %List format string, with the value for each column as an element of the %List:

FROM Sample.Person

By default, this Home column is hidden and is not projected as a column of Sample.Person.

Querying Collections

Collections may be referenced from the SQL WHERE clause, as follows:

 WHERE FOR SOME %ELEMENT(collectionRef) [AS label] (predicate)

The FOR SOME %ELEMENT clause can be used for list collections and arrays that specify STORAGEDEFAULT="list". The predicate may contain one reference to the pseudo-columns %KEY, %VALUE, or both. A few examples should help to clarify how the FOR SOME %ELEMENT clause may be used. The following returns the name and the list of FavoriteColors for each person whose FavoriteColors include 'Red'.

SELECT Name,FavoriteColors FROM Sample.Person
    WHERE FOR SOME %ELEMENT(FavoriteColors) (%Value = 'Red')

Any SQL predicate may appear after the %Value (or %Key), so for example the following is also legal syntax:

SELECT Name,FavoriteColors FROM Sample.Person
    WHERE FOR SOME %ELEMENT(Sample.Person.FavoriteColors)
        (%Value IN ('Red', 'Blue', 'Green'))

A list collection is considered a special case of an array collection that has sequential numeric keys 1, 2, and so on. Array collections may have arbitrary non-null keys:

 FOR SOME (children) (%Key = 'betty' AND %Value > 5)

In addition to the built-in list and array collection types, generalized collections may be created by providing a BuildValueArray() class method for any property. The BuildValueArray() class method transforms the value of a property into a local array, where each subscript of the array is a %KEY and the value is the corresponding %VALUE.

In addition to simple selections on the %KEY or %VALUE, it is also possible to logically connect two collections, as in the following example:

   FOR SOME %ELEMENT(flavors) AS f
      (f.%VALUE IN ('Chocolate', 'Vanilla') AND
       FOR SOME %ELEMENT(toppings) AS t
           (t.%VALUE = 'Butterscotch' AND
            f.%KEY = t.%KEY))

This example has two collections: flavors and toppings, that are positionally related through their key. The query qualifies a row that has chocolate or vanilla specified as an element of flavors, and that also has butterscotch listed as the corresponding topping, where the correspondence is established through the %KEY.

You can change this default system-wide using the CollectionProjection option of the $SYSTEM.SQL.Util.SetOption()Opens in a new tab method. SET status=$SYSTEM.SQL.Util.SetOption("CollectionProjection",1,.oldval) to project a collection as a column if the collection is projected as a child table; the default is 0. Changes made to this system-wide setting takes effect for each class when that class is compiled or recompiled. You can use $SYSTEM.SQL.Util.GetOption("CollectionProjection")Opens in a new tab to return the current setting.

For information on indexing a collection, refer to Indexing Collections.

Usage Notes and Restrictions

  • FOR SOME %ELEMENT may only appear in the WHERE clause.

  • %KEY and/or %VALUE may only appear in a FOR predicate.

  • Any particular %KEY or %VALUE may be referenced only once.

  • %KEY and %VALUE may not appear in an outer join.

  • %KEY and %VALUE may not appear in a value expression (only in a predicate).

Queries Invoking Free-text Search

InterSystems IRIS supports what is called “free-text search,” which includes support for:

  • Wildcards

  • Stemming

  • Multiple-word searches (also called n-grams)

  • Automatic classification

  • Dictionary management

This feature enables SQL to support full text indexing, and also enables SQL to index and reference individual elements of a collection without projecting the collection property as a child table. While the underlying mechanisms that support collection indexing and full text indexing are closely related, text retrieval has many special properties, and therefore special classes and SQL features have been provided for text retrieval.

For further details refer to Using InterSystems SQL Search.

Pseudo-Field Variables

InterSystems SQL queries support the following pseudo-field values:

  • %ID — returns the RowID field value, regardless of the actual name of the RowID field.

  • %TABLENAME — returns the qualified name of an existing table that is specified in the FROM clause. The qualified table name is returned in the letter case used when defining the table, not the letter case specified in the FROM clause. If the FROM clause specifies an unqualified table name, %TABLENAME returns the qualified table name (schema.table), with the schema name supplied from either a user-supplied schema search path or the system-wide default schema name. For example, if the FROM clause specified mytable, the %TABLENAME variable might return SQLUser.MyTable.

  • %CLASSNAME — returns the qualified class name (package.class) corresponding to an existing table specified in the FROM clause. For example, if the FROM clause specified SQLUser.mytable, the %CLASSNAME variable might return User.MyTable.


    The %CLASSNAME pseudo-field value should not be confused with the %ClassName()Opens in a new tab instance method. They return different values.

Pseudo-field variables can only be returned for a table that contains data.

If multiple tables are specified in the FROM clause you must use table aliases, as shown in the following Embedded SQL example:

      INTO :name,:rid,:ptname,:etname
    FROM Sample.Person AS P,Sample.Employee AS E)
      IF SQLCODE<0 {WRITE "SQLCODE error ",SQLCODE," ",%msg  QUIT}
      ELSEIF SQLCODE=100 {WRITE "Query returns no results"  QUIT}
    WRITE ptname,"Person table Name is: ",name,!
    WRITE ptname,"Person table RowId is: ",rid,!
    WRITE "P alias TableName is: ",ptname,!
    WRITE "E alias TableName is: ",etname,!

The %TABLENAME and %CLASSNAME columns are assigned the default column name Literal_n, where n is the selectItem position of the pseudo-field variable in the SELECT statement.

Query Metadata

You can use Dynamic SQL to return metadata about the query, such as the number of columns specified in the query, the name (or alias) of a column specified in the query, and the data type of a column specified in the query.

The following ObjectScript Dynamic SQL example returns the column name and an integer code for the column's ODBC data type for all of the columns in Sample.Person:

  SET myquery="SELECT * FROM Sample.Person"
  SET rset = ##class(%SQL.Statement).%New()
  SET qStatus = rset.%Prepare(myquery)
    IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
  SET x=rset.%Metadata.columns.Count()
  WHILE x>0 {
    SET column=rset.%Metadata.columns.GetAt(x)
  WRITE !,x," ",column.colName," ",column.ODBCType
  SET x=x-1 }
  WRITE !,"end of columns"

In this example, columns are listed in reverse column order. Note that the FavoriteColors column, which contains list structured data, returns a data type of 12 (VARCHAR) because ODBC represents an InterSystems IRIS list data type value as a string of comma-separated values.

For further details, see Dynamic SQL and see the %SQL.StatementOpens in a new tab class in the InterSystems Class Reference.

Queries and Enterprise Cache Protocol (ECP)

InterSystems IRIS implementations that use Enterprise Cache Protocol (ECP), such as distributed cache clusters, can synchronize query results. ECP is a distributed data caching architecture that manages the distribution of data and locks among a heterogeneous network of server systems.

If ECP synchronization is active, each time a SELECT statement is executed InterSystems IRIS forces all pending ECP requests to the dataserver. On completion this guarantees that the client cache is in sync. This synchronization occurs in the Open logic of the query. This is in the OPEN cursor execution if this is a cursor query.

To activate ECP synchronization system-wide, use the $SYSTEM.SQL.Util.SetOption()Opens in a new tab method, as follows: SET status=$SYSTEM.SQL.Util.SetOption("ECPSync",1,.oldval); the default is 0. To determine the current setting, call $SYSTEM.SQL.CurrentSettings()Opens in a new tab.

For further details, refer to Horizontally Scaling Systems for User Volume with InterSystems Distributed Caching.

Cached Queries

The system automatically maintains a cache of prepared SQL statements (“queries”). This permits the re-execution of an SQL query without repeating the overhead of optimizing the query and developing a Query Plan; this improves performance of the query on subsequent executions. A cached query is created when certain SQL statements are prepared. Preparing a query occurs at runtime, not when the routine containing the SQL query code is compiled. Commonly, a prepare is immediately followed by the first execution of the SQL statement, though in Dynamic SQL it is possible to prepare a query without executing it.

Subsequent executions ignore the prepare statement and instead access the cached query. To force a new prepare of an existing query it is necessary to purge the cached query.

All invocations of SQL create cached queries, whether invoked in an ObjectScript routine or a class method.

  • Dynamic SQL, ODBC, JDBC, and the $SYSTEM.SQL.DDLImport() method create a cached query when the query is prepared. The Management Portal execute SQL interface, the InterSystems SQL Shell, and the %SYSTEM.SQL.Execute()Opens in a new tab method use Dynamic SQL, and thus use a prepare operation to create cached queries.

    They are listed in the Management Portal general Cached Queries listing for the namespace (or specified schema), the Management Portal Catalog Details Cached Queries listings for each table being accessed, and the SQL Statements listings.

  • Class Queries create a cached query upon prepare (%PrepareClassQuery() method) or first execution (CALL).

    They are listed in the Management Portal general Cached Queries listing for the namespace. If the class query is defined in a Persistent class, the cached query is also listed in the Catalog Details Cached Queries for that class. It is not listed in the Catalog Details for the table(s) being accessed. It is not listed in the SQL Statements listings.

  • Embedded SQL creates a cached query upon first execution of the SQL code, or the initiation of code execution by invoking the OPEN command for a declared cursor. Embedded SQL cached queries are listed in the Management Portal Cached Queries listings with a Query Type of Embedded cached SQL, and the SQL Statements listings.


A cached query is created when you prepare the query. For this reason, it is important not to put a %Prepare() method in a loop structure. A subsequent %Prepare() of the same query (differing only in specified literal values) uses the existing cached query rather than creating a new cached query.

Changing the SetMapSelectability()Opens in a new tab value for a table invalidates all existing cached queries that reference that table. A subsequent prepare of an existing query creates a new cached query and removes the old cached query from the listing.

Comments are stripped from the text of the query when it is cached. A cached query can include comment options following the query text, such as /*#OPTIONS {"optionName":value} */.

All comments in queries issued over a JDBC or ODBC connection are stripped from the corresponding cached query, unless it begins with /#TAG and ends with a backslash (/).

The creation of a cached query is not part of a transaction. The creation of a cached query is not journaled.

Listing Cached Queries

You can list (and manage) the contents of the query cache using the InterSystems IRIS Management Portal. From System Explorer, select SQL. Select a namespace by clicking the name of the current namespace displayed at the top of the page; this displays the list of available namespaces. On the left side of the screen open the Cached Queries folder. Selecting one of these cached queries displays the details.

The Query Type can be one of the following values:

  • %SQL.Statement Dynamic SQL: a Dynamic SQL query using %SQL.Statement.

  • Embedded cached SQL: an Embedded SQL query.

  • ODBC/JDBC Statement: a dynamic query from either ODBC or JDBC.

The Statement is displayed for a Dynamic SQL cached query. This consists of the statement hash, which is a selectable link that takes you to the SQL Statement Details, and the plan state, such as (Unfrozen) or (Frozen/Explicit).

When you successfully prepare an SQL statement, the system generates a new class that implements the statement. If you have set the Retain cached query source system-wide configuration option, the source code for this generated class is retained and can be opened for inspection using Studio. To do this, go to the InterSystems IRIS Management Portal. From System Administration, select Configuration, then SQL and Object Settings, then SQL. On this screen you can set the Retain cached query source option. If this option is not set (the default), the system generates and deploys the class and does not save the source code. You can set this system-wide behavior with the CachedQuerySaveSource SQL setting.

Purge Cached Queries

A cached query is deleted when you purge cached queries. There are four methods of purging cached queries:

In addition, modifying a table definition automatically purges any queries that reference that table. Issuing a Prepare or Purge automatically requests an exclusive system-wide lock while the query cache metadata is updated. To turn these automatic requests off, modify the CachedQueryLockTimeout SQL setting.

FeedbackOpens in a new tab