TRUE or FALSE in column after LEFT JOIN

Question

Let's say I have table A and table B.
A has 5 columns: F, G, H, I and X.
B has 1 column: X.
X has the same kind of values in both A and B.

Say I run the following.

SELECT *  
FROM A  
LEFT JOIN B  
ON A.X = B.X

The resulting data set has 6 columns. Sometimes, however, because of the LEFT JOIN, the data in the row from table B is NULL.

How to return * from A but to have an extra (sixth) column in the result set that shows 'Yes' if the join did find a match and 'No' if it didn't?

BStateham · Answer 1 · 2012-12-12T05:49:15.997

7

Does this work?

SELECT 
  A.*,
  CASE
    WHEN B.X IS NULL THEN 'No'
    ELSE 'Yes'
  END AS BExists
FROM A 
LEFT JOIN B 
ON A.X = B.X

If A.X and/or B.X can be null you might change it to something like this:

SELECT 
  A.*,
  CASE
    WHEN B.Id IS NULL THEN 'No'
    ELSE 'Yes'
  END AS BExists
FROM A 
LEFT JOIN B 
ON ISNULL(A.X,0) = ISNULL(B.X,0)

edited Dec 12 '12 at 05:49

answered Dec 12 '12 at 04:35

BStateham

My only problem with your code up there is what happens if the actual data in B coming over because of the join were NULL. The NULL may result because of no match between A.X and B.X or it may actually be what is contained in X at certain places in B. – Schwanzenstueck Dec 12 '12 at 05:43
Do your test on a column that can't be null. For example, does table B have a primary key? The CASE would change to something like, CASE WHEN B.Id IS NULL THEN 'No' ELSE 'Yes' END... Also, if A.X and B.X can be null, those won't be matched by the ON A.X = B.X. If you need to tree nulls as equal you might change it to IsNull(A.X,0) = IsNull(B.X,0). That will replace A.X and B.X with zero (0) if they are null, for comparison purposes only. – BStateham Dec 12 '12 at 05:46

score 0 · Answer 2 · edited Mar 31 '22 at 08:13

0

SELECT * FROM A 
LEFT OUTER JOIN B ON A.X = B.X

edited Mar 31 '22 at 08:13

RF1991

answered Dec 12 '12 at 04:36

Vikram Jain

No both are not same, Please execute both query and see difference,. – Vikram Jain Dec 12 '12 at 04:47
http://stackoverflow.com/questions/406294/left-join-and-left-outer-join-in-sql-server – Schwanzenstueck Dec 12 '12 at 04:49

2 Answers2