sql - 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)

Answer 1

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.