Drew Ackerman Drew Ackerman - 1 month ago 4
SQL Question

Can I skip a join in my select?

When learing about joins, our instructor says to not skip tables.

For example, lets do a query that

Selects
the
Last_Name
,
First_name
, and
Numeric_Grade
.

I would write



Select Last_Name, First_Name, Numeric_Grade
From Student
Join Grade
Using(Student_id)


He says to write



Select Last_Name, First_Name, Numeric_Grade
From Student
Join Enrollment
Using(Student_id)
Join Grade
Using(Student_id)


Im confused because as long as long as i can link them through similar fields, i dont see the point of going enrollment.
He has not given me a reason for going through enrollment, other than its what the Diagram shows. Follow the diagram.

Do I have to go through Enrollment? Is it the safe way to do it, or does it not matter because Grade and Student have a
Student_id
primary key?

enter image description here

Answer

Quoting Alice Rischert in Oracle SQL By Example, lab 7.2:

The second choice is to join the STUDENT_ID from the GRADE table directly to the STUDENT_ID of the STUDENT table, thus skipping the ENROLLMENT table entirely. - - This shortcut is perfectly acceptable, even if it does not follow the primary key/foreign key relationship path. In this case, you can be sure not to build a Cartesian product because you can guarantee only one STUDENT_ID in the STUDENT table for every STUDENT_ID in the GRADE table. In addition, it also eliminates a join; thus, the query executes a little faster and requires fewer resources. The effect is probably fairly negligible with this small result set.

Comments