Using set inclusion and exclusion predicates

In SQL, you can embed an SQL query in-line within another query. A query that is embedded inside another query is called a subselect or a subquery. The SQL query language does not provide a keyword to create a subselect. The structure of your SQL query creates a subselect, as shown in the following SQL syntax, which creates a subselect to provide a set of values for an IN predicate.

SELECT column_name1 FROM table_name
  WHERE column_name1 
    IN (SELECT column_name2 FROM table_name2
          WHERE column_name2 LIKE ‘%some_value%’);

The following example uses a subselect to find users that have created notes of the specified topic types:

SELECT ID FROM pc_user
  WHERE ID
    IN (SELECT AuthorID FROM pc_note
          WHERE Topic IN (1, 10006));

The following Gosu code constructs and executes a functionally equivalent query to the preceding SQL example.

uses gw.api.database.Query
uses gw.api.database.InOperation

var outerQuery = Query.make(User) // Returns User Query
var innerQuery = Query.make(Note) // Returns Note Query

// Filter the inner query
innerQuery.compareIn(Note#Topic, {NoteTopicType.TC_GENERAL, NoteTopicType.TC_LEGAL})
// Filter the outer query by using a subselect
outerQuery.subselect(User#ID, InOperation.CompareIn, innerQuery, Note#Author )

// Alternatively, use simpler syntax if you do not need to filter the inner query
// queryParent.subselect(User#ID, InOperation.CompareIn, Note#Author )

var result = outerQuery.select() // Returns User query Object result
for (user in result) {            // Execute the query and iterate the results
  print(user.DisplayName)
}

The subselect method is overloaded with many method signatures for different purposes. The preceding Gosu code shows two of these method signatures.

The query builder APIs generate an SQL IN clause for the CompareIn method. The query builder APIs generate an SQL NOT EXISTS clause for the CompareNotIn method. These methods do not accept a null value in the list of values. A run-time error occurs if you provide a null value in set of comparison values.