join two tables where unique id is 0 in other table

below are the two tables 1st is media_taxonomy and 2nd is media_taxonomy_map to join

media_taxonomy:

media_taxonomy_map:

here is my sql query

select * from media_taxonomy_map as mtm
join media_taxonomy as mt
on mtm.media_taxonomy_id = mt.media_taxonomy_id

here is the result:

BUT, I also want the rows in the joined table where media_taxonomy_id is 0 in media_taxonomy_map table

is this possible?

Answer

I am guessing that you want all rows in the table. If so, just use left join:

select *
from media_taxonomy_map as mtm left join
     media_taxonomy as mt
     on mtm.media_taxonomy_id = mt.media_taxonomy_id;

If you specifically want matches or 0, then you can filter:

select *
from media_taxonomy_map as mtm left join
     media_taxonomy as mt
     on mtm.media_taxonomy_id = mt.media_taxonomy_id
where mtm.media_taxonomy_id = 0 or mt.media_taxonomy_id is not null;