What is wrong with this JOIN syntax?-CodePudding

If I run this SQL code in my PostgreSQL database, it works fine:

select 
    e.type_id,
    s.copy_destination || '\'
FROM 
    ebooks.ebooks e, ebooks.static_text s
WHERE
    e.status_id = 1

But when I add the join, everything goes pear-shaped. So if I run:

select 
    e.type_id,
    s.copy_destination || '\'
FROM 
    ebooks.ebooks e, ebooks.static_text s
join 
    ebooks.types y
on 
    e.type_id = y.type_id
WHERE
    e.status_id = 1

I get this error message:

ERROR:  invalid reference to FROM-clause entry for table "e"
LINE 11:     e.type_id = y.type_id
             ^
HINT:  There is an entry for table "e", but it cannot be referenced from this part of the query.
SQL state: 42P01
Character: 159

I've tried using the full reference (ebooks.eboooks.type_id) instead of just e, but nothing changed. I still got the "invalid reference to FROM-clause entry for table 'e'" - note that it still thinks I'm trying to reference table 'e'.

What am I doing wrong?

CodePudding user response：

The reason is that a comma (,) in the FROM list is almost, but not quite the same as a CROSS JOIN. Explicit join syntax binds before commas or, in other words, the comma separates stronger. So the reference to e.type_id is placed where that table is still out of scope. See:

Why does this implicit join get planned differently than an explicit join?

You can replace the comma with an otherwise equivalent CROSS JOIN, to silence the error:

SELECT e.type_id, s.copy_destination || '\'
FROM   ebooks.ebooks e
CROSS  JOIN ebooks.static_text s               -- !!!
JOIN   ebooks.types y ON e.type_id = y.type_id
WHERE  e.status_id = 1;

Rearranged to make clearer:

SELECT e.type_id, s.copy_destination || '\'
FROM   ebooks.ebooks      e
-- JOIN   ebooks.types       y USING (type_id)
CROSS  JOIN ebooks.static_text s    --   now you might as well use a comma again!
WHERE  e.status_id = 1

I commented out the join to ebooks.types completely as it seems to be dead freight - assuming referential integrity is guaranteed with an FK constraint.

The issue remains that the table static_text is joined to the rest without condition, which produces a Cartesian Product of both sets. Typically not what you want ...

CodePudding user response：

    select 
        e.type_id,
        s.copy_destination ||
        '\'
    FROM 
        ebooks.static_text s Join  ebooks.ebooks e
on s.ref_id = e.id
    join 
        ebooks.types y
    on 
        e.type_id = y.type_id
    WHERE
        e.status_id = 1

Change the query to this format then it should work. ref_id and id are the names of joining columns of 2 tables. Change that way because the order of tables in query and multiple joining columns (more than 1 column get join) in both tables can be the reason for this error.