Postgres Join Table Error if First Select is Empty-CodePudding

I am using Postgres. I have three tables: pictures, tags and picture_tags.

picture_tags is a join table.

Columns worth mentioning:

picture.id
tag.id
picture_tags.picture_id
picture_tags.tag_id

I am trying to run the following query to get all tags for picture id 100:

SELECT * FROM picture_tags
WHERE picture_id = 100
JOIN tags
ON tags.id = picture_tags.tag_id;

In my app there is a chance that a Picture may not have any Tags, thus there will be no picture_tags record to associate the two. When there is no Tags for a Picture, I get the following error:

syntax error at or near "JOIN"

since my select query returns nothing / is empty. If my select query is empty, is there a way to exit early before trying to join? Trying to make a fail-safe for when the select returns empty.

CodePudding user response：

SELECT P.*,PTG.*,TG.*
FROM PICTURE AS P
LEFT JOIN PICTURE_TAGS AS PTG ON P.PICTURE_ID=PTG.PICTURE_ID
LEFT JOIN TAGS AS TG ON PTG.TAG_ID=TG.TAG_ID
WHERE P.PICTURE_ID=100