CODEWITHSUNDEEP
sqljoinleft-joininner-join
viv_acious
viv_acious

Reputation: 2489

Inner join 2 tables but return all if 1 table empty

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.

Upvotes: 17

Views: 33294

Answers (8)

Queequeg
Queequeg

Reputation: 131

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

Upvotes: 0

Yehuda Makarov
Yehuda Makarov

Reputation: 605

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.

Upvotes: 0

Eray Balkanli
Eray Balkanli

Reputation: 7960

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

Upvotes: 2

Jose
Jose

Reputation: 403

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

Upvotes: 2

scar80
scar80

Reputation: 1702

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

Upvotes: 1

pradeep
pradeep

Reputation: 790

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;

Upvotes: 0

s_a
s_a

Reputation: 173

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)

Upvotes: 8

John Woo
John Woo

Reputation: 263713

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:

Upvotes: 17

Related Questions