CODEWITHSUNDEEP
sqloracle11g
Drew Ackerman
Drew Ackerman

Reputation: 341

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

Upvotes: 3

Views: 330

Answers (3)

James Brown
James Brown

Reputation: 37404

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.

Upvotes: 2

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48197

Depend on your tables. Sometimes you can but sometimes dont.

For example imagine in enrollment you have something like student_quit_course

Then you may only want grade of student actually finish the course and you need all three table

For this particular case you will have a GRADE for several section_id but to know what is that section you need [Section] and [Course] both join using [Enrollment]

Upvotes: 2

Randy Toye
Randy Toye

Reputation: 71

The only reason to go through the Enrollment table would be if you need information (fields) from that table. If both the Enrollment and Grade table have a Student_id field then you wouldn't need to go through Enrollment to get there.

In your example it looks like you are looking for First and Last Name, which should both come from the Student table and Numeric_Grade which should come from the Grade table. In this instance, there would be no need for the Enrollment table. If there were a WHERE clause that required something from the Enrollment table then yes you would need to include it, but your example I would say it is not needed.

If this is a question on a test or assignment and the teacher is requesting you go through the Enrollment table too I would do it just to appease him, but knowing that you don't actually need to do it to get the information that you require.

Upvotes: 2

Related Questions