Get all records from inner joined tables if second table does not have data

Posted on

Question :

I follow below syntax by which I achieve the result. but it takes time

IF NOT EXISTS(select * from #tblxyz)
BEGIN
INSERT INTO #tblxyz select NULL
END

Select a.*
        FROM
            tblabc a 
            INNER JOIN #tblxyz b ON a.ID = ISNULL(b.ID,a.id)

If second joined table has some rows, I only want to return those joined rows only. Only if the second table has no rows, I want to return al lrows from the first table.

Answer :

I think the condition ON a.ID = ISNULL(b.ID,a.id) is not the best way to do this. It may lead to inefficient plans and may return unwanted results (according to your specifications). If the tblxyz table has rows with NULL values, you’ll still get all the rows from table a (and possibly multiple times).

I would write the query without using the ISNULL() function, like this:

SELECT a.*
FROM tblabc a 
    INNER JOIN #tblxyz b 
    ON a.ID = b.ID 
    OR NOT EXISTS (SELECT * FROM #tblxyz) ;

or like this:

SELECT a.*
FROM tblabc a 
    INNER JOIN #tblxyz b 
    ON a.ID = b.ID 

UNION ALL

SELECT a.*
FROM tblabc a 
WHERE NOT EXISTS (SELECT * FROM #tblxyz) ;

The way you’ve done it is correct.

So the reasons for slowness will likely come down to: when is it slow (is it when the temp table has a lot of rows, or when it has few rows?), how many rows are usually in each table, what indexes exist on each table, and what data type is ID?

You can also look at your real-world execution plan for some answers.

I think you will find that you have an index on ID on the base table, it’s slow when you have a lot of rows in the temp table, and if that is the case you likely just need to add an index on the temp table (otherwise the plans are built assuming 1 row).

IF OBJECT_ID(N'tempdb..#table1') IS NOT NULL drop table #table1
IF OBJECT_ID(N'tempdb..#table2') IS NOT NULL drop table #table2

create table #table1 (id int, value varchar(10))
create table #table2 (id int)

insert into #table1(id, value) VALUES (1,'value1'), (2,'value2'), (3,'value3')
--test here. Comment next line
insert into #table2(id) VALUES (1) --Comment/Uncomment

select * from #table1
select * from #table2


select #table1.* 
from #table1 
left JOIN #table2 on 
    #table1.id = #table2.id
where (#table2.id is not null or not exists (select * from #table2))

Leave a Reply

Your email address will not be published. Required fields are marked *