SQL natural join, vs join on, vs where

Question

For example, I have the following tables:

pilot(eID, pID)

plane(pID, make, model, range)

(eID means employee ID and pID means planeID)

I want to get the eID of the pilot and the model of the plane the pilot flys, of all the planes made by Airbus. It seems there are multiple ways to get this, and I'm not sure which should be used:

using natural join:

SELECT pilot.eID, plane.model FROM pilot NATURAL JOIN plane WHERE plane.make = 'Airbus'

using cross join: (or the equivalent where you replace pilot, plane with pilot CROSS JOIN plane)

SELECT pilot.eID, plane.model FROM pilot, plane WHERE pilot.pID = plane.pID AND plane.make = 'Airbus'

using theta join:

SELECT pilot.eID, plane.model FROM pilot join plane ON pilot.pID = plane.pID WHERE plane.make = 'Airbus'

(I understand you can use USING (pID) instead of on pilot.pID = plane.pID for theta join and it will remove the duplicate pID column).

Which is the better to use? Why are there these different ways of doing it? (As in what other cases would there be that required one query rather than the others) And if I didn't have the make = 'Airbus' requirement would the answer be different?

[From Wikipedia](http://en.wikipedia.org/wiki/Join_(SQL)#Natural_join) `Most experts agree that NATURAL JOINs are dangerous and therefore strongly discourage their use` — StuartLC, May 08 '14 at 13:36
@StuartLC, I can understand why Natural Joins are more dangerous, but my course is more theoretical, so performance is more important. — Jonathan., May 08 '14 at 13:39
@DStanley, not homework, revision. (is that why you deleted your answer? because I can see deleted answers :)) — Jonathan., May 08 '14 at 13:43

score 0 · Answer 1 · answered May 08 '14 at 13:40

NATURAL JOIN is not supported in all database systems
cross join is an older syntax that is falling out of favor
JOIN syntax (your example #3 which is different than what I think of as a "theta" join) is more widely accepted (both by SQL providers and by developers)

Which is the better to use?

Most would say #3, but some systems may optimize different join types better

Why are there these different ways of doing it?

Because syntax changes over time and across systems

And if I didn't have the make = 'Airbus' requirement would the answer be different?

Probably not. Again, some systems may optimize a particular syntax better but there's not a universal answer that covers all systems.

score 0 · Answer 2 · edited May 23 '17 at 10:33

0

Cross join can cause enormous data size because of the property of the cartesian product rule. See: http://en.wikipedia.org/wiki/Cartesian_product

You can check the site below for the two other join types;

Difference between a theta join, equijoin and natural join

And for more: http://en.wikipedia.org/wiki/Relational_algebra

edited May 23 '17 at 10:33

Community

1
1

answered May 08 '14 at 13:40

Ozan Deniz

1,087
7
22

score 0 · Answer 3 · answered May 08 '14 at 13:44

I would suggest you use the most widely supported version of the latest syntax - ie:

SELECT pilot.eID, plane.model 
FROM pilot 
     [ INNER ] JOIN plane 
ON pilot.pID = plane.pID 
WHERE plane.make = 'Airbus'

The advantage over the other two options is that the join is

a) explicit and
b) separated from the filtering (where) clause.

SQL natural join, vs join on, vs where

3 Answers3