SQL joins, "there is an entry for table but it cannot be referenced"

Question

I have a SQL query which is failing, giving me the error:

"There is an entry for table but it cannot be referenced from this part of the query"

Going through the query, I need both all 3 tables, but only trips and boats have a matching ID to join on. Test is a shapefile which I need to perform a postGIS function on but it shares no similar column id's to the other two.

 select trips.*
 from trips, test
 inner join boats on boats.id = trips.boat_id
 where st_intersects(trips.geom, test.geom) and
 boats.uid = 44

I think it has something to do with the join statement but I don't really understand what. I'd be very interested in an explanation of what is happening here.

Answer 1

Simple rule: Never use commas in the FROM clause. Always use explicit JOIN syntax . . . even if that means writing out CROSS JOIN.

As mentioned in the comments, the right way to write the query is:

 select trips.*
 from trips inner join
      test
      on st_intersects(trips.geom, test.geom) inner join
      boats
      on boats.id = trips.boat_id
 where boats.uid = 44;

However, the question is why this doesn't work:

from trips,
     test inner join
     boats
     on boats.id = trips.boat_id

First, I want to note that the failure of this syntax is better described in the MySQL documentation than in the Postgres documentation:

However, the precedence of the comma operator is less than of INNER JOIN, CROSS JOIN, LEFT JOIN, and so on. If you mix comma joins with the other join types when there is a join condition, an error of the form Unknown column 'col_name' in 'on clause' may occur. Information about dealing with this problem is given later in this section.

I think a simpler way of describing this is that JOIN is an operator that acts on the tables/expressions that are its arguments. The comma separates the arguments. So, the first table simply isn't recognized because the comma "blocks" it.

I think you can get around this using parentheses:

from (trips, test inner) join
     boats
     on boats.id = trips.boat_id

You can definitely get around it using CROSS JOIN:

from trips cross join
     test inner join
     boats
     on boats.id = trips.boat_id

But why bother? There is a correct way to write the query.