OQL Clauses

Introduction

OQL clauses are reserved words that structure an OQL query into sections. Each section, and therefore clause, is responsible for a different aspect of the query. Every query has two mandatory clauses: SELECT and FROM.

For example:

SELECT LastName, Address
FROM Sales.Customer

These basic clauses define the data that needs to be retrieved and which entity or entities should be used as a source. Other clauses are not mandatory. They are used to specify limitations and other rules on the data being retrieved.

Clauses must be presented in the following order, but can be left out if they are optional:

1. SELECT
2. FROM
3. WHERE
4. GROUP BY
5. HAVING (allowed only in combination with GROUP BY)
6. ORDER BY
7. LIMIT
8. OFFSET

The UNION clause defies the usual order presented above. It will be presented in a Union Clause section at the end.

SELECT Clause

The SELECT clause specifies which entity attributes or other specified data must be retrieved. The clause returns all the requested values of objects which match the SELECT clause.

The SELECT clause consists of the term SELECT and one or more column definitions. Each column definition must be separated by a comma. Each column definition defines a column or a set of columns in the result. Each single value column definition can have an alias, which will be the name of the column in the result.

Syntax

The syntax is as follows:

SELECT [ DISTINCT ]
	{
			*
		| { entity_name | from_alias }.*
		| { expression [ [ AS ] column_alias ] } [ ,...n ]
	}

Selecting Specific Attributes

A simple way to define what is to be retrieved is to specify particular attributes of an entity. For example:

SELECT FirstName AS FName, LastName AS LName
FROM Sales.Customer

It is possible to specify aliases for columns – in the example above: FirstName AS FName. The keyword AS is not mandatory, so the following query is equivalent to the one above:

SELECT FirstName FName, LastName LName
FROM Sales.Customer

An alias is an alternative name which replaces the column name in the result. For example, when the name attribute is retrieved, the result column is “Name”. With an alias, you can call the result column something else, like “Customer_Name”. An alias cannot contain spaces.

Selecting All Attributes Using *

Using * (asterisk) in the SELECT clause specifies that the values of all attributes from all entities in the FROM clause should be returned.

