MS SQL - JOIN only if parameter exists in stored proc

Question

I have a stored proc that has the following (simplified example):

DECLARE @id int = NULL
SELECT * FROM table1 t1
INNER JOIN table2 t2 ON ((@id IS NOT NULL) AND (t2.id = @id))

The purpose of this is to return all rows if @id is not provided, else return just rows matching @id.

Works as expected as long as @id is provided. But it returns no rows is @id is null.

I thought, maybe

INNER JOIN table t2 ON (@id IS NULL OR ((@id IS NOT NULL) AND (t2.id = @id)))

might work, but if @id is null, then it seems to return unending rows (I waited 30 seconds, and it was past 1M rows. (There are only 150 rows in table1)

I've read around, and most other examples to accomplish this seem to use dynamic SQL (which I'd rather not do), or the possibility of creating a temp table, which seems a little extreme for this kind of thing.

What are my options? Thank you.

Answer 1

I am doubtful on your table joining, tried to simplified your SQL query

DECLARE @id int = NULL
SELECT * 
FROM table1 t1
INNER JOIN table2 t2 ON  t1.id=t2.id
WHERE @id IS NULL OR t2.id = @id

Answer 2

Both table1 and table2 have one column (id) of type int in this example. It should work for other datatypes too just need to change the where clause and ISNULL function in the select accordingly as shown below ( see comments for more info on how it works )

CREATE TABLE [dbo].[TABLE1]( [ID] [int] NOT NULL)  
GO
CREATE TABLE [dbo].[TABLE2]( [ID] [int] NOT NULL)  
GO

---- For INT Datatype -----

DECLARE @id int =  1--  NULL --
SELECT Distinct A.id_t1  -- , id_t2 -- , A.ID -- , *
FROM
(
    SELECT Distinct
            t1.id as id_t1  , -- Alias the id col differently for clarity
            t2.id as id_t2  , 
            ISNULL( @id , -1 ) as ID -- Helper column to help check for NULL value passed for @id. 
    FROM        dbo.table1 t1
    CROSS JOIN  dbo.table2 t2  

) A 
WHERE (A.id_t1 = A.ID  AND A.id_t1 = A.id_t2 )  OR A.ID = -1 -- Use that Helper column A.ID to narrow down the result set to what you want


---- For Char Datatype -----
    DECLARE @id varchar(1) =  '1' --  NULL -- Uncomment to Switch between NULL and 

    SELECT Distinct A.id_t1  -- , id_t2 -- , A.ID -- Uncomment as needed for Troubleshooting purposes
    FROM
    (
        SELECT Distinct
                t1.id as id_t1  , 
                t2.id as id_t2  , 
                ISNULL( @id , 'x' ) as ID -- Helper column to help check for NULL value passed for @id. 
        FROM        dbo.table1 t1
        CROSS JOIN  dbo.table2 t2  

    ) A 
    WHERE (A.id_t1 = A.ID  AND A.id_t1 = A.id_t2 )  OR A.ID = 'x'

Answer 3

You have a rather strange query when you have a Cartesian product of two tables.

It makes some sense if ID in table2 is unique. In this case the query below will always return all rows from table1 and either a value of the single row with the given ID from table2, or NULLs in the corresponding columns.

Sample data

DECLARE @Table1 TABLE (ID1 int PRIMARY KEY, Value1 int);
INSERT INTO @Table1(ID1, Value1) VALUES
(11, 101),
(12, 102),
(13, 103),
(14, 104);

DECLARE @Table2 TABLE (ID2 int PRIMARY KEY, Value2 int);
INSERT INTO @Table2(ID2, Value2) VALUES
(21, 221),
(22, 222),
(23, 223);

Query with not NULL

DECLARE @id int;

SET @id = 22;
SELECT * 
FROM
    @Table1 AS T1
    LEFT JOIN @Table2 AS T2 ON T2.ID2 = @id
OPTION(RECOMPILE);

+-----+--------+-----+--------+
| ID1 | Value1 | ID2 | Value2 |
+-----+--------+-----+--------+
|  11 |    101 |  22 |    222 |
|  12 |    102 |  22 |    222 |
|  13 |    103 |  22 |    222 |
|  14 |    104 |  22 |    222 |
+-----+--------+-----+--------+

Query with NULL

SET @id = NULL;
SELECT * 
FROM
    @Table1 AS T1
    LEFT JOIN @Table2 AS T2 ON T2.ID2 = @id
OPTION(RECOMPILE);

+-----+--------+------+--------+
| ID1 | Value1 | ID2  | Value2 |
+-----+--------+------+--------+
|  11 |    101 | NULL | NULL   |
|  12 |    102 | NULL | NULL   |
|  13 |    103 | NULL | NULL   |
|  14 |    104 | NULL | NULL   |
+-----+--------+------+--------+

It works because T2.ID2 = @id returns one row from table2 when @id is not NULL and returns no rows from table2 when @id is NULL.

With OPTION(RECOMPILE) optimiser can generate optimal plan in both cases. You can see actual plans and confirm for yourself that in the second case when @id is NULL optimiser is smart enough to not touch table2 at all, because it knows that T2.ID2 = NULL is always false.