Using the SELECT statement in 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 for the description using the SELECT statement in the WHERE clause? Is this a 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 much less elegant, but it seems to be faster than my previous version. I don’t like it because it doesn’t appear very clearly in the graphical interface (and it should be understood by beginners in SQL). I could break it into two separate requests, but then everything starts to get clogged ...

NB I need more than just a date and rating (e.g. name)

Answer 1

It is called a correlated subquery. He uses it.

Answer 2

This is a good practice. Usually they are called SUBQUERY , SUBSELECT, or CORRECTED QUERY .

This is a relatively expensive operation, but quite often there are many subqueries when working with databases, since this is the only way to perform certain operations with data.

Answer 3

There is a much better way to achieve the desired result using SQL Server analytic (or window) functions .

 SELECT DISTINCT Date, MAX(Score) OVER(PARTITION BY Date) FROM ScoresTable 

If you need more than a combination with a date and a maximum score, you can use ranking functions, for example:

 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 can use RANK () instead of ROW_NUMBER () if you want to return multiple records if they both have the same MAX (Score).

Answer 4

The principle of subqueries is not bad at all, but I don’t think 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 GROUP BY.

Answer 5

This is a correlated subquery.

(This is a "nested" query - this is a very non-technical term, though)

The inner query takes values ​​from the outer query (WHERE st.Date = ScoresTable.Date), so it is evaluated once for each row in the outer query.

There is also an uncorrelated form in which the internal query is independent, as it is executed only once.

eg.

  SELECT * FROM ScoresTable WHERE Score = (SELECT MAX(Score) FROM Scores) 

There is nothing wrong with using subqueries, except when they are not needed :)

Your expression may be overwritten as an aggregate function, depending on which columns you require in the select statement.

 SELECT Max(score), Date FROM ScoresTable Group By Date 

Answer 6

In your case, the scenario is why not use the GROUP BY and HAVING clauses instead of the JOINING table for yourself. You can also use another useful feature. see this link

Answer 7

A subquery is a name.

This is required from time to time, but good / bad depends on how it is applied.