How to reduce subqueries in a SQL View-CodePudding

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

CodePudding user response：

Subquerying in the SELECT clause has its uses, but it's generally an exception to solve a problem. Instead the proper way to do what you are doing is to join your two tables properly in your FROM clause:

SELECT people.field1, people.field2, people.field3, people.field4
FROM peopleGroup g
    LEFT OUTER JOIN people
         ON g.company = people.company

CodePudding user response：

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