Geek Answers Handbook

SQL is an inner join of 2 tables, but returns everything if 1 table is empty

I have 2 tables that say A and B, and I want to join them.

There will always be entries in table A.

When table B has rows in it, I want the query to include all rows in which table A and table B correspond (i.e. behave like an internal join)

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

Is it possible to do this in 1 query?

Thanks.

+9
source share
6 answers

Yes, for results like this, use LEFT JOIN .

Basically, what an INNER JOIN does is, it returns a row only if it has at least one match with another table. On the other hand, the LEFT JOIN returns all the entries in the left-side table, whether it matches the other table.

To learn more about joining, follow the link below:

+12
source

I came across the same question and, since they have never been answered, I am publishing a solution to this problem somewhere else, in case it helps anyone 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)
+5
source

Here is another one, but you need to add one “zero” row to table B if it is empty

 -- In case B is empty Insert into TableB (col1,col2) values (null,null) select * from TableA as a inner join TableB as b on b.A_Id = a.A_Id or b.A_Id is null
+2
source

try it

 SELECT t1.* FROM table1 AS t1 INNER JOIN table2 AS t2 ON t1.something = t2.someotherthing UNION SELECT * FROM table1 WHERE something = somethingelse;
0
source

This solution:

 CREATE TABLE MyData(Id INT, Something VARCHAR(10), OwnerId INT); CREATE TABLE OwnerFilter(OwnerId INT); SELECT * FROM (SELECT NULL AS Gr) AS Dummy LEFT JOIN OwnerFilter F ON (1 = 1) JOIN MyData D ON (F.OwnerId IS NULL OR D.OwnerId = F.OwnerId);

Link to sqlfiddle: http://sqlfiddle.com/#!6/0f9d9/7

0
source

I would use an if-else block to solve it, as shown below:

 if (select count(*) from tableB) > 0 begin Select * from TableA a Inner Join TableB b on a.ID = b.A_ID end else begin Select * from TableA end
0
source

More articles:


All Articles