Inline Subqueries

Complex queries may require combination of separate nested subqueries, built and saved as independent queries. However, there are many situation where we transparently generate simple inline subqueries within your current query. These are single-field independent (SELECT field FROM table) subqueries.

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:

(a) First approach uses [NOT] 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):

Generated SQL query in MySQL, with inline subquery in the WHERE clause:

SELECT dept_.`DEPTNO` AS `DEPTNO`, dept_.`DNAME` AS `DNAME`, dept_.`LOC` AS `LOC`
FROM `employeesqx`.`dept` AS dept_
WHERE dept_.`DEPTNO` IN (SELECT `DEPTNO` FROM `employeesqx`.`emp`)

(b) 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. [NOT] 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:

MySQL SQL query, using no subquery:

SELECT dept_.`DEPTNO` AS `DEPTNO`, dept_.`DNAME` AS `DNAME`, dept_.`LOC` AS `LOC`
FROM `employeesqx`.`dept` AS dept_
WHERE dept_.`DEPTNO` IN (10, 20, 30)

All/Some/Any Aggregates

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).

Generated SQL query in MySQL, using ANY:

SELECT dept_.`DEPTNO` AS `DEPTNO`, dept_.`DNAME` AS `DNAME`, dept_.`LOC` AS `LOC`
FROM `employeesqx`.`dept` AS dept_
WHERE dept_.`DEPTNO` = ANY (SELECT `DEPTNO` FROM `employeesqx`.`emp`)

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:

Generated SQL query in MySQL, using ALL:

SELECT dept_.`DEPTNO` AS `DEPTNO`, dept_.`DNAME` AS `DNAME`, dept_.`LOC` AS `LOC`
FROM `employeesqx`.`dept` AS dept_
WHERE dept_.`DEPTNO` >= ALL (SELECT `DEPTNO` FROM `employeesqx`.`emp`)

SOME is synonym of ANY. Read why we recommend it instead, by turning on the Use Alternative Operators query option.

Common Inline Aggregates

All or Any may be replaced with common 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:

Generated SQL query in MySQL, with aggregate inline subquery:

SELECT dept_.`DEPTNO` AS `DEPTNO`, dept_.`DNAME` AS `DNAME`, dept_.`LOC` AS `LOC`
FROM `employeesqx`.`dept` AS dept_
WHERE dept_.`DEPTNO` >= (SELECT MAX(`DEPTNO`) FROM `employeesqx`.`emp`)

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:

Alternative generated SQL query in MySQL, referencing another query column. You may also hide the max of DEPTNO column from the SELECT clause, and get the exact same syntax as before.

SELECT dept_.`DEPTNO` AS `DEPTNO`, dept_.`DNAME` AS `DNAME`, dept_.`LOC` AS `LOC`,
   (SELECT MAX(`DEPTNO`) FROM `employeesqx`.`emp`) AS `max of DEPTNO`
FROM `employeesqx`.`dept` AS dept_
WHERE dept_.`DEPTNO` >= (SELECT MAX(`DEPTNO`) FROM `employeesqx`.`emp`)

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.