Cart

Nesting Queries

Table of Contents

Nested Subqueries

This is a summary of the numerous layout options for a generated SQL query. You can design the query with our visual SQL query builder, then use toolbar buttons for different options on how your generated SQL query should look.

Reuse of a query by inserting it into another query makes it a nested subquery. You don’t have access to the inner structure of a subquery from an outer query. You see it and treat it like a view. Outer queries are obviously very sensitive to changes of their nested subqueries, so limit this as much as you can.

SQL definition of a query using nested subqueries may look like SELECT … FROM table1, … (SELECT …) AS subquery1, ….

Most demo queries in this article will be generated in MySQL 8 and will use the following Fruits dynamic query as nested subquery:

(SELECT	1 AS `id`, 'Apples' AS `name`, 2.0 AS `unit_price`, 22 AS `quantity` FROM dual)
UNION ALL (SELECT 2, 'Oranges', 1.9, 5 FROM dual)
UNION ALL (SELECT 4, 'Lemons', 0.88, 8 FROM dual)
UNION ALL (SELECT 5, 'Grapes', 1.22, 0 FROM dual)
ORDER BY 1

Fruits Subquery

CTEs (Common Table Expressions)

CTEs (original name from SQL) are not supported by any platform, and consist in isolating the definition of each nested subquery into an element of a WITH list. Their generated SQL looks like WITH subquery1 AS (SELECT …), subquery2 AS (SELECT …), … SELECT … . The last SELECT is your current query definition, which may use tables, views and/or subqueries identified by names in this list.

Below is a query with CTE in MySQL 8. Until version 8, MySQL didn’t support this feature. To see the query with CTE, you must have the Use WITH with Subqueries option turned on. An empty line separates the last current query from the CTE definitions.

WITH fruits_ AS (
(SELECT	1 AS `id`, 'Apples' AS `name`, 2.0 AS `unit_price`, 22 AS `quantity` FROM dual)
UNION ALL (SELECT 2, 'Oranges', 1.9, 5 FROM dual)
UNION ALL (SELECT 4, 'Lemons', 0.88, 8 FROM dual)
UNION ALL (SELECT 5, 'Grapes', 1.22, 0 FROM dual)
ORDER BY 1)

SELECT fruits_.`name` AS `name`, SUM(fruits_.`quantity`) AS `quantity`
FROM fruits_
WHERE fruits_.`quantity` > 5
GROUP BY 1

Fruits Query

Force Nested Subqueries

When CTE is supported by your platform and your option to use CTEs when available is on, we generate by default the query definition with a CTE notation, as before. But you can always switch to see a query definition as nested. Instantly switching between SQL definitions with CTEs and nested subqueries is a powerful feature, that offers multiple views on the same query.

Here is the same MySQL query displayed with no CTEs. The nested query definition usually appears embedded within the FROM clause. As you see, the overall SQL query syntax is harder to read.

SELECT	fruits_.`name` AS `name`, SUM(fruits_.`quantity`) AS `quantity`
FROM (
(SELECT	1 AS `id`, 'Apples' AS `name`, 2.0 AS `unit_price`, 22 AS `quantity` FROM dual)
UNION ALL (SELECT 2, 'Oranges', 1.9, 5 FROM dual)
UNION ALL (SELECT 4, 'Lemons', 0.88, 8 FROM dual)
UNION ALL (SELECT 5, 'Grapes', 1.22, 0 FROM dual)
ORDER BY 1) AS fruits_
WHERE fruits_.`quantity` > 5
GROUP BY 1

Hide SQL for Subqueries

This query option generates a SQL query definition with nested subqueries identified by their names only, just like tables or views, with no SQL definitions at all. Obviously, you cannot execute a query with such simplified definition, but it is sometimes worth simplifying details and getting a better look only at what your current query does. Your query definition will be a much simpler SELECT … FROM table1, … subquery1, ….

Hide SQL for Subqueries option generates a temporary SQL syntax, as the switch is automatically turned back off, and the next SQL query button will regenerate a correct SQL syntax.

The MySQL generated query below replaces the whole subquery definition with its name, Fruits:

