Inner join if tables are empty with Top 1000 results

Question

I have three table valued parameters passed into an SP. These all contain data to filter a query. I want to join them to a table as below but only if there is data in a table valued parameter

    SELECT DISTINCT TOP (1000)
     Person.col1,
     Person.col2,
     FROM dbo.Person INNER JOIN
             @tbl1 t1 ON Person.col3 = t1.val INNER JOIN
             @tbl2 t2 ON Person.col4 = t2.val INNER JOIN
             @tbl3 t3 ON Person.col5 = t3.val

I am aware I can do a Left Outer Join but I don't want results with NULL values. The top 1000 necessary as there is a lot of data and scanning the whole table causes performance issues

You can use left join and select the column that is not null. — Alireza Ahmadi, Sep 08 '21 at 07:54
@AlirezaAhmadi Check for not null in a Where statement at the end of the query doesn't work when you have multiple tvfs. If tbl1 has values but tbl2 is empty and you select is not null on both tables no rows are returned. I would still want the result from tbl1 returned — Richard Watts, Sep 08 '21 at 09:18

score 0 · Answer 1 · answered Sep 08 '21 at 08:24

Try use coalesce to cater nulls check

SELECT DISTINCT TOP (1000)
     Person.col1,
     Person.col2,
     FROM dbo.Person INNER JOIN
         @tbl1 t1 ON coalesce(Person.col3, '111') = t1.val INNER JOIN
         @tbl2 t2 ON coalesce(Person.col4 , '111')= t2.val INNER JOIN
         @tbl3 t3 ON coalesce(Person.col5, '111') = t3.val

Inner join if tables are empty with Top 1000 results

1 Answers1