SQL version of VLOOKUP

Question

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

Answer 1

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