2

Possible Duplicate:
Explicit vs implicit SQL joins

I understand that lots of people will shout at me now. But from my understanding

Say I have two tables

STUDENTS

student_id
firstname
surname

COURSES

course_id
name
student_id

So the courses table has a foreign key STUDENT_ID meaning that ONE student can have MANY courses yes?

OKAY.

From my understanding, if I want to select all the course associated with ONE student I could do either these:

SELECT * 
FROM courses AS c, students AS s 
WHERE c.student_id = s.student_id 
  AND s.student_id = 1;

OR

SELECT * 
FROM courses AS c 
   JOIN students AS s ON c.student_id = s.student_id AND s.student_id = 1;

So what's the point in the JOIN when its essentially EXACTLY the same as the WHERE?

I know my understanding is WRONG but I cannot find a simple answer.

Please enlighten me!

Community
  • 1
  • 1
AlexMorley-Finch
  • 6,785
  • 15
  • 68
  • 103
  • it's not wrong, those queries are synonym. Lookup here for implicit joins vs explicit joins. Also change the title of your question, it is inaccurate. And note that if that is synonym for INNER JOINs, it's not for OUTER JOINs. – Benoit Feb 23 '12 at 10:47
  • 1
    I think the OP is getting confused between joins and constraints, see the case mentioned above for the difference between joins and where clauses and see the answer by Sapan for the usage of constraints. – rrrr-o Feb 23 '12 at 10:51
  • @MitchWheat "what are the point of shoes?" – rrrr-o Feb 23 '12 at 10:52
  • 1
    @rrrr: to protect the feet of people stamping on one's head – Mitch Wheat Feb 23 '12 at 10:55
  • @rrrr: Actually, I would say that the noun referred to by `what's` is `point` and not `shoes`, and `point` is singular not plural. Thus, `What is` should be singular, and not plural. `What are the purposes of shoes?` is fine, but `What are the purpose of shoes?` appears to be incorrect. – MatBailie Feb 23 '12 at 11:17
  • How long are we all going to stand around not re-word the question? :-) – Aaron Bertrand Feb 23 '12 at 13:02

2 Answers2

7

FOREIGN_KEY makes your life simple when you try to insert something by checking for the integrity of the data. It was never meant to help you while retrieving the data from the relations.

e.g. If you try to insert a student with course_id = 10 when no such course exists, then foreign key constraint wouldn't allow you to have such a student.

JOIN is exactly the same as using WHERE. Have a look at this question.

Community
  • 1
  • 1
Sapan Diwakar
  • 10,480
  • 6
  • 33
  • 43
  • That answers the title of the question. But the content then asks about the difference between using `,` and `JOIN`. – MatBailie Feb 23 '12 at 10:51
1

In short: there is no difference.

Longer explanation: the relational model is based on the "cartesian product". In the query

SELECT a.x , b.y
FROM table_a a, table_b b
;

, every possible combination of rows form table_a and table_b is produced. If a contains 10 rows, and b 100 rows, you would get 1000 rows. Everything you add to the WHERE-clause restricts these results to only the pairs of rows that satisfy the WHERE-clause. So in 

SELECT a.x , b.y, ...
FROM table_a a, table_b b
WHERE a.x = b.y
;

you would get everything, except the rows for which `NOT (a.x = b.y)'

In practice, there are two kinds of WHERE-clause elements: those that relate two tables, and those that compare a column-expression to a constant. The JOIN-clause is a way to specify the first kind of restrictions. There are some minor differences and complications (NULLs, outer joins), but for the time being the two constructs are equivalent.

wildplasser
  • 43,142
  • 8
  • 66
  • 109