0

Say I have a simple query like this:

SELECT * from A JOIN B on B.refId = A.id
WHERE B.restriction = 123

I would like to change the behaviour as follows:

  • If B.restriction exists, the query acts like the above
  • If B.restriction is NULL (or possibly some specified 'magic' value) the clause is ignored

Is there a short, neat way to do this that doesn't rely on IF/ELSE type logic? I'm specifically using SqlServer (2012 at the oldest).

To clarify, it is allowed for B.restriction to be Null. A restriction can be added but if not specified, shouldn't get in the way hence wanting to 'ignore' the where clause.

Mr. Boy
  • 60,845
  • 93
  • 320
  • 589
  • 1
    That isn't how SQL works. If you want `NULL` values too you need to use an `OR` and an addition clause to check if the value `IS NULL`. – Thom A Dec 16 '21 at 15:26
  • I feel this must be a dupe but Idon't know what to google – Mr. Boy Dec 16 '21 at 15:26
  • Is it actually the column `B.restriction` is `NULL`, or is it that actually, there was no row found from the table `B`? – Thom A Dec 16 '21 at 15:28
  • @Larnu the row exists but no `restriction` has been set. If a restriction exists it must be observed but if not, we match all results. Slightly clunky client data for optional restrictions where we don't know all the possible values :) – Mr. Boy Dec 16 '21 at 15:29
  • I had been wondering if some sort of `NVL` type magic might work but I can't wrap my head around it (yes I know NVL is Oracle) – Mr. Boy Dec 16 '21 at 15:32
  • 2
    It's ill advised to use `ISNULL` (SQL Server's equivilent to `NVL`) on a column in your `WHERE`, as that would cause the query to be non-SARGable. – Thom A Dec 16 '21 at 15:34
  • Does this answer your question? [Why is my t-sql left join not working?](https://stackoverflow.com/questions/40093809/why-is-my-t-sql-left-join-not-working) Move the condition to the `ON` clause – Charlieface Dec 16 '21 at 16:20
  • The linked question _might_ be about a similar issue but it is at best the same answer to a different question. – Mr. Boy Dec 16 '21 at 17:15

1 Answers1

2

This depends on what the question you are actually asking is.

If the column restriction in the table B can be NULL, but a row found, you need to use an OR:

SELECT *
FROM dbo.A
     JOIN dbo.B ON B.refId = A.id
WHERE B.restriction = 123
   OR B.restriction IS NULL;

If actually what you want is for when rows aren't found but you only want rows where the value of restriction is 123 then use a LEFT JOIN and migrate the clause in your WHERE to the JOIN's ON:

SELECT *
FROM dbo.A
     LEFT JOIN dbo.B ON B.RefId = A.id
                    AND B.restriction = 123;
Thom A
  • 88,727
  • 11
  • 45
  • 75