Mixing explicit and implicit joins fails with "There is an entry for table ... but it cannot be referenced from this part of the query"

46
votes
SELECT
      i.*, 
      r.name AS roomname, 
      c.name AS cat, 
      p.key AS imgkey, 
      p.extension AS imgext
   FROM 
      items i, 
      rooms r, 
      categories c 
         LEFT JOIN photos p 
            ON p.referencekey = i.key 
   WHERE 
          i.room = r.key 
      AND r.key = 663308 
      AND i.sitekey = 32201 
      AND c.key = i.categorykey

The above query when executed returns following error.

ERROR: invalid reference to FROM-clause entry for table "i"

LINE 1: ...tegory c LEFT JOIN photos p ON p.referencekey = i.key WHER...

HINT: There is an entry for table "i", but it cannot be referenced from this part of the query.

3
sqlpostgresql
@DRapp: Thanks for Editing Great! – Umair A.
@a_horse_with_no_name Not disagreeing, but context/reasons for "never ever mix... " would be helpful here – kevlarr
@a_horse_with_no_name The answers here indicate that mixing joins is merely a little messy rather than being something one should not do for one reason or another. I agree with you about not mixing them, but only really because of clarity - are there more objective reasons for not mixing? (Asking because I would like knowing/learning.) – kevlarr
@user2297550 stackoverflow.com/questions/6347897/… i.e. this question – Caleth
@Caleth Absolutely agree that clarity is important. I was only suggesting that "Never ever mix implicit and explicit joins" is a great rule of thumb but it doesn't help people learn, as it - and the other answers - don't go into good detail on why they shouldn't ever be mixed. At least, there aren't objective reasons that non-experts like me would read as "A-ha, that's why I should never use implicit!"... I personally don't use implicit because I like the clarity of using JOIN ... ON ... but I don't know if there are performance benefits, etc. – kevlarr

3 Answers

47
votes

The SQL spec states that explicit joins are performed before implicit joins. This is an implicit join:

FROM table1 t1, table2 t2 WHERE t1.id=t2.t1id

This is an explicit join:

FROM table1 t1 JOIN table2 t2 ON (t1.id=t2.t1id)

This code bit:

categories c 
     LEFT JOIN photos p 
        ON p.referencekey = i.key

is an explicit join and is run first. Note that at this point the table aliased as i hasn't been looked at yet, so it can't be joined yet. Note that MySQL fixed this behaviour in 5.2 I believe, and this query will no longer work there either.

25
votes

Move your JOIN statement next to the table you are joining on:

SELECT
      i.*, 
      r.name AS roomname, 
      c.name AS cat, 
      p.key AS imgkey, 
      p.extension AS imgext
   FROM 
      items i
         LEFT JOIN photos p 
            ON p.referencekey = i.key, 
      rooms r, 
      categories c 
   WHERE 
          i.room = r.key 
      AND r.key = 663308 
      AND i.sitekey = 32201 
      AND c.key = i.categorykey

The long explanation:

A JOIN is part of an expression that results in a source table, used in the FROM clause as a from_item. Your FROM clause has 3 from_item source tables:

  • items
  • rooms
  • categories joined to photos

The error is in the ON join_condition of your categories joined to photos from_item. You are referencing a table, items, that does not exist in the from_item. The solution is to move the photos join into the items from_item, so that you have the following from_item source tables:

  • items joined to photos
  • rooms
  • categories

Sadly, I can't find an example in the documentation that makes clear this relationship between a table in the FROM clause and a JOIN. The SELECT Synopsis shows this syntax and is the best source in the documentation to find this distinction. Notice that a JOIN is not a sibling clause to FROM, but actually part of a from_item within the FROM clause. Thus, if your FROM clause consists of a list of tables, each table in that list can have its own joins. Then it becomes more intuitive that each table involved in a join must be included in a single from_item.

16
votes

Since your Items.Room = the Rooms.Key, I would just have that as the where..

SELECT
      i.*, 
      r.name AS roomname, 
      c.name AS cat, 
      p.key AS imgkey, 
      p.extension AS imgext
   FROM 
      items i
         LEFT JOIN photos p 
            ON p.referencekey = i.key 
         JOIN rooms r
            on i.room = r.key
         JOIN categories c 
            on i.categorykey = c.key
   WHERE 
          i.sitekey = 32201 
      AND i.room = 663308