CODEWITHSUNDEEP
sqlsql-serverjoin
André Reichelt
André Reichelt

Reputation: 1631

Return true (1), if a joined data entry exists, else false (0) in MS-SQL

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

Upvotes: 7

Views: 7262

Answers (2)

Sebastian Brosch
Sebastian Brosch

Reputation: 43594

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.

Upvotes: 3

gvee
gvee

Reputation: 17171

Personally I would do it this way:

Cast(CASE WHEN A.ID IS NULL THEN 0 ELSE 1 END AS bit) AS IsA

Upvotes: 2

Related Questions