Select values that have different rows matching different conditions?

Question

This is a canonical question on how to perform a relational-division using a RDBMS as a result of this discussion on Meta. If you need to add more informations, please do not post new answer, but instead edit the community wiki answer.

Consider this table:

| student | course  |
+---------+---------+
|  Adam   | Math    |
|  Adam   | Science |
|  John   | Math    |
|  John   | Science |
|  Jane   | Math    |
|  Jane   | Physics |

I want to pull all students who are taking both math and science. This means Adam and John should be returned.

@BrianDeMilia for this question we're leaving that part open, and will update the community wiki answer as appropriate. See the linked Meta post for more information. — AdamMc331, Dec 23 '14 at 22:06

score 2 · Answer 1 · answered Dec 23 '14 at 21:52

2

Group by the student and take only those having both courses

select student
from your_table
where course in ('math','science')
group by student
having count(distinct course) = 2

answered Dec 23 '14 at 21:52

juergen d

201,996
37
293
362

This is a great solution, one I've never even thought of. Do you mind adding (or if I add) it to the community wiki answer? – AdamMc331 Dec 23 '14 at 22:04
In fact I answered this very question in at least a hundred variations and this is not really new. :) – juergen d Dec 23 '14 at 22:06
I know it's not. Did you take a look at the meta post? – AdamMc331 Dec 23 '14 at 22:07
I did a quick read just now. I will look for a highly upvoted question like this one. There must be at least one. – juergen d Dec 23 '14 at 22:08
I don't know how you caught that duplicate, but thanks. I just fear that the title doesn't really capture the issue, and so new users may still be unlikely to find it, don't you think? – AdamMc331 Dec 23 '14 at 22:18
1

@McAdam331 You can edit the title to make it more general and searchable. – ThisSuitIsBlackNot Dec 23 '14 at 22:20
1

This is likely the best answer as it satisfies the query in only a single scan. There may be configurations of data where two range seeks are superior to a whole table scan, though--but in most cases, the `GROUP BY` solution is superior to a `JOIN` or other pattern that forces two separate table accesses. The problem is only magnified by having more courses in the list... :) – ErikE Dec 29 '14 at 20:28

score 1 · Answer 2 · edited Dec 23 '14 at 22:13

1

Such operation is called a relational division and could be defined as the inverse of the CROSS JOIN operation. There are various solutions to solve that problem. Some of them may be dependent of your RDBMS. In no particular order:

Using the `IN` operator

One solution is to write subqueries using the IN operator. Start by selecting a list of all students who are taking math, and make sure that student is IN the list of all students taking science, like this:

SELECT student
FROM enrollment
WHERE course = 'Math'
   AND student IN(
      SELECT student
      FROM enrollment
      WHERE course = 'Science');

Using a `JOIN`

SELECT "student"
FROM T t1 JOIN T t2 USING("student")
WHERE t1."course" = 'Math'
  AND t2."course" = 'Science'

See http://sqlfiddle.com/#!4/b637c7/3

edited Dec 23 '14 at 22:13

Sylvain Leroux

50,096
7
103
125

answered Dec 23 '14 at 21:50

AdamMc331

16,492
10
71
133

2

This is just opinion, but thumbs down on the `IN` syntax to perform what is basically a `JOIN`. It leads to hard-to-maintain code, and in general, nesting `IN`s is an antipattern that is best replaced with the normal `JOIN` syntax. – ErikE Dec 23 '14 at 21:55
@ErikE a join will definitely work here also. Feel free to add to the answer, if you'd like to demonstrate the syntax for a join. There's always more than one way to solve a problem. – AdamMc331 Dec 23 '14 at 21:56
If `IN` will not work, I surely would have said "This will not work" instead of "thumbs down on the `IN` syntax." Do you have a response about what I actually *did say*? If not, then my comment stands unopposed--`JOIN` is generally superior to `IN`, especially once you are forced to start nesting the blasted things, and the clarity and correctness of `JOIN` rises up as so patently and blatantly superior. – ErikE Dec 29 '14 at 20:25

score 0 · Answer 3 · answered Dec 23 '14 at 22:07

0

In Oracle, SQL Server, and Postgresql, you can use the intersect set operator:

select student from tbl where course = 'Math'
intersect
select student from tbl where course = 'Science'

answered Dec 23 '14 at 22:07

Brian DeMilia

13,103
1
23
33

This is great relationally, and knowing `INTERSECT` is a good skill! Just know that this will take two different table accesses. Depending on the data, that could be good if both accesses are range seeks, and the `GROUP BY` method has to scan the whole table. However, I would expect (totally opinion based on experience, here) that `INTERSECT` in this case will not perform as well as the `GROUP BY` solution. – ErikE Dec 29 '14 at 20:30

Select values that have different rows matching different conditions?

3 Answers3

Using the IN operator

Using a JOIN

Using the `IN` operator

Using a `JOIN`