1

I'm starting out with SQL and for some reason the professor advised us to stay away from JOIN statements. However, after doing some reason I've come to a conclusion that JOIN statements are actually preferred. What's the official stance on this?

Also, I'd like to convert this WHERE statement to JOIN statement: 

SELECT max(z.depttime) 
FROM flights z
WHERE z.ref = p.ref 
   AND z.depttime < f.depttime

I've tried this, but apparently it's wrong :

SELECT max(z.depttime) 
FROM bus_journey z 
JOIN z.ref=p.ref 
ON z.depttime < f.depttimewhere

Also, just to clarify, the reason why i'm converting from WHERE to JOIN is that the software i'm using states that "Old style JOIN (ANSI JOINs) are not allowed. Please use standard syntax."

Any help is appreciated. Cheers!

Grandas
  • 519
  • 1
  • 5
  • 13
  • 3
    A join has to be between two tables. What is `p`? – Barmar Feb 13 '15 at 00:59
  • 3
    The version with `WHERE` won't work, either, because there's no `p` table. – Barmar Feb 13 '15 at 00:59
  • 2
    `JOIN table_name ON join_condition` –  Feb 13 '15 at 01:11
  • 1
    This is a much debated topic on SO - inner join vs where [here](http://stackoverflow.com/questions/121631/inner-join-vs-where) and [here](http://stackoverflow.com/questions/1018822/inner-join-on-vs-where-clause). Basically, WHERE are implicit joins and JOINs are explicit joins -the latter being the new ANSI standard for over 20 years now! Performance and logic does not differ but most agree JOINs provide better readability, clarity, and debugging. – Parfait Feb 13 '15 at 01:36

2 Answers2

1

The JOIN keyword has to be followed by a table name. Then you use an ON clause to specify the joining condition between that table and previous tables. It should be something like this:

SELECT MAX(z.depttime)
FROM bus_journey z
JOIN other_table p ON z.ref = p.ref
JOIN third_table f ON z.depttime < f.depttime

This is equivalent to the implicit join:

SELECT MAX(z.depttime)
FROM bus_journey z, other_table p, third_table f
WHERE z.ref = p.ref AND z.depttime < f.depttime
Barmar
  • 741,623
  • 53
  • 500
  • 612
1

Being a bit of an old fart I use the WHERE syntax. Some of the younger people I work with have gotten excited about the JOIN syntax. There really is no functional difference and sometimes I use the JOIN syntax, especially when sharing code. It is more consistent with the OUTER and INNER JOIN syntax, otherwise IMHO has no benefit (or detriment)

Karl
  • 3,312
  • 21
  • 27