CODEWITHSUNDEEP
sqlsql-servert-sql
Mou
Mou

Reputation: 16282

How to achieve this with join instead of using a subquery?

How could I change this query bellow to use join instead of a subquery?

SELECT ID, Name, UnitPrice
FROM Products
WHERE UnitPrice <
  ( SELECT AVG( UnitPrice ) FROM Products )
ORDER BY UnitPrice DESC;

Thanks

Upvotes: 1

Views: 57

Answers (3)

John Cappelletti
John Cappelletti

Reputation: 81930

Another option is to use the window functions

;with cte as (
    SELECT ID 
         , Name
         , UnitPrice
         , AvgPrice = avg(UnitPrice) over (Order By (Select NULL))
     FROM Products
)
Select * from cte where UnitPrice<AvgPrice

Upvotes: 2

Hadi
Hadi

Reputation: 37313

There is a way to do it with join as the following:

SELECT T1.* 
FROM [dbo].[Table_1] AS T1 
INNER JOIN ( SELECT AVG( Number ) as Number FROM [Table_1] ) AS T2 
ON T1.Number < T2.Number 
ORDER BY T1.UnitPrice DESC;

Otherwise use variable to store this value

Upvotes: 1

Deadsheep39
Deadsheep39

Reputation: 611

You can do it with joins on

  • With CTE.
  • With derivated table.
  • With assistant view.
  • With assistant temp table.
  • With join on grouped subquery.

or can use:

  • With variable.
  • With table function.
  • With cross apply.

But your qry is the clearest solution.

Upvotes: 0

Related Questions