'How to reduce subqueries in a SQL View

I have a query that has a number of subqueries and it is slowing it down. Many of the subqueries are the same record but different fields. I was wondering if there was a way to query that record once rather than four times.

For example:

select 
field1 as (select field1 from people where company = g.company),
field2 as (select field2 from people where company = g.company),
field3 as (select field3 from people where company = g.company),
field4 as (select field4 from people where company = g.company)
from peopleGroup g
sqlsql-servertsql


Solution 1:[1]

If you still want to subquery for whatever reason (let's say a TOP 1) then you can use an APPLY

select 
  p.field1,
  p.field2,
  p.field3,
  p.field4
from peopleGroup g
outer apply (
    select
      p.field1,
      p.field2,
      p.field3,
      p.field4
    from people p
    where p.company = g.company
) p;

OUTER APPLY simulates a LEFT JOIN and CROSS APPLY simulates a INNER JOIN

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Charlieface

Related Questions