Subquery

A subquery is a query that is nested inside a SELECT statement. Nesting queries allows you to perform multi-step operations.

A subquery follows this syntax.

1SELECT field
2FROM ( SELECT_STATEMENT || UNION_ALL )

To pass fields to a math or string function in a subquery, include it in the outer query’s WHERE clause. If you use it in the innermost query, SQL throws an error.

This query returns the sum of all profits and the max profit value from the Superstore dataset where all the profits are greater than zero. Values greater than zero mean that no losses are reflected in the results.

1SELECT d.sumProfit, maxProfit
2FROM (
3   SELECT SUM(Profit) as sumProfit, MAX(Profit) as maxProfit
4   FROM (
5       SELECT Profit
6       FROM "Superstore"
7       WHERE Profit > 0
8       )
9   ) as d;

CRM Analytics first runs the innermost query: SELECT Profit FROM "Superstore" WHERE Profit > 0.

The output from this query becomes the dataset on which the outer query operates.

1SELECT SUM(Profit) as sumProfit, MAX(Profit) as maxProfit
2FROM (INNERMOST QUERY);

Optionally, you can refer to an inner query by its alias, if you choose to use one. In the outermost query, you can refer to sumProfit as d.sumProfit, as shown in the example, or you can refer to it simply as sumProfit.

1SELECT d.sumProfit, maxProfit FROM (NESTED SUBQUERIES as d);

The UNION ALL operator combines the results of two datasets into one. It doesn’t remove any duplicate rows. This example includes UNION ALL after a subquery.

The first part of this example, up to the UNION ALL statement, returns the total profit and the maximum profit values from the Superstore dataset. After the UNION ALL statement, the query returns the total and maximum profit values from the ClearanceSales dataset. Note that ClearanceSales is intended for example purposes only.

1SELECT d.sumProfit as total, maxProfit as max_profit
2   FROM (
3       SELECT SUM(Profit) as sumProfit, MAX(Profit) as maxProfit
4       FROM (
5           SELECT Profit
6           FROM "Superstore"
7           WHERE Profit > 0
8           )
9       ) as d
10UNION ALL (
11   SELECT SUM("Profit") as total, MAX("Profit") as max_profit
12   FROM "ClearanceSales"
13   GROUP BY ()
14)

You can also include UNION ALL within a subquery. This example finds all the names in the Superstore and Opportunity datasets that contain “an.” The outer query then filters for names that have counts over 30 and returns that subset of results.

1SELECT Customer_Name as "name", "count"
2FROM (
3   (SELECT Customer_Name, count(*) as "count"
4   FROM "Superstore"
5   WHERE Customer_Name LIKE '%an%'
6   GROUP BY Customer_Name
7) UNION ALL (
8   SELECT User_Name as "name", count(*) as "count" FROM "Opportunity" WHERE User_Name LIKE '%an%' GROUP BY User_Name
9   )
10) WHERE "count" > 30

j

Introducing SQL for CRM Analytics

Quick Start: Run Your First Query

SQL vs SAQL

SQL Statements

SELECT Clause

FROM Clause

WHERE Clause

Boolean Expressions in SELECT Clause

GROUP BY Clause

GROUP BY ROLLUP

GROUPING()

HAVING Clause

ORDER BY Clause

LIKE Clause

BETWEEN Operator

LIMIT Clause

FETCH Clause

OFFSET Clause

CASE Statements

COALESCE()

NULLIF()

UNION Operator

Subquery

Window Functions

Date Functions

Aggregate Functions

Math Functions

String Functions and Operators

Release Notes

Previous Versions