SQL Server query with same functionality as Excel VLookup

sql sql-server excel vlookup data-comparison
slayernoah picture slayernoah · May 10, 2013 · Viewed 66.3k times · Source

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!

Answer

ExactaBox picture ExactaBox · May 10, 2013 
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

Related questions

Export SQL query data to Excel

I have a query that returns a very large data set. I cannot copy and paste it into Excel which I usually do. I have been doing some research on how to export directly to an Excel sheet. I am …

Read More
SQL Server export to Excel with OPENROWSET

I am successfully exporting to excel with the following statement: insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:\template.xls;', 'SELECT * FROM [SheetName$]') select * from myTable Is there any standard way to use this template specifying …

Read More
Import data from Excel Wizard automatically detects data types

Hello I'm trying to import data from excel file (xls) to new SQL table so I use Import and Export data 32/bit to achieve that. When I load the excel file it automatically detects data types of columns. e.g. …

Read More