SELECT fruits_.`name` AS `name`, SUM(fruits_.`quantity`) AS `quantity`
FROM `Fruits` AS fruits_
WHERE fruits_.`quantity` > 5
GROUP BY 1

Simulate for a Different Platform

Another powerful feature allows you to instantly see what your SQL query definition would be for another database type, without having to install a database of that type. There are many differences in syntax, features, notation and behavior between the database platforms, and – if you plan a migration or are just curious to learn quickly about another database type – you’re one click away with our product.

Of course, this generated query definition will fail most often if you try to run it against your own database, but you never know. We allow you to do it, to see if it works. Or at least to get a specific error message and see what’s different. This query definition will show a warning in SQL mode.

Here is the previous MySQL query simulated for Microsoft SQL Server, with one click and without having SQL Server installed at all. We can guarantee that’s a correct Transact-SQL syntax, for SQL Server. But your query will likely fail if you try to run it on the MySQL database server.

SELECT fruits_.[name] AS [name], SUM(fruits_.[quantity]) AS [quantity]
FROM (
SELECT TOP 2147483647 1 AS [id], 'Apples' AS [name], 2.0 AS [unit_price], 22 AS [quantity]
UNION ALL SELECT TOP 2147483647 2, 'Oranges', 1.9, 5
UNION ALL SELECT TOP 2147483647 4, 'Lemons', 0.88, 8
UNION ALL SELECT TOP 2147483647 5, 'Grapes', 1.22, 0
ORDER BY 1) AS fruits_
WHERE fruits_.[quantity] > 5
GROUP BY fruits_.[name]

Among the few notable SQL syntax differences in this query alone:

  • SQL Server column headers and identifiers are rather surrounded by […]
  • SELECT TOP clauses for the subquery are our fixes, as SQL SERVER subqueries with ORDER BY would otherwise fail.
  • You don’t need parentheses for the SELECTs joined by UNION, as you do in MySQL.

Other Layout Options

Many other settings allow you a different perspective on a SQL query definition:

  1. Inline Subqueries – instant generation of small independent subqueries like (SELECT field FROM table) in the current query, used with IN, ALL/ANY or an aggregate function. That’s one powerful way to get a query within another query without creating them both separately.
  2. Generate Query Parameters – when on, the SQL query definition packs all constant values into internally generated query parameters. When you run such a query, you avoid any possible SQL injection. You may automatically generate internal query parameters in function calls, lists, and/or UNION/VALUES lists.
  3. Use Alternative Operators – different SQL operators or keywords may be used instead on the query generation. When pagination is used, Use Alternative Paging may render as well a different query (enabled for Firebird and SQL Server). Replace CASE with IF/DECODE can replace conditional CASE expressions with alternative IF, IIF or DECODE functions, when supported.
  4. Order/Group by position/alias – as positional numbers, column aliases or full expressions. By default, we try positional numbers if supported, then column aliases if supported.
  5. Lower Case SQL Keywords/Names – keywords and/or data types or function names can be displayed in lower case. Two-Column Layout SQL is also a default query option.
  6. Fix Math/NULL Operations – these options – as well as the Fix Sorting Issues – will allow us to automatically add code to try and fix known issues caused by either NULL values or in arithmetic operations.
  7. Max Level of Nested Queries – limits the imbrication of nested subqueries (by default it may go down to 5 levels). Each query has a property with its current Nesting Level, as well as the total number of subqueries. This option becomes visible as a toolbar dropdown button once you use at least one subquery.

Here is the same SQL query with CTEs, but with keywords and names all lower case. As the SQL language is case-insensitive, it’s just a choice if you want to use part of the syntax with lower or upper cases. Most DBAs and software developers who type SQL queries by hand enter them usually in lower case. But books and publications frequently use a polished upper case syntax.

with fruits_ as (
(select	1 as `id`, 'Apples' as `name`, 2.0 as `unit_price`, 22 as `quantity` from dual)
union all (select 2, 'Oranges', 1.9, 5 from dual)
union all (select 4, 'Lemons', 0.88, 8 from dual)
union all (select 5, 'Grapes', 1.22, 0 from dual)
order by 1)

select fruits_.`name` as `name`, sum(fruits_.`quantity`) as `quantity`
from fruits_
where fruits_.`quantity` > 5
group by 1