Skip to main content

Querying the Database

This chapter 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 ... select-items INTO ... FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY. This is the command syntax order. All of these clauses are optional, except SELECT select-items. (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. select-item — selects a data fields from the specified table or view. A select-item 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 select-items) 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 select-item 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 select-item 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 select-item. This is why the letter case of the data column header may differ from the select-item field name.

You can specify a column alias for a select-item 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 select-item 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 select-item 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 select-item items other than field names, refer to The select-item section 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, refer to the JOIN page in the InterSystems SQL Reference.

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 select-item 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 select-item fields.

A query selecting more than 150 select-item 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 select-item 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:

  • Define the query using CREATE QUERY. This query is defined as a stored procedure, and can be executed using CALL.

  • Define a class query (a query defined in a class definition). A class query is projected as a stored procedure. It can be executed using CALL. A class query can also be prepared using the %SQL.Statement %PrepareClassQuery() method, and then executed using the %Execute() method. See “Using Dynamic SQL”.


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.Statement 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” in Defining and Using Classes.

For information on query names automatically assigned to executed queries, refer to the Cached Queries chapter of InterSystems SQL Optimization Guide.

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) in the “Defining Tables” chapter 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() 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") to return the current setting.

For information on indexing a collection, refer to Indexing Collections in the “Defining and Building Indices” chapter of the InterSystems SQL Optimization Guide.

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() 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 select-item position of the pseudo-field variable in the SELECT statement.

Terminating a Running Query

If a query is running for an excessive amount of time, you may wish to interrupt (terminate) the query execution.

You can terminate a query that has been invoked via any of the supported interfaces, including Dynamic SQL, Embedded SQL, Cached Query, SQL Server/xDBC, and CSP. Interrupting an executing query requires %Admin_Operate:USE privilege. Use the following method:

Parameters Description
pid The process ID (pid) of the job running the query
IntReason Optional — Reserved for future use. Do not specify.
timeout Optional — An integer specifying the timeout, in seconds, for this interrupt operation. If omitted, defaults to 10 seconds.
interface Optional — An integer flag specifying the type of SQL query. The available values are: 0 = Embedded SQL. 1 = Cached Query. 2 = SQL Server/xDBC. 3 = CSP (for example, the Management Portal SQL interface) . 9 = General-purpose. If specified, this optional parameter is used to optimize the interrupt. The default is Embedded SQL.

The SQLInterrupt() method returns a %Status value: Success returns a status of 1. Failure returns an object expression that begins with 0, followed by encoded error information. SQLInterrupt() for an Embedded SQL query running on process 12345 is shown in the following Terminal example:

USER>SET stat=$$SQLInterrupt^%apiSQL(12345,,,0)

The interrupted query generates an SQLCODE -456 with the %msg "SQL query execution interrupted by user".

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, refer to the Dynamic SQL chapter of this manual, and the %SQL.Statement class in the InterSystems Class Reference.

Fast Select

InterSystems IRIS supports Fast Select, an internal optimization for rapid query execution over ODBC and JDBC. This optimization maps InterSystems globals to Java objects. It passes the contents of a global node (a data record) as a Java object. Upon receiving these Java objects it extracts the desired column values from them and generates a result set. InterSystems IRIS automatically applies this optimization wherever possible. This optimization is automatic and invisible to the user; when a query is Prepared, InterSystems IRIS flags the query either for execution using the Fast Select mechanism or for execution using the standard query mechanism.

Fast Select is applied to %PARALLEL queries and queries against a sharded table if the query only references fields, constants, or expressions that reference fields and/or constants.

Fast Select must be supported on both the server and the client. To enable or disable Fast Select in the client, use Properties in the definition of the class instance as follows:

Properties p = new Properties();
p.setProperty("FeatureOption","3");   / 1 is fast Select, 2 is fast Insert, 3 is both

Because of the difference in performance, it is important for the user to know what circumstances restrict the application of Fast Select.

Table Restrictions: the following types of tables cannot be queried using Fast Select:

  • A linked table

  • A table whose master/data map has multiple nodes

  • A table that has multiple fields mapped to the same data location (this is only possible using %Storage.SQL)

Field Restrictions: if the following columns are included in the select-item list, the query cannot be executed using Fast Select. These types of columns can be defined in the table, but the query cannot select them:

  • A stream field (data type %Stream.GlobalCharacter or %Stream.GlobalBinary)

  • A field that is computed when queried (COMPUTECODE Calculated or Transient)

  • A field that is a list collection (has LogicalToOdbc conversion)

  • A field that performs LogicalToOdbc conversion and is not of data type %Date, %Time, or %PosixTime

  • A field that has overridden LogicalToOdbc conversion code

  • A field that performs LogicalToStorage conversion

  • A field whose map data entry uses retrieval code

  • A field whose map data entry has a delimiter (not %List storage)

  • A field that is mapped to a piece of nested storage

Index Restriction: Fast Select is not used if the select-item list consists of only the %ID field and/or fields that are all mapped to the same index.

If a query is executed using Fast Select, this fact is flagged in the SELECT audit event in the Audit Database, provided that %System/%SQL/XDBCStatement is enabled. For further details on system-wide SQL event auditing, refer to Auditing Dynamic SQL.

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() 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().

For further details, refer to the “Horizontally Scaling Systems for User Volume with InterSystems Distributed Caching” chapter of the Scalability Guide.