Complex queries may require combination of separate nested subqueries, built and saved as independent queries. However, there are may situation where we transparently generate simple inline subqueries within your current query. These are single-field independent (SELECT field FROM table) subqueries.
[Not] In Field/List Values
They translate into four different conditional operators you can select from builder’s contextual menu, for a Field or in Where/Having conditions. Consider a query to list all departments with employees. Each dept.DEPTNO value must have been used in emp.DEPTNO as well. There are two ways to approach this:
First approach uses In Field Values and translates into WHERE dept.DEPTNO IN (SELECT DEPTNO FROM emp), a filter with IN operator and a simple inline subquery (remark how you must bring the emp table into the diagram area just to reference its DEPTNO field in our condition):
A second approach may assume you already know what limited set of DEPTNO values is used by emp, and you want to pass those values instead. In List Values makes visible the Edit Elements menu item, and you can enter 10, 20, 30 as list elements in a popup. The query translates now into the similar filter WHERE dept.DEPTNO IN (10, 20, 30), and you no longer need the emp table at all:
Back to your first query, change the In Field Values by the Equals (=) operator. You get the WHERE dept.DEPTNO = (SELECT DEPTNO FROM emp) inline subquery, which expression may be confusing for some platforms and throw an error. More, if you used at least one field from emp as a select field, you may not get a subquery at all, but the simple equivalent of an equi-join as WHERE dept.DEPTNO = emp.DEPTNO. It’s also not very clear if you want to compare your left-side value to ALL values from emp, or just to SOME or ANY value that provides a good match.
To avoid all this confusion and always get an inline subquery, provide an additional inline aggregate function you’ll find now in the contextual menu, which is Any value from. This tells us: compare dept.DEPTNO with values from emp until you find a good match (if any). The filter translates into WHERE dept.DEPTNO = ANY (SELECT DEPTNO FROM emp) and this returns the same result as our first query (to exclude ALL values found in the emp table, use the opposite <> ALL emp.DEPTNO condition).
You can use All and Any in a similar way with other comparison operators. For instance, the query below returns department 30 because no value from emp is lower than this (remember dept contains 10, 20, 30, 40, and 40 is the only value not used in emp) and department 40 because it is obviously greater than ALL 10, 20 and 30 values from emp:
SOME is synonym of ANY. Read why we recommend it instead and why it’s good to leave the Use SOME switch on, as it is by default.
Other Inline Aggregates
All or Any may be replaced with regular inline aggregates such as Count, Sum, Average, List, Minimum or Maximum, followed by the optional Distinct clause. In the previous query, replace the >= ALL operator by >= MAX, and coincidentally you will get the same result, with the filter translated into WHERE dept.DEPTNO >= (SELECT MAX(DEPTNO) FROM emp). This is because 30 is the maximum value of DEPTNO from emp, 30 and 40 being the dept.DEPTNO values properly comparing to them:
Unlike All and Any – which may be applied only to a comparison operator – regular inline aggregates may also be applied to individual fields, in the Field builder.
Check the DEPTNO field from the emp shape, to append it at the end of your result. With nothing else done, this will translate into a cross join with Cartesian Product between the two tables, because they are not linked by any join. However, if you select the aggregate function Maximum from the contextual menu, you will create a completely different effect. An inline independent subquery will be generated to get the max value of DEPTNO from emp, in the form (SELECT MAX(DEPTNO) FROM emp), which will be repeated for every dept row. You may use this value in other parts of your query. Let’s compare it with the dept.DEPTNO value (using it as a query expression!), to get a similar result as the previous query:
To conclude, inline subqueries help you generate on-the-fly some simple subqueries with no need to build and reuse separate independent queries as nested subqueries.