Semantics of multiple joins

Question

What happens actually when we use cascaded join statements

select student.name, count(teacher.id)
from student
left join course on student.course_id = course.id
left join teacher on student.teacher_id = teacher.id
group by student.name;

It seems when I used only the first left join alone it returned 30 rows while using the second left join alone returned 20 rows. But using together returns 600 rows. What is actually happening ? Does the result from the first left join is used in the second ? I don't understand the semantics. Help me understand it.

See http://en.wikipedia.org/wiki/Cartesian_product. FYI, you have shown absolutely no reason to join on `course` — Phil, Aug 05 '14 at 04:24
@Phil suppose I want to get the average course fee using avg(course.fee), then what should I do ? — Md. Arafat Al Mahmud, Aug 05 '14 at 04:41

Barmar · Accepted Answer · 2014-08-05T04:53:45.783

4

Since you don't have any join conditions between teacher and course, you're getting a full cross-product between each of the other two joins. Since one join returns 20 rows and the other returns 30 rows, the 3-way join returns 20x30 = 600 rows. Its equivalent to:

SELECT t1.name, count(t2.id)
FROM (SELECT student.name
      FROM student
      LEFT JOIN course ON student.id = course.id) AS t1
CROSS JOIN
     (SELECT teacher.id
      FROM student
      LEFT JOIN teacher ON student.id = teacher.id) AS t2
GROUP BY t1.name

Notice that the CROSS JOIN of the two subqueries has no ON condition.

The correct way to structure this database is as follows:

student table: id (PK), name
course table: id (PK), name, fee, credits
student_course table: id (PK), student_id (FK), course_id (FK), unique key on (student_id, course_id)

Then to get the name of each student and the average course fee, you would do:

SELECT s.name, AVG(c.fee) AS avg_fee
FROM student AS s
LEFT JOIN student_course AS sc ON s.id = sc.student_id
LEFT JOIN course AS c ON sc.course_id = c.id

edited Aug 05 '14 at 04:53

answered Aug 05 '14 at 04:27

Barmar

741,623
53
500
612

so If I want to see the average course fee from the same select statement, Should I do that in different query ? – Md. Arafat Al Mahmud Aug 05 '14 at 04:35
Your queries don't seem to make much sense at all. Why are you equating student, teacher, and course IDs? – Barmar Aug 05 '14 at 04:38
What you need are relation tables. There should be a `student_course` table that lists the courses that each student is taking, and a `course_teacher` table that lists which courses each teacher is teaching. – Barmar Aug 05 '14 at 04:40
I understand that, but I have to work on existing design like this – Md. Arafat Al Mahmud Aug 05 '14 at 04:40
It doesn't make any sense. Just because a student and a course have the same ID, it doesn't mean the student is taking that class. – Barmar Aug 05 '14 at 04:41
1

If there are 30 students taking the same class, they can't all have the same ID as the class. – Barmar Aug 05 '14 at 04:42
sorry this was a mistake. Now edited the SQL statement in my question. Please have a look. Student table has id, name, course_id, teacher_id fields – Md. Arafat Al Mahmud Aug 05 '14 at 04:44
So your students can only take one course, and they only have one teacher? – Barmar Aug 05 '14 at 04:45
If there's only one course, then the average is the same as the fee for that course. – Barmar Aug 05 '14 at 04:47
I know this is the design flaw. But can you please suggest me, what would be the statement if the student could take multiple courses ? – Md. Arafat Al Mahmud Aug 05 '14 at 04:48
so no way to do that from existing design in the select statement ? – Md. Arafat Al Mahmud Aug 05 '14 at 04:59

score 0 · Answer 2 · edited May 23 '17 at 11:49

0

All Mysql joins are graphically explained here. Take a look and choose correct joins for both joined tables.

edited May 23 '17 at 11:49

Community

1
1

answered Aug 05 '14 at 04:27

LHristov

1,103
7
16

1

Those diagrams only show 2-way joins. His question is specifically about 3-way joins. – Barmar Aug 05 '14 at 04:28

Semantics of multiple joins

2 Answers2