SQL Server query with same functionality as Excel VLookup

Question

I have 2 columns of data that I need to compare with each other - Column A and Column B.

Column A:

Steve
Jane
Mary
Peter
Ed
Scott
Ted

Column B:

Peter
Scott
David
Nancy

Column A has a larger amount of data than column B.
But it may not have all the values in column B.

I need to find out which of the values in column B are also in column A.

Output expected for above sample data:

Peter   TRUE
Scott   TRUE
David   FALSE
Nancy   FALSE

Need to use SQL Server / T-SQL to get this output.
Column A and Column B are fields in 2 separate tables
There are no other columns in the 2 tables

Thanks for all your help!

score 10 · Accepted Answer · answered May 09 '13 at 23:04

10

select 
   b.columnb,
   case when a.columna is null then 'FALSE' else 'TRUE' end 

from
   tableb b left outer join
   tablea a on b.columnb = a.columna

answered May 09 '13 at 23:04

ExactaBox

3,235
16
27

Gordon Linoff · Answer 2 · 2013-05-09T23:35:15.960

5

The problem with a left join is that there might be duplicates in table A.

If this is an issue, you can do this:

select b.col, (case when a.val is NULL then 'FALSE' else 'TRUE' end)
from b left outer join
     (select distinct a.val
      from a
     ) a
     on b.col = a.val;

An alternative way of expressing this is using a correlated subquery. This puts all the logic in the select:

select b.col,
       (case when exists (select 1 from a where a.val = b.col)
             then 'TRUE'
             else 'FALSE'
       end)
from b

edited May 09 '13 at 23:35

answered May 09 '13 at 23:10

Gordon Linoff

1,242,037
58
646
786

This possibility wasn't mentioned in the original question, so it doesn't really deserve an answer tailored for it. In any case, why do you assume that if the original list contains two mentions of a specific name, that the output should only include one mention of that name? And why wouldn't you simply do a SELECT DISTINCT on the base query (instead of using a sub-query)? – ExactaBox May 09 '13 at 23:17
1

@ExactaBox . . . The question is about how to get functionality that resembles VLOOKUP (actually it is more like ISNUMBER(MATCH())). That function *never* duplicates rows, and neither should a SQL equivalent. Putting the distinct in the outer select doesn't work, because you might have duplicates in the first table, and you do want to retain those. – Gordon Linoff May 09 '13 at 23:21
OK I get what you are saying. I'm assuming Column A is in Table A, Column B is in Table B. If table A has duplicates, I agree. But your answer says "there might be duplicates in Table B." We're just mixing up the source list and the list being looked up. – ExactaBox May 09 '13 at 23:30
@ExactaBox . . . I did answer the question backwards. Thank you for the clarification. I think I reversed all the a's and b's in the solution. – Gordon Linoff May 09 '13 at 23:35
Thanks for the answer and for thinking about duplicates as well. I had considered this separately. The second piece of code worked fine. Thanks! – slayernoah May 13 '13 at 14:58

SQL Server query with same functionality as Excel VLookup

2 Answers2

Linked