SQL - inner join 2 tables but return all if 1 table empty

Question

I have 2 tables say A and B, and I want to do a join on them.

Table A will always have records in it.

When table B has rows in it, I want the query to turn all the rows in which table A and table B matches. (i.e. behave like inner join)

However, if table B is empty, I'd like to return everything from table A.

Is this possible to do in 1 query?

Thanks.

Answer 1

Yes, for results like this, use LEFT JOIN.

Basically what INNER JOIN does is it only returns row where it has atleast one match on the other table. The LEFT JOIN, on the other hand, returns all records on the left hand side table whether it has not match on the other table.

To further gain more knowledge about joins, kindly visit the link below:

• Visual Representation of SQL Joins

Answer 2

I came across the same question and, as it was never answered, I post a solution given to this problem somewhere else in case it helps someone in the future. See the source.

select *
from TableA as a
left join TableB as b
    on b.A_Id = a.A_Id
where
    b.A_Id is not null or
    not exists (select top 1 A_Id from TableB)