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.
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))