Sql-server – Get all records from inner joined tables if second table does not have data

performancequery-performancesql serversql-server-2008

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.

Best 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) ;
Related Question