Using a SELECT statement within a WHERE clause

Question

SELECT   FROM ScoresTable WHERE Score       SELECT MAX Score  FROM ScoresTable AS st WHERE st Date   ScoresTable Date    Is there a name to describe using a SELECT statement within a WHERE clause  Is this good bad practice   Would this be a better alternative   SELECT ScoresTable    FROM ScoresTable INNER JOIN     SELECT Date  MAX Score  AS MaxScore    FROM ScoresTable GROUP BY Date  SubQuery    ON ScoresTable Date   SubQuery Date    AND ScoresTable Score   SubQuery MaxScore   It is far less elegant  but appears to run more quickly than my previous version  I dislike it because it is not displayed very clearly in the GUI  and it needs to be understood by SQL beginners   I could split it into two separate queries  but then things begin to get cluttered     N B  I need more than just Date and Score  e g  name

User · Accepted Answer

It s called correlated subquery  It has it s uses

User · Answer

The principle of subqueries is not at all bad  but I don t think that you should use it in your example  If I understand correctly you want to get the maximum score for each date  In this case you should use a GROUP BY

User · Answer

In your case scenario  Why not use GROUP BY and HAVING clause instead of JOINING table to itself  You may also use other useful function  see this link

User · Answer

It s not bad practice at all  They are usually referred as SUBQUERY  SUBSELECT or NESTED QUERY   It s a relatively expensive operation  but it s quite common to encounter a lot of subqueries when dealing with databases since it s the only way to perform certain kind of operations on data

User · Answer

There s a much better way to achieve your desired result  using SQL Server s analytic  or windowing  functions   SELECT DISTINCT Date  MAX Score  OVER PARTITION BY Date  FROM ScoresTable   If you need more than just the date and max score combinations  you can use ranking functions  eg   SELECT    FROM    ScoresTable t JOIN          SELECT          ScoreId          ROW NUMBER   OVER  PARTITION BY Date ORDER BY Score DESC  AS  Rank           FROM ScoresTable   window ON window ScoreId   p ScoreId AND window  Rank    1   You may want to use RANK   instead of ROW NUMBER   if you want multiple records to be returned if they both share the same MAX Score

User · Answer

This is a correlated sub-query     It is a  nested  query - this is very non-technical term though   The inner query takes values from the outer-query  WHERE st Date   ScoresTable Date  thus it is evaluated once for each row in the outer query   There is also a non-correlated form in which the inner query is independent as as such is only executed once   e g    SELECT   FROM ScoresTable WHERE Score        SELECT MAX Score  FROM Scores    There is nothing wrong with using subqueries  except where they are not needed     Your statement may be rewritable as an aggregate function depending on what columns you require in your select statement   SELECT Max score   Date FROM ScoresTable  Group By Date

User · Answer

Subquery is the name   At times it s required  but good bad depends on how it s applied

[sql] Using a SELECT statement within a WHERE clause

Examples related to sql

Examples related to select

Examples related to where-clause