Building a SubSelect expression

Your query can return values which come from another table. It is also called a SubQuery.

Building a new SubSelect

This can be done in two places:

in the Query Builder > New Column > Build SubSelect
in the SQL Window > Insert > Column SubSelect

Amending a SubSelect

If you have a SubSelect expression in an existing SQL statement, then you can amend this as folows:

import the SQL into the Query Builder
go to the Column tab > select the column expression > Mod > Build SubSelect

AQT will parse the SubSelect and display it in the SubSelect dialog for you to amend.

Example

The following query displays Sales Details, but includes the customers full name, which comes from the Customers table.

Select ORDERNO, ORDER_DATE, QUANTITY_ORDERED, CUSTOMER,

(SELECT FULLNAME FROM CUSTOMER_INFO WHERE CUSTOMER=s.CUSTOMER)

FROM SALES_DETAILS s

The clause (SELECT FULLNAME FROM CUSTOMER_INFO WHERE CUSTOMER=s.CUSTOMER) is called a SubSelect clause as it selects data from another table.

Some rules about SubSelects:

they can return only a single column
they can return only a single row

In most cases, you would "join" the SubSelect to the outer table. This is done here with the clause WHERE CUSTOMER=s.CUSTOMER. This ensures that only a single value is returned per row in the outer table. This is often called a correlated subselect (or correlated subquery) as this Where clauses correlates / matches the rows between the two tables.

query_builder_cols_subselect

Advanced Query Tool

https://www.querytool.com