Specifying entity_name/* and from_alias/* specify that the values of all attributes of the specified entity or expression of the FROM clause should be returned.

entity_name can optionally be put in double quotes. If the entity name is a reserved OQL word (like Order or Group), double quotes are mandatory.

Examples

The following query returns the values of all the attributes of Sales.Customer

SELECT *
FROM Sales.Customer

The following query returns all attributes of objects of Sales.Request that are associated with Sales.Customer objects (see Select from Multiple Tables using JOIN)

SELECT Sales.Request/*
FROM Sales.Customer
JOIN Sales.Customer/Sales.Customer_Request/Sales.Request

The following query is equivalent to the previous one, but it uses table aliases

SELECT Req/*
FROM Sales.Customer Cust
JOIN Cust/Sales.Customer_Request/Sales.Request Req

Selecting Distinct Values with DISTINCT

The keyword DISTINCT specifies that duplicate rows must not be included in the result. If used, DISTINCT should follow directly after SELECT. It is not possible to request only some attributes to be distinct. Null is treated as a separate value. If the query result contains multiple Null values, DISTINCT is applied to them the same way it would be applied to other values.

In this example, the Sales.Customer entity has 4 objects.

SELECT FirstName FName, LastName LName
FROM Sales.Customer

returns

The following query, using DISTINCT will result only in unique last names

SELECT DISTINCT LastName LName
FROM Sales.Customer

returns

If multiple attributes are selected, the result is all the unique combinations that are present in the database:

SELECT DISTINCT FirstName FName, LastName LName
FROM Sales.Customer

returns

DISTINCT can also be combined with *.

SELECT DISTINCT *
FROM Sales.Customer

returns

Expressions

It is possible to use more complex expressions in SELECT. This is explained in detail in OQL Expressions.

It is also possible to use a subquery. See Subquery in SELECT for more details.

Selecting Attributes over Associations

A unique feature of OQL is the ability to access attributes of associated objects using paths. For example:

SELECT
	Number AS RequestNumber,
	Sales.Request/Sales.Request_Customer/Sales.Customer/LastName AS CustomerName
FROM Sales.Request

It is possible to build paths over multiple associations. Associated entities can be reached from both directions. System associations System.owner and System.changedBy can also be used in such association paths, assuming they are enabled for the entity. For example:

SELECT
	LastName AS CustomerName,
	Sales.Customer/Sales.Request_Customer/Sales.Request/System.owner/System.User/Name AS UserName
FROM Sales.Customer

In the case when the association multiplicity type is one-to-many or many-to-many, every attribute over association may result in multiple results per object. If there are multiple attributes over association in the FROM clause, the result will be a cartesian product of associated objects meaning that there will be a row for every combination of associated objects. If you want to avoid that effect, consider rewriting the query using JOIN.

For example, when a Sales.Customer with LastName “Doe” has two associated Sales.Request objects with a Number attribute with values 1 and 2, the following query will return 4 rows:

SELECT
	LastName AS CustomerName,
	Sales.Customer/Sales.Request_Customer/Sales.Request/Number AS RequestNumber,
	Sales.Customer/Sales.Request_Customer/Sales.Request/Number AS OrthogonalRequestNumber
FROM Sales.Customer
WHERE Sales.Customer/LastName = 'Doe'

FROM Clause

The FROM clause specifies the entities or other source or sources from which the data must be retrieved.

This clause starts with the FROM keyword, followed by an entity name. To select data from additional entities, add these entities via the JOIN keyword. In OQL, this syntax is a little more strict than that of the SQL FROM clause.

Syntax

Below is the full syntax of the FROM clause:

FROM
	{
		entity_name | ( sub_oql_query )
	}
	[ [ AS ] from_alias ]

	{
		{ INNER | { { LEFT | RIGHT | FULL } [ OUTER ] } } JOIN
		entity_path | entity_name | ( sub_oql_query ) [ [ AS ] from_alias ]
		[ ON <constraint> ]
	} [ ,...n ]

Select from One Entity

Below is an example of a simple select from one entity:

SELECT Sales.Customer/LastName
FROM Sales.Customer

You can use an alias to replace the entity name:

SELECT Cust/LastName
FROM Sales.Customer AS Cust

Select from Multiple Entities

You can specify multiple tables in FROM.

SELECT *
FROM Sales.Customer

SELECT *
FROM Sales.Request

SELECT *
FROM Sales.Customer, Sales.Request

This returns get all attributes of both entities. Every object of the first entity is combined with every object of the second entity, which results in a cartesian product of objects of the two entities.

You can specify conditions to filter the results in a WHERE clause (see more details and examples in WHERE Clause, below)

SELECT *
FROM Sales.Customer Cust, Sales.Request Req
WHERE Cust.LastName = Req.CustomerName

returns

To avoid ambiguity in case of duplicate attribute names, you should use the entity name or alias when specifying columns to be selected. The format in that case is, respectively, <module>.<entity>/<attribute> or <alias>/<attribute>. You can also retrieve all attributes of a particular entity using <alias>/*. For example:

SELECT Cust/FirstName, Req/*
FROM Sales.Customer Cust, Sales.Request Req
WHERE Cust.LastName = Req.CustomerName

returns

Select from Multiple Tables using JOIN

OQL supports the JOIN syntax, which is similar to SQL. There are 4 main types of JOIN:

• INNER JOIN or simply JOIN
• LEFT JOIN or LEFT OUTER JOIN
• RIGHT JOIN or RIGHT OUTER JOIN
• FULL JOIN or FULL OUTER JOIN

In addition to standard SQL syntax, OQL supports joins over associations.

The syntax is as follows:

{ INNER | { { LEFT | RIGHT | FULL } [ OUTER ] } } JOIN
entity_path | entity_name | ( sub_oql_query ) [ [ AS ] from_alias ]
[ ON <constraint> ]

entity_path

entity_path specifies the entity to join and the path to this entity from an earlier defined entity in the FROM clause.

For example, the path Crm.Customer/Crm.Customer_Address/Crm.Address defines an association path from the entity Crm.Customer to an associated entity Crm.Address.

As with entity_name, double quotes can be used.

If entity_path is specified, the ON condition is optional. If entities are joined by name without using an association path, the ON condition is mandatory.

JOIN Types

INNER JOIN

An INNER JOIN is the most common join operation between entities and represents the default join type. The query compares each row of entity A with each row of entity B to find all the pairs of rows that have an association and/or satisfy the JOIN predicate. If the association exists and the JOIN predicate is satisfied, the column values for each matched pair of rows of A and B are combined into a resulting row.

Example of an INNER JOIN with predicate:

SELECT Cust/LastName, Req/Number
FROM Sales.Customer Cust
JOIN Sales.Request Req ON Cust.LastName = Req.CustomerName

If the model contains an association between Sales.Request and Sales.Customer, a more logical way to write this query would be to use a join on association:

SELECT Cust/LastName, Req/Number
FROM Sales.Customer Cust
JOIN Cust/Sales.Request_Customer/Sales.Request Req

LEFT OUTER JOIN

A LEFT OUTER JOIN query compares each row of entity A with each row of entity B to find all pairs of rows which have an association and thus satisfy the JOIN predicate. When the association exists and the JOIN predicate is satisfied, column values for each matched pair of rows of A and B are combined into a resulting row.

However, in contrast to the INNER JOIN construction, the query will also return rows of entity A which do not match entity B. When columns of entity B are specified, these columns contain a null value for these rows.

The syntax is as follows:

LEFT [ OUTER ] JOIN entity_path [ ON <constraint> ]

Example of a LEFT OUTER JOIN with predicate:

SELECT Cust/LastName, Req/Number
FROM Sales.Customer Cust
LEFT OUTER JOIN Sales.Request Req ON Cust.LastName = Req.CustomerName

LEFT OUTER JOIN over association is also supported:

SELECT Cust/LastName, Req/Number
FROM Sales.Customer Cust
LEFT OUTER JOIN Cust/Sales.Request_Customer/Sales.Request Req

RIGHT OUTER JOIN

A RIGHT OUTER JOIN query compares each row of entity A with each row of entity B to find all pairs of rows which have an association and thus satisfy the JOIN predicate. If the association exists and the JOIN predicate is satisfied, the column values for each matched pair of rows of A and B are combined into a resulting row.

However, in contrast to the INNER JOIN construction, rows from entity B that do not match entity A will also be returned. When columns of entity A are specified, these columns contain a null value for these rows.

The syntax is as follows:

RIGHT [ OUTER ] JOIN entity_path [ ON <constraint> ]

Example of a RIGHT OUTER JOIN with predicate:

SELECT Cust/LastName, Req/Number
FROM Sales.Customer Cust
RIGHT OUTER JOIN Sales.Request Req ON Cust.LastName = Req.CustomerName

RIGHT OUTER JOIN over association is also supported:

SELECT Cust/LastName, Req/Number
FROM Sales.Customer Cust
RIGHT OUTER JOIN Cust/Sales.Request_Customer/Sales.Request Req

FULL OUTER JOIN

A FULL OUTER JOIN query compares each row of entity A with each row of entity B to find all pairs of rows which have an association and thus satisfy the JOIN predicate. When the association exists and the JOIN predicate is satisfied, column values for each matched pair of rows from A and B are combined into a result row.

However, in contrast to the INNER JOIN construction, data from entities that do not match will also be returned. For these rows, columns of missing entities will contain null values.

The syntax is as follows:

FULL [ OUTER ] JOIN entity_path [ ON <constraint> ]

Example of a FULL OUTER JOIN with predicate:

SELECT Cust/LastName, Req/Number
FROM Sales.Customer Cust
FULL OUTER JOIN Sales.Request Req ON Cust.LastName = Req.CustomerName

FULL OUTER JOIN over association is also supported:

SELECT Cust/LastName, Req/Number
FROM Sales.Customer Cust
FULL OUTER JOIN Cust/Sales.Request_Customer/Sales.Request Req

Select from a Subquery

See Subquery in FROM for details.

WHERE Clause

The WHERE clause specifies how the data being retrieved must be constrained.

Syntax

The syntax is as follows:

WHERE <constraint>

<constraint> is an expression of type BOOLEAN. Expressions can consist of simple comparisons using operators, functions, keywords, parameters, and system variables. If the result of the expression is True for a particular row, that row is included in the result. Rows that do not match the expression are not included in the result.

For more information, see OQL Expressions.

Examples

The following query retrieves all customers whose name is equal to “Doe”:

SELECT FirstName, LastName
FROM Sales.Customer
WHERE LastName = 'Doe'

It is possible to specify multiple conditions in a constraint using logical operators AND and OR.

For example:

SELECT CustomerName, Number
FROM Sales.Request
WHERE
	CustomerName = 'Doe'
	OR
	CustomerName != 'Doe'
	AND
	Number < 0

You can modify the precedence of logical operators using parentheses:

SELECT CustomerName, Number
FROM Sales.Request
WHERE
	(
		CustomerName = 'Doe'
		OR
		CustomerName != 'Doe'
	)
	AND
	Number < 0

You can also use a subquery in WHERE. See Subquery in WHERE for examples.

Entities over Associations in WHERE Clause

A feature that is specific to OQL and is not in the standard SQL syntax is using paths to other entities over associations.

For example, when selecting from entity Sales.Customer, we can access an attribute of the associated entity Sales.Request:

SELECT FirstName, LastName
FROM Sales.Customer
WHERE
	Sales.Customer/Sales.Request_Customer/Sales.Request/Number = 1

WHERE Clause Returns NULL

In some cases, the expression in WHERE can result in NULL. In that case, the WHERE expression is equal to FALSE, and the query returns no rows.

SELECT FirstName, LastName
FROM Sales.Customer
WHERE NULL

GROUP BY Clause

The GROUP BY clause groups OQL query results into summary rows based on the values of one or more attributes. You can filter aggregated results further using a HAVING clause.

Syntax

The syntax is as follows:

GROUP BY
	expression [ ,...n ]

[HAVING <constraint>]

Using GROUP BY

When a query contains a GROUP BY clause, then its SELECT clause can contain only aggregate functions and attributes and other expressions used in GROUP BY. If an attribute is present in SELECT, but not present in GROUP BY, the query is invalid.

SELECT Brand, City, Stock, Address, LocationNumber
FROM Sales.Location

GROUP BY with Single Aggregate

The following query retrieves total stock per brand:

SELECT Brand, SUM(Stock) AS SumStock
FROM Sales.Location
GROUP BY Brand

GROUP BY with Multiple Aggregates

You can specify multiple aggregate functions in combination with GROUP BY:

SELECT
	Brand,
	SUM(Stock) AS SumStock,
	MIN(Stock) AS MinStock,
	MAX(Stock) AS MaxStock
FROM Sales.Location
GROUP BY Brand

GROUP BY Multiple Attributes

You can also group by multiple attributes:

SELECT Brand, City, SUM(Stock) AS SumStock
FROM Sales.Location
GROUP BY Brand, City

Using Functions with GROUP BY

You can use functions of attributes in the SELECT clause, but only if those attributes are present in the GROUP BY clause:

SELECT Brand, LENGTH(Brand) AS NameLen, SUM(Stock) AS SumStock
FROM Sales.Location
GROUP BY Brand

You can also use functions of attributes in GROUP BY. Please note that if GROUP BY contains a function of an attribute, the attribute itself cannot be present in the SELECT clause because that would lead to ambiguity.

SELECT LENGTH(Brand) AS NameLen, SUM(Stock) AS SumStock
FROM Sales.Location
GROUP BY LENGTH(Brand)

Using GROUP BY with HAVING

The HAVING clause is used to filter aggregated results of GROUP BY. The difference between HAVING and WHERE is that WHERE is applied to every object before the objects are grouped, and HAVING is applied only to the aggregate rows.

The following query returns only aggregate rows for brands with more than one location:

SELECT Brand, SUM(Stock) AS SumStock, COUNT(*) AS LocationCount
FROM Sales.Location
GROUP BY Brand
HAVING COUNT(*) > 1

You can use aggregate functions that are not present in SELECT:

SELECT Brand
FROM Sales.Location
GROUP BY Brand
HAVING COUNT(*) > 1 AND SUM(Stock) < 20

You can also use more complex expressions such as subqueries and functions in HAVING:

SELECT Brand
FROM Sales.Location loc
GROUP BY Brand
HAVING
	COUNT(*) > 1
	AND
	EXISTS (
		SELECT *
		FROM Sales.Storage stor
		WHERE stor/AvailableStorage >= SUM(loc/Stock)
	)

ORDER BY Clause

The ORDER BY clause specifies the sort order used on columns returned in a SELECT statement. Multiple columns can be specified. Columns are ordered in the sequence of the items in the ORDER BY clause.

Syntax

The syntax is as follows:

ORDER BY
	{
		order_by_expression [ ASC | DESC ]
	} [ ,...n ]

order_by_expression

order_by_expression specifies an attribute of an entity or an alias from the FROM clause to sort on.

For example:

SELECT FirstName, LastName
FROM Sales.SalesPerson
ORDER BY LastName

ASC

ASC specifies that the results must be returned in ascending order, from the lowest to the highest value. This is the default sort type, so results are equivalent to not specifying ASC.

For example:

SELECT FirstName, LastName
FROM Sales.SalesPerson
ORDER BY LastName ASC

DESC

DESC specifies that the results must be returned in descending order, from the highest to the lowest value.

For example:

SELECT FirstName, LastName
FROM Sales.SalesPerson
ORDER BY LastName DESC

Multiple ORDER BY Criteria

Multiple criteria can be specified in ORDER BY. Separate each criterion with a comma.

For example:

SELECT FirstName, LastName
FROM Sales.SalesPerson
ORDER BY LastName, FirstName

You can apply ASC and DESC modifiers to each criterion separately:

SELECT FirstName, LastName
FROM Sales.SalesPerson
ORDER BY LastName DESC, FirstName ASC

ORDER BY Associated Attribute

OQL allows you to specify paths to attributes of associated entities in the ORDER BY clause.

SELECT LastName
FROM Sales.Customer
ORDER BY Sales.Customer/Sales.Customer_Request/Sales.Request/Number

LIMIT and OFFSET Clauses

With the LIMIT and OFFSET clauses, you can specify that only a portion of the results of a query is returned.

Syntax

The syntax is as follows:

[ LIMIT number ] [ OFFSET number ]

LIMIT Clause

LIMIT specifies the maximum amount of rows to return.

For example, the following query retrieves the first three records sorted by last name and first name:

SELECT Brand, City, LocationNumber
FROM Sales.Location
ORDER BY LocationNumber
LIMIT 3

OFFSET Clause

OFFSET specifies how many rows must be skipped before returning the result rows.

For example, the following query retrieves all records except the first two:

SELECT Brand, City, LocationNumber
FROM Sales.Location
ORDER BY LocationNumber
OFFSET 2

LIMIT and OFFSET can be combined in one query.

For example, the following query skips 2 records and then returns 3 records:

SELECT Brand, City, LocationNumber
FROM Sales.Location
ORDER BY LocationNumber
LIMIT 3
OFFSET 2

UNION Clause

The clause takes multiple select queries and combines their results into a single result set. The resulting set by default only includes distinct rows. The ALL keyword can be used to include all rows. Rows are considered distinct if the combination of the column values is distinct from all other rows. Comparison logic of values is the same as the DISTINCT keyword of a SELECT clause.

All select queries must define the same number of columns in the same order and their data types should be compatible. The resulting column names will be those used in the very first SELECT clause.

Syntax

The syntax is as follows:

  select_query
    { 
       UNION [ALL] select_query
    } [ ,...n ]
    [ order_by_clause ]
    [ LIMIT number ]
    [ OFFSET number ]

Result data type

The data types used in select_query statements are considered when determining the final return type of the UNION clause. All data types used in select_query statements must be compatible. All data types are compatible with themselves. Differing types are only compatible in these cases:

• UNION of numeric types INTEGER, LONG and DECIMAL. The return type is the numeric type found in the select_query statements with the highest precedence (see Type Precedence for more information).
• UNION of limited and unlimited STRING. The return type is determined by the largest length of STRING attributes in the select_query statements.
• UNION of any known type and a NULL literal. The result type is the known type.

Examples

The following query combines sales person and customer names into a single table:

SELECT FirstName, LastName
FROM Sales.SalesPerson
UNION
SELECT FirstName, LastName
FROM Sales.Customer

Some names are duplicated across the tables, so only some entries are included in the result. The query below uses UNION ALL to include all names the result:

SELECT FirstName, LastName
FROM Sales.SalesPerson
UNION ALL
SELECT FirstName, LastName
FROM Sales.Customer

The following query performs a self union of a table, returning fewer rows than the original table, as only distinct rows are included in the result.

SELECT FirstName FName, LastName LName
FROM Sales.Customer
UNION
SELECT FirstName FName, LastName LName
FROM Sales.Customer

The result of the union can also be sorted and limited, similar to a normal SELECT clause. This query retrieves the first 4 names sorted on both first and last name:

SELECT FirstName, LastName
FROM Sales.SalesPerson
UNION
SELECT FirstName, LastName
FROM Sales.Customer
ORDER BY FirstName, LastName
LIMIT 4

UNION can be chained to use more than 2 select queries as a source. This query collects all distinct first and last names into a single column in a single table:

SELECT FirstName AS Name FROM Sales.SalesPerson
UNION
SELECT FirstName AS Name FROM Sales.Customer
UNION
SELECT LastName AS Name FROM Sales.SalesPerson
UNION
SELECT LastName AS Name FROM Sales.Customer

Union of different types

Presume two entities that have columns of types INTEGER and DECIMAL:

SELECT Sale
FROM Sales.BulkSales

SELECT Sale
FROM Sales.Sales

Performing a UNION of the entities will result in a column of type DECIMAL:

SELECT Sale as CombinedSale FROM Sales.BulkSales
UNION
SELECT Sale FROM Sales.Sales

Union of associations

Performing a UNION with columns that are associations is possible, given the columns refer to the same entity for all select clauses.

Presume an entity that has one-to-many association to Sales.Customer:

SELECT *
FROM Sales.ExtraInfo

The query below combines associations to the Sales.Customer entity from 2 different source entities with duplicates:

SELECT Cust.LastName as CustomerName FROM (
    SELECT Sales.Request/Sales.Request_Customer as RequestAssoc
    FROM Sales.Request
    UNION ALL
    SELECT Sales.ExtraInfo/Sales.ExtraInfo_Customer as RequestAssoc
    FROM Sales.ExtraInfo
) AS Cust

Subqueries

A subquery is an OQL query nested inside another query. A subquery can contain the same clauses as a regular OQL query. The entities from the outer query can be referred to in a subquery. A subquery can be used in different parts of the query.

Subquery in SELECT

A subquery can be used as a column in the SELECT clause. It can refer to other tables and expressions in FROM.

SELECT
	Req/Number AS RequestNumber,
	(
		SELECT COUNT(*)
		FROM Sales.Customer AS Cust
		WHERE Cust/LastName = Req/CustomerName
	) AS CustomerCount
FROM Sales.Request Req

Subquery in FROM

It is possible to use a subquery in FROM. For example:

SELECT Cust/LastName
FROM (
		SELECT *
		FROM Sales.Customer
	) AS Cust

Subqueries in FROM can be combined with other tables:

SELECT Cust/LastName, Req/Number
FROM
	Sales.Request AS Req,
	(
		SELECT *
		FROM Sales.Customer
	) AS Cust
WHERE
	Req.CustomerName = Cust.LastName

It is possible to refer to other tables in the outer FROM clause from a subquery:

SELECT Cust/LastName, Req/MaxNumber
FROM
	Sales.Customer AS Cust,
	(
		SELECT MAX(Number) as MaxNumber
		FROM Sales.Request
		WHERE Req.CustomerName = Cust.LastName
	) AS Req

JOIN is also supported:

SELECT Cust/LastName, Req/Number
FROM
	Sales.Request Req
	LEFT JOIN
	(
		SELECT *
		FROM Sales.Customer
	) AS Cust
	ON Req.CustomerName = Cust.LastName

Subquery in WHERE

A subquery can be used in the WHERE clause. There are multiple ways to use subqueries

Subquery as a Value

An outcome of a subquery can be used as a value to be compared to another value or expression. In this case, the subquery should always return exactly one column and at most one row.

For example:

SELECT Brand, City
FROM Sales.Location AS Location
WHERE
	Location.Stock = (
		SELECT MAX(Stock)
		FROM Sales.Location AS MaxStockLocation
		WHERE Location.City = MaxStockLocation.City
	)

Subquery with IN

A subquery can be combined with the IN keyword. In that case, the expression is true if a value in the outer query matches one of the results of the subquery. In this case, the subquery can return any number of rows, but it should always return exactly one column.

SELECT FirstName, LastName
FROM Sales.Customer Cust
WHERE
	Cust/LastName IN (
		SELECT CustomerName
		FROM Sales.Request Req
	)

Subquery with EXISTS

A subquery can be combined with the EXISTS keyword. In that case, the expression is true if the subquery returns at least one row. In case of EXISTS, the subquery can return any number of rows and any number of columns.

SELECT FirstName, LastName
FROM Sales.Customer Cust
WHERE
	EXISTS (
		SELECT * FROM Sales.Request Req
		WHERE Req/CustomerName = Cust/LastName
	)

Subquery in HAVING

Subqueries can be used in a HAVING clause the in same way they are used in WHERE: as a value or combined with IN or EXISTS clauses. For other cases, the same limitations apply to the subquery outcome.

Example:

SELECT COUNT(*) AS LocationCount, SUM(Stock) as CityStock, City AS City
FROM Sales.Location AS Location
GROUP BY City
HAVING
	SUM(Stock) <= (
		SELECT COUNT(*)
		FROM Sales.Location
	)

Example of EXISTS:

SELECT COUNT(*) AS LocationCount, City AS City
FROM Sales.Location AS Location
GROUP BY City
HAVING
	EXISTS (
		SELECT *
		FROM Sales.Location AS SubLocation
		WHERE
			Location/City = SubLocation/City
			AND SubLocation/Brand = 'Rekall'
	)

The same result can be achieved with IN:

SELECT COUNT(*) AS LocationCount, City AS City
FROM Sales.Location AS Location
GROUP BY City
HAVING
	Location/City IN (
		SELECT SubLocation/City
		FROM Sales.Location AS SubLocation
		WHERE SubLocation/Brand = 'Rekall'
	)