SQL Join returns all rows

Question

I am trying to do a select on 2 tables and return data if the Table1.codeId is presented on Table2 OR if Table1.codeId = 0.
However it's returning all the data from Table2.

    Table1 {
      name nvarchar,
      codeId int
    }

    Table1 Records
    Bob, 1
    John, 2
    Chris, 0

    Table2 {
      id int,
      codeName nvarchar
    }

    Table2 Records
    1, Engineer
    2, Doctor
    3, Dentist
    4, Pilot
    5, Mechanic

    SELECT t1.name, t2.codeName
    FROM dbo.Table1 t1, dbo.Table2 t2
    WHERE (t1.codeId = t2.id OR t1.codeId = 0)

I just want to return:
Bob, 1
John, 2
Chris, 0

How can I do it ?
Thx

Answer 1

Try two queries:

select * 
from Table1
where codeId = 0 or codeId in (select id from Table2)

select t1.name, t2.codeName
from Table1 t1
left join Table2 t2 on t2.id = t1.codeId