SQL using subqueries instead of join for one to ma

问题:

I am trying to create a select that will allow me to join two tables together without using any operation that uses JOIN. The catch is that these two tables contain a one to many relationship.

Let's say there are two tables, Department and Course.

Department
D_CODE | SCHOOL
01             | 1111
02             | 1111
03             | 2222

Course
CRS_CODE | D_CODE
MATH1           | 01
MATH2           | 02
ACCT1           | 03

How do I find all schools and the total number of courses it provides without using any type of join?
The result is that School 1111 has 2 courses and School 2222 has 1 course.
I've tried using WITH and multiple subqueries, but the resulting values always duplicates. So it will say School 1111 has 16 courses and School 2222 has 16 courses (as an example).

Guidance would be much appreciated. Thank you!

回答1:

Try this. Without using sub-query in Where condition also you will get the same result.

SELECT Count(D_CODE) No_OF_Course,
       SCHOOL
FROM   Department
WHERE  D_CODE IN(SELECT D_CODE
                 FROM   course)
GROUP  BY SCHOOL 

回答2:

But you can avoid sub queries and explicit joins: Use SELECTS over multiple tables (some kind of implicit join). This Code is not tested but will probably work:

SELECT COUNT(*) FROM course, department WHERE course.D_CODE = department.D_CODE

Edit: a commenter claims this statement is wrong and sounds reasonable about it: You know subqueries are very expensive and will kill your database server performance? Use joins when possible.