CODEWITHSUNDEEP
sqlpostgresqljoin
Mandroid
Mandroid

Reputation: 7494

WHERE not working with INNER JOIN in Postgresql

When I run:

SELECT * from actor inner join film_actor fa ON actor.actor_id = fa.actor_id

I get results.

But running:

SELECT * from actor inner join film_actor fa WHERE actor.actor_id = fa.actor_id

It gives error:

ERROR: syntax error at or near "WHERE"

As far as I understand, WHERE is correct usage in this case.

Any input would help.

Upvotes: 0

Views: 1528

Answers (2)

user330315
user330315

Reputation:

As far as I understand, WHERE is correct usage in this case.

No, you mis-understood this.

The WHERE clause comes after all JOIN clauses, it can never be part of the JOIN condition.

The general format of a JOIN is: 

table_b JOIN table_b ON <some_condition>

there is no WHERE allowed in that part. The only variation to that are different types of joins (left join, full join, right join). The joins always follow the FROM clause. Only if all joins are fully specified (including the join condition using ON) you can start writing the WHERE clause.

Ignoring sub-queries and derived tables for the moment, you can imagine the FROM clause as "one big thing", and only if that is complete you not allowed to write the WHERE clause.

Maybe you are thinking of the ancient, outdated and fragile implicit joins:

SELECT * 
from actor, 
     film_actor fa 
WHERE actor.actor_id = fa.actor_id

But using the explicit JOIN operator with the join condition in the ON clause is much better coding style. Don't get used to the outdated and ancient implicit joins.

Upvotes: 1

donkopotamus
donkopotamus

Reputation: 23186

For that style of syntax you want

SELECT * from actor, film_actor fa WHERE actor.actor_id = fa.actor_id

Upvotes: 0

Related Questions