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.

"table A and table B match" Match on what condition? On some condition no matter what it is? Do you mean natural join? Do you mean cross join? But then why mention "matches"? (Although cross join is inner join on true.) (Do you mean, cross join pairings?) — philipxy, Apr 12 '22 at 08:56

score 17 · Answer 1 · answered Mar 07 '13 at 07:10

17

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

answered Mar 07 '13 at 07:10

John Woo

258,903
69
498
492

11

But I don't want the query to return EVERYTHING (even non-matches) when table B has records...i want it to perform like a inner join. Only if table B is empty, then I want to return everything in table A. Does it make sense? – viv_acious Mar 07 '13 at 10:49

score 8 · Answer 2 · answered May 11 '15 at 14:46

8

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)

answered May 11 '15 at 14:46

s_a

173
2
4

Yikes, doesn't this do a table scan for every row in the initial result? – soren.qvist Jul 11 '17 at 07:21
Why not the inner join and join or id = id or (not exists) – Sahib Khan Dec 13 '22 at 20:49

score 2 · Answer 3 · answered Sep 18 '18 at 02:42

Here is another one, but you need to add one "null" row to table B if it's 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

score 2 · Answer 4 · answered Jul 19 '19 at 19:44

2

I would use an if-else block to solve it like 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

answered Jul 19 '19 at 19:44

Eray Balkanli

7,752
11
48
82

This should be the accepted answer, there isn't another easy way to solve this. – August Dec 22 '19 at 13:32

score 1 · Answer 5 · answered Dec 22 '17 at 10:25

This is 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

score 0 · Answer 6 · answered Sep 01 '17 at 13:11

0

Try This

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

answered Sep 01 '17 at 13:11

pradeep

550
4
10

You're complicating a LEFT JOIN and I'm not sure that you query works for the question asked – JSantos Sep 01 '17 at 13:36

score 0 · Answer 7 · answered Apr 20 '20 at 17:11

I did the following:

DECLARE @TableB TABLE (id INT)
-- INSERT INTO @TableB
-- VALUES (some ids to filter by)

SELECT TOP 10 *
FROM [TableA]          A
     LEFT JOIN @TableB B
               ON A.ID = B.id
WHERE B.id IS NOT NULL
   OR iif(exists(SELECT *
                 FROM TableB), 1, 0) = 0

Now: If TableB is empty (leave the commented lines commented) you'll get the top 10. If TableB has some ids in it, you'll only join by those.

I do not know how efficient this is. Comments are welcome.

Queequeg · Answer 8 · 2020-11-23T15:14:48.413

0

Maybe use a CTE

;WITH ctetable(
 Select * from TableA
)
    
IF(EXISTS(SELECT 1 FROM TableB))
BEGIN
 Select * from ctetable 
  Inner join TableB
END
ELSE 
BEGIN
 Select * from ctetable 
END

or dynamic SQL

DECLARE @Query NVARCHAR(max);
SET @QUERY = 'Select * FROM TableA';
IF(EXISTS(SELECT 1 FROM TableB))
BEGIN
 SET @QUERY = CONCAT(@QUERY,' INNER JOIN TableB');
END
EXEC sp_executesql @Query

edited Nov 23 '20 at 15:14

answered Nov 23 '20 at 11:27

Queequeg

131
1
3

Inner join 2 tables but return all if 1 table empty

8 Answers8