7

I have an SQL statement that joins to two different tables in a 1 to 1 relationship each. For each of those joins, I want to return a BIT value (0 or 1), if the join was successful or not.

Let's say, we have a base table Base and the tables A and B, which are joined together via a LEFT JOIN on a common ID. I want to return the ID, as well as a field IsA and a field IsB.

What would be the best-practice solution to do this in Microsoft SQL Server most efficiently?

By the way, my current approach is this:

CAST(ISNULL(A.ID, 0) AS BIT) AS IsA,
CAST(ISNULL(B.ID, 0) AS BIT) AS IsB
iminiki
  • 2,549
  • 12
  • 35
  • 45
André Reichelt
  • 1,484
  • 18
  • 52
  • Your solution is short (which is good), but it relies on the fact that converting to bit promotes any nonzero value to 1 (which might be non-intuitive). I think that the CASE-WHEN-solutions proposed in the answers are a bit more readable. – Heinzi Sep 02 '19 at 08:02
  • 1
    Related: https://stackoverflow.com/q/6777910/87698 – Heinzi Sep 02 '19 at 08:43

2 Answers2

3

You can use the following, using CASE WHEN instead of ISNULL:

SELECT Base.*, A.id, B.id, 
  CAST(CASE WHEN A.id IS NULL THEN 0 ELSE 1 END AS BIT) AS IsA,
  CAST(CASE WHEN B.id IS NULL THEN 0 ELSE 1 END AS BIT) AS IsB
FROM Base LEFT JOIN A ON Base.id = A.base_id
  LEFT JOIN B ON Base.id = B.base_id

demo on dbfiddle.uk

This solution, compared to your current approach, has the same efficiency. But also see this answer (check multiple columns for NULL values). There you can see the ISNULL solution is less efficient. In your case it makes no big difference.

Also be careful: The ISNULL can also return 0 in case the column values is 0. So with your current approach you would get False in such a case.

Sebastian Brosch
  • 42,106
  • 15
  • 72
  • 87
2

Personally I would do it this way:

Cast(CASE WHEN A.ID IS NULL THEN 0 ELSE 1 END AS bit) AS IsA
gvee
  • 16,732
  • 35
  • 50
  • See @Sebastian's answer: Would that be more efficient than my current approach? – André Reichelt Sep 02 '19 at 07:54
  • 1
    @AndréReichelt - assuming you have indexes on the join fields and your scenario is as described then this will be as efficient as you can get it. The approach I have shared should be equal to yours but I would say that this is _clearer_ – gvee Sep 02 '19 at 07:57