Selecting records based on two other tables

Question

I have a query with inner join to another table, with this I want also include records which are contained in another column.

Example:

select name, address from table1
inner join table2 on table1.id = table2.id

With this, I want to also include rows which are having table1.recno = (1,2,4).

How could I write query for that?

One option I know is to use the IN keyword instead of the first table join. But our client doesn't want to use the IN keyword.

Answer 1

Use a left join and then use the WHERE clause to filter out the rows that you need.

select name, address 
from table1 
    left join table2 on table1.id = table2.id
where
    table2.id IS NOT NULL OR table1.ID In (1,2,4)

Or if you want to avoid an innocuous IN for silly reasons, use:

select name, address 
from table1 
    left join table2 on table1.id = table2.id
where
    table2.id IS NOT NULL 
  OR table1.ID = 1
  OR table1.ID = 2
  OR table1.ID = 4