8

I am new to SQL and if you have a spare moment, I was wondering whether anybody could help me replicate the Excel Vlookup function in SQL please?

From some research, I am suspecting that it is one of the join functions that I require, however, I don't want to just select data that is contained in both tables - I just want to lookup the value in 1 table against another.

If the data is contained in the lookup table then return the value and if not, just return NULL.

I have given a couple of example tables below to help illustrate my question.

Please note that Products 'C' and 'D' are not in Table2 but they are still in the result table but with NULL value.

Also I have a large number of unique products, so I am not looking for an answer which includes hard-coding, for example; CASE WHEN [Product] = 'A' THEN...



TABLE1

Product    Quantity
-------------------
A          10
B          41
D          2
C          5
B          16
A          19
C          17
A          21

TABLE 2

Product    Cost
-----------------
A          £31.45
B          £97.23



RESULT TABLE

Product   Quantity    Cost
-----------------------------
A         10          £31.45
B         41          £97.23
D         2           NULL
C         5           NULL
B         16          £97.23
A         19          £31.45
C         17          NULL
A         21          £31.45
dertkw
  • 7,798
  • 5
  • 37
  • 45
user3715274
  • 81
  • 1
  • 1
  • 2
  • 2
    "I was wondering whether anybody could help me replicate the Excel Vlookup function in SQL please?" = surely you could make an attempt? – Mitch Wheat Jun 08 '14 at 09:25

2 Answers2

10

It looks as if you need an outer join, I'll use a left one in my example:

select t1.Product, t1.Quantity, t2.Cost
from table1 as t1
left outer join table2 as t2
    on t1.Product = t2.Product

You can also leave out the outer keyword:

select t1.Product, t1.Quantity, t2.Cost
from table1 as t1
left join table2 as t2
    on t1.Product = t2.Product
Lennart - Slava Ukraini
  • 6,936
  • 1
  • 20
  • 32
  • 1
    If you want it to work correctly you need to add **and t2.Product <> ''** , otherwise you might get more records then you asked for. Because the join might duplicate if there's duplicates in the match column. – user890332 Aug 08 '19 at 14:44
  • 1
    @user890332, I'm not sure what situation your suggestion prevents, can you elaborate? – Lennart - Slava Ukraini Aug 08 '19 at 14:50
  • 1
    Actually my edit was not clear. I mean to say that left outer join will not return exactly the rows in the left table if there are duplicate rows in the right table. If you need an exact count ( which is what I needed ) then you need to find a way to narrow down to get only one row returned for each join. – user890332 Aug 09 '19 at 15:59
  • @user890332, Do you know of a way to do such a thing and have it remove duplicates from the result set? I have a different situation but still was curious if you have any insights. – Tim Wilcox Jan 04 '20 at 01:10
  • 1
    Use distinct (or group by)? – Lennart - Slava Ukraini Jan 04 '20 at 01:12
  • @Lennart, I get fewer duplicates but still quite a few. 79,000 or so – Tim Wilcox Jan 04 '20 at 01:21
  • 1
    Ask a new question, hard to tell without any details. Include create table and insert statements for sample data that Illustrated the problem – Lennart - Slava Ukraini Jan 04 '20 at 01:25
  • @TimWilcox, I ended up using group by which is not best solution but it works. Just use min() or max() or avg() on all the fields you need. – user890332 Jan 09 '20 at 15:48
  • @TimWilcox, I found a better way. Use row_number() over (partition by order by ) to create a unique row number for the column your joining by. Then wrap it in an outer select, where row_num = 1 or whatever number you want. This way you will only get one result. – user890332 Feb 28 '20 at 14:19
1

Here's an updated version of Lennart's answer which really works great.

select *
from table1 as t1
left outer join table2 as t2
    on t1.Product = t2.Product
    and t2.Product <> ''
left outer join table3 as t3
    on t1.Product = t3.Product2
    and t3.Product2 <> ''

The point is, you need to exclude rows where the join table column is blank, otherwise you will return way too many rows then table1 has. A true vlookup does not add any rows to the left table.

I even added a third table for effect.

user890332
  • 1,315
  • 15
  • 15