CODEWITHSUNDEEP
sqljoinlookupvlookup
user3715274
user3715274

Reputation: 81

SQL version of VLOOKUP

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

Upvotes: 8

Views: 78883

Answers (2)

user890332
user890332

Reputation: 1361

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.

Upvotes: 1

Lennart - Slava Ukraini
Lennart - Slava Ukraini

Reputation: 7171

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

Upvotes: 11

Related Questions