Using a subquery

A subquery is a querying technique designed for more complex business questions that require us to analyze them further and additional information based on the data retrieved.

The subquery technique is used to return data that will be used by the main query. For example, we want to generate a better analysis for the sales team and understand which types of cars were bought by the same client.

The goal of this business question is to investigate whether a specific combination of car types are being bought by clients: did clients who bought Austin Martini cars buy other types of cars as well?

By analyzing this data, we will be able to make better marketing and sales offers to the customers as well as get some basic information about sales trends and customer behavior.

Getting ready

We want to analyze the client's car sales combinations; we will drag the Client Name, Maker, and Sales Revenue objects.

Here comes the catch. Creating a query filter based on the model object with the value Austin Martini can't solve this business question and nor can using an in list operator that will enable us to choose several values.

Using any one of these methods, we will only get half the answer: which client bought a specific model or several ones. However, if we necessarily need to retrieve all the clients who purchased Austin Martini cars and based on this population check which other car types they purchased as well, then that kind of business question will have to be split into two queries.

How to do it...

We will use the Add a subquery button located on the right-hand side of the Query Filters pane.

Since the focus of the query is finding clients who bought cars, the client object will be marked in the result objects. A subquery condition will be created by clicking on the Add a sub query button, as shown in the following screenshot:

The operator that will be used in this query will be the In list operator since there is a very high probability to find more than one customer that have bought cars made by several car makers.

We will add another maker filter to the subquery area and choose the value Austin Martini, as shown in the following screenshot:

We will now click on the Run query button and get the following results:

Note that several customers returned two rows.

The Austin Martini rows were retrieved by the subquery that was used by the main query to find which other makers' cars were bought by the client.

How it works...

A subquery is a query that serves only one purpose: feeding the main query with its results for further lookup.

A subquery has no meaning in a standalone query; it's a part of the main query.

In this example, the subquery retrieved a list of all the clients who bought a car made by Austin Martini. This list was the base for the main query to run and fetch the rest of the car makers' cars that were bought by those clients retrieved in the subquery.

Using a subquery does not ensure that all the clients owning cars from more than one maker will be fetched. If any one of the Austin Martini clients are found with other makers, those rows will be pulled too; if not, a single row will be presented for the client, as we can see in the query results.

In terms of dependencies, the subquery runs first and fetches the client list, then the main query uses that client list as its base for fetching data.

There's more...

Subqueries can use operators such as =, <, >, >=, <=, In list, and Between. Undoubtedly, the In list operator is one of the most useful ones as it deals with classic business questions such as:

• Which customers bought product x and bought other products as well?
• Which customers that weren't billed in the last month were not billed in the current month either?

As a basic rule, the subquery can only have one result object. This rule further emphasizes that the subquery is just a list that is retrieved and used by the main query.

The subquery can also be used to eliminate certain types of rows by using the operator Not in list; for example, to eliminate the customers who bought a specific product.

See also

• To know about more advanced query techniques, go through the next recipe, Using combined queries, and the Using other query results recipe