Is Possible to Reuse Subqueries

Is possible to reuse subqueries?

You can take the aggregations out into a CTE (common table expression):

with minima as (select t.id, t.type, min(value) min_value
                from table2 t
                where t.type in (1,2,3,4)
                group by t.id, t.type)
select a.id, a.name,
       (select min_value from minima where minima.id = subquery.id and minima.type = 1) as column1,
       (select min_value from minima where minima.id = subquery.id and minima.type = 2) as column2,
       (select min_value from minima where minima.id = subquery.id and minima.type = 3) as column3,
       (select min_value from minima where minima.id = subquery.id and minima.type = 4) as column4
from (select distinct id from table2 t where t.type in (1,2,3,4) and t.value between '2010-01-01' and '2010-01-07') as subquery
     left join a on a.id = subquery.id

Whether this is actually any benefit (or even supported) or not depends on your environment and dataset, of course.

Another approach:

select xx.id, a.name, xx.column1, xx.column2, xx.column3, xx.column4
from (
      select id,
             max(case type when 1 then min_value end) as column1,
             max(case type when 2 then min_value end) as column2,
             max(case type when 3 then min_value end) as column3,
             max(case type when 4 then min_value end) as column4
      from (select t.id, t.type, min(value) min_value
            from table2 t
            where t.type in (1,2,3,4)
            group by t.id, t.type) minima
      group by id
) xx left join a on a.id = xx.id
order by 1

How to reuse a sub query in sql?

Use a Common Table Expression (CTE) if you're using SQL Server 2005+:

with cte as (
      select columns
      from result_set
      where condition_common
    )
select columns
from cte  as subset1
      join
      cte as subset2 
         on subset1.somekey = subset2.somekey
where otherconditions

How can I reuse a subquery inside a select expression?

The subquery can be reused by introducing intermediate projection (Select), which is the equivalent of let operator in the query syntax.

For instance:

dbContext.Organizations.AsNoTracking()
    // intermediate projection
    .Select(x => new
    {
        Organization = x,
        Owner = x.Members
            .Where(member => member.Role == RoleType.Owner)
            .OrderBy(member => member.CreatedAt)
            .FirstOrDefault()
    })
    // final projection
    .Select(x => new OrganizationListItem
    {
        Id = x.Organization.Id,
        Name = x.Organization.Name,
        OwnerFirstName = Owner.FirstName,
        OwnerLastName = Owner.LastName,
        OwnerEmailAddress = Owner.EmailAddress
    })

Note that in pre EF Core 3.0 you have to use FirstOrDefault instead of First if you want to avoid client evaluation.

Also this does not make the generated SQL query better/faster - it still contains separate inline subquery for each property included in the final select. Hence will improve readability, but not the efficiency.

That's why it's usually better to project nested object into unflattened DTO property, i.e. instead of OwnerFirstName, OwnerLastName, OwnerEmailAddress have a class with properties FirstName, LastName, EmailAddress and property let say Owner of that type in OrganizationListItem (similar to entity with reference navigation property). This way you will be able to use something like

dbContext.Organizations.AsNoTracking()
    .Select(x => new
    {
        Id = x.Organization.Id,
        Name = x.Organization.Name,
        Owner = x.Members
            .Where(member => member.Role == RoleType.Owner)
            .OrderBy(member => member.CreatedAt)
            .Select(member => new OwnerInfo // the new class
             {
                 FirstName = member.FirstName,
                 LastName = member.LastName,
                 EmailAddress = member.EmailAddress
             })
            .FirstOrDefault()
    })

Unfortunately in pre 3.0 versions EF Core will generate N + 1 SQL queries for this LINQ query, but in 3.0+ it will generate a single and quite efficient SQL query.

Reuse of a field from a joined table inside a subquery in FROM clause

Recall SQL's logical order of operations that differ from its lexical order (i.e., order in how it is written). Usually the first step in query processing is the FROM clause, then JOIN, ON, WHERE, GROUP BY, etc. and usually ending with ORDER BY and SELECT (ironically one of the last clauses processed though written first).

Technically, your queries do not involve correlated subqueries since there are no inner or outer levels. Specifically, the derived table t3 and base table t4 are at the same level. The query engine evaluates t3 in isolation by itself during FROM clause step. Then, it evaluates JOIN table, t4, in isolation by itself and finally applies the matching ON logic.

Because t4 is not defined in the universe of t3, MS Access via GUI prompts for that parameter value (where MS Access via ODBC will raise an error). To resolve you have to include all necessary data sources in each table scope:

SELECT t1.field1, t1.field2 - IIF(t3.calcfield IS NULL, 0, t3.calc) As Diff
FROM
  (SELECT t2.fieldid, SUM(t2.field3) AS fsum
   FROM t2
   INNER JOIN table4 sub_t4
     ON t2.fieldid = sub_t4.fieldid
   WHERE t2.date > sub_t4.date 
   GROUP BY t2.fieldid
  ) t3
LEFT JOIN table4 t4 
  ON t3.fieldid = t4.fieldid

---

Often, too, using layered queries is beneficial in Access and can help with final, compact queries:

t3 query (save below as a query object)

SELECT t2.fieldid, SUM(t2.field3) AS fsum
FROM t2
INNER JOIN table4 sub_t4
   ON t2.fieldid = sub_t4.fieldid
WHERE t2.date > sub_t4.date 
GROUP BY t2.fieldid

Final query (join saved query)

SELECT t1.field1, t1.field2 - IIF(t3.calcfield IS NULL, 0, t3.calc) As Diff
FROM my_saved_query t3
LEFT JOIN table4 t4 
   ON t3.fieldid = t4.fieldid

How to reuse a subquery

Subquery factoring (aka CTEs in other database platforms) is what you need, eg:

with dataset as (select datasetid
                 from   Reportingdatasetmembers
                 where  ReportingDatasetID = param_in_ReportingDataSetID)
select ...
from   some_table_1
where  ...
and    datasetid in (select datasetid from dataset)
union all
select ...
from   some_table_2
where  ...
and    datasetid in (select datasetid from dataset);

Is there a way to reuse subqueries in the same query?

Does WITH help? That lets you define a result set you can use multiple times in the SELECT.

From their example:

WITH orderable_items (product_id, quantity) AS
 ( SELECT stocked.product_id, stocked.quantity
   FROM stocked, product
   WHERE stocked.product_id = product.product_id
   AND   product.on_hand > 5
 )

SELECT product_id, quantity
FROM orderable_items
WHERE quantity < 10;

SQL Server reuse aliases in subqueries

I think that following script can explain you everything.

SELECT 1,1,GETDATE()
INSERT INTO @t ( Id, UserId, TranDate )
SELECT 2,1,GETDATE()
INSERT INTO @t ( Id, UserId, TranDate )
SELECT 3,1,GETDATE()

SELECT tx.Id/*main alias*/,
       tx1.Id /*First subquery alias*/,
       tx2.Id /*Second subquery alias*/,
       (SELECT Id FROM @t txs /*alias only in this one subquery/must be different from main if you want use main alias in it...*/ 
        WHERE txs.Id = tx.Id+2 /*here is used main value = subquery value+2*/) AS Id
FROM @t tx /*main*/
JOIN (SELECT * 
      FROM @t tx 
      WHERE tx.Id =  1 /*this one using subquery values + you are not able to use here main value*/ 
      ) tx1 --alias of subquery
      ON tx.Id = tx1.Id /*here is used main value = subquery value*/
CROSS APPLY (SELECT TOP 1 * 
             FROM @t txc /*This one must be different from main if you want use it to comparison with main*/
             WHERE txc.Id > tx.Id /*this one using subquery value > main value*/ 
             ) tx2 --alias of subquery
WHERE tx.Id = 1 AND /*Subquery alias canot reference on First subquery value*/
      tx1.Id = 1 AND/*Subquery alias*/
      tx2.Id = 2 /*Subquery alias*/

It means that yea, it could be reused, but only if you dont want compare main / sub, because if you reuse it and for example you try to do folowing statement in subquery tx.Id > tx.Id It causes that only values in subquery will be compared. In our example it causes that you dont get anything because you comaring values in same row...

---

Related Topics