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)

Have a look at windowing functions http://www.sqlbooks.ru/readarticle.aspx?part=02&file=sql200523 — Winston Smith, Jun 21 '11 at 11:27
you're assuming his database implements this. Also window functions are almost certainly not required here to meet Jo's needs. — BonyT, Jun 21 '11 at 11:43
Indeed they're not required for this simple example, but they offer elegant solutions to more complex variations on these types of query, so they're worth knowing about. — Winston Smith, Jun 21 '11 at 11:44
I agree - worth knowing about - still a lot to take in for someone obviously quite new to SQL. — BonyT, Jun 21 '11 at 11:46
Your EDIT is a much better solution, and I'd argue more elegant. As you've already noticed, it is also much more performant. It may look more complex, but beginners should learn to write good code from the outset instead of learning bad habits first. — Winston Smith, Jun 21 '11 at 13:38

score 8 · Answer 1 · answered Jun 21 '11 at 11:10

8

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.

answered Jun 21 '11 at 11:10

Pablo Santa Cruz

176,835
32
241
292

1

@Gunner: yes. Also, subselect. – Pablo Santa Cruz Jun 21 '11 at 11:13
You can usually factor them out into a JOIN, but SQL server is pretty good at figuring out the best query plan itself these days. Still, it doesn't hurt to write the best code you can, instead of relying on compiler optimizations. – Winston Smith Jun 21 '11 at 11:38
True. You can **usually** factor them into a join. But there are some operations that you won't be able to perform with a JOIN. For example ANY/SOME/ALL subquery operations... – Pablo Santa Cruz Jun 21 '11 at 11:40
@WinstonSmith: Can I factor my example into a JOIN (or is that what I have done in my edit?); I'm afraid my knowledge of precise terminology is pretty poor. – jofitz Jun 21 '11 at 12:59
@Pablo: From reviewing other people's comments it seems what I have is a _correlated_ subquery. I am very familiar with simple subqueries, but this is my first experience of a correlated subquery. Can you suggest a better method? – jofitz Jun 21 '11 at 13:01

score 8 · Accepted Answer · answered Jun 21 '11 at 11:11

8

It's called correlated subquery. It has it's uses.

answered Jun 21 '11 at 11:11

Mladen Prajdic

15,457
2
43
51

1

Thanks @Mladen. In your opinion, is this the best / most efficient solution to the problem? – jofitz Jun 21 '11 at 13:04
4

no it's not. the way you do it, the subquery is ran for each row in your table. it's always better to make it as a join like you did. – Mladen Prajdic Jun 21 '11 at 14:03

Winston Smith · Answer 3 · 2011-06-21T11:36:23.940

3

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

edited Jun 21 '11 at 11:36

answered Jun 21 '11 at 11:21

Winston Smith

21,585
10
60
75

Thanks @WinstonSmith, but your suggestion appears to be more complicated than necessary. Does this offer more than my initial suggestions? – jofitz Jun 21 '11 at 13:03
It won't work on access anyway, but it's something to keep in mind if you move on to SQL server at some point. – Winston Smith Jun 21 '11 at 13:38

score 2 · Answer 4 · answered Jun 21 '11 at 11:12

2

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.

answered Jun 21 '11 at 11:12

user254875486

11,190
7
36
65

Since he's selecting *, it looks like he wants more than just the score and date combinations. Maybe he needs to figure out which students got those scores. – Winston Smith Jun 21 '11 at 11:41
You're right. Luckily, in MySQL (though I'm not sure he's using that) he will be able to select columns that are not aggregated or in the group by clause. – user254875486 Jun 21 '11 at 11:52
You are exactly right, @WinstonSmith, I need to get the name associated with the scores so a simple GROUP BY is not enough. Unfortunately, I'm not using MySQL on this occasion, @Lex; I'm ashamed to say I'm using Access :) – jofitz Jun 21 '11 at 12:37

BonyT · Answer 5 · 2011-06-21T11:49:25.487

2

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

edited Jun 21 '11 at 11:49

answered Jun 21 '11 at 11:15

BonyT

10,750
5
31
52

Aggregate function won't work if you need more than just the MAX(Score) and Date pairs. – Winston Smith Jun 21 '11 at 11:37
You're assuming he needs more - it is better practise to only code to meet requirements rather than introducing unecessary complexity. – BonyT Jun 21 '11 at 11:40
I'm not assuming - it's obvious from the query which begins `SELECT *`. Of course, that could just be lazyness, but that would be an assumption too! – Winston Smith Jun 21 '11 at 11:43
Now that I'm all together a happier fellow, I've removed my downvote! – Winston Smith Jun 21 '11 at 11:52
As commented elsewhere, @WinstonSmith is right, I need more than just the score and date. – jofitz Jun 21 '11 at 12:40

score 1 · Answer 6 · answered Mar 02 '16 at 03:42

1

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

answered Mar 02 '16 at 03:42

dr.Crow

1,493
14
17

GROUP BY and HAVING will not work in this case because, as stated in the original question: "I need more than just Date and Score (e.g. name)". If you check again you will find that aliases are already used in the question. – jofitz Apr 22 '16 at 16:36

score 0 · Answer 7 · answered Jun 21 '11 at 11:11

0

Subquery is the name.

At times it's required, but good/bad depends on how it's applied.

answered Jun 21 '11 at 11:11

Shamim Hafiz - MSFT

21,454
43
116
176

Using a SELECT statement within a WHERE clause

7 Answers7

Linked