VLOOKUP in SQL (Updated in 2025)

VLOOKUP is a popular Excel function that enables users to transfer data between tables or sheets without manually matching the data in different columns. This is a function you’ll likely use regularly if Excel is your primary platform for conducting data analysis. However, data analytics today mainly uses SQL. Is it possible to also use VLOOKUP in SQL?

SQL doesn’t support VLOOKUP, but you can replicate it using methods like INNER JOIN, LEFT JOIN, and WHERE. This guide covers performing VLOOKUP in SQL and key tips for analytics work.

In its most basic form, the VLOOKUP function retrieves (looks up) a specific data entry based on another data entry. For example, in a grocery store database, you can use VLOOKUP to return the price of a product based on the product code as shown below.

In the above example, the VLOOKUP function (shown in red) returns the price of the garden planter. The first argument (4) is the ID of the product whose price will be returned. The second argument defines the table in which the function should look, and the third argument specifies that the function should retrieve the value in the fourth column of the table defined in the third argument.

The standard VLOOKUP function contains the following arguments:

• lookup_value: This is the value the function looks for in the leftmost column in the table you’ll define. In our example, this was the product ID.
• table_array: This is the range of cells (or the table) that contains the data you are searching for. It must include the look_up value and the output column.
• col_index_num: This is the column’s index whose value will be returned. The index is counted from 1. In our example, the value returned was in the fourth column.
• range_lookup: This optional value can be set to TRUE (1) or FALSE (0). An approximate match is returned if set to TRUE, but only exact matches will be returned when set to FALSE. This will be expounded on below when the output includes multiple values.

Solving the above problem using SQL is relatively easy. Assuming the table name was product_price_list, you can return the price of the garden planter using the following SQL code:

SELECT Price FROM product_price_list WHERE ProductID=4;

Although VLOOKUP is useful for returning individual items of data, it is often used to transfer multiple entries or whole columns between tables. For instance:

You have two tables relating to company sales staff. One contains basic information, including phone numbers, while the other contains data on sales performance and bonuses. To quickly inform employees of their bonuses, it makes sense to transfer the phone numbers of each employee who qualifies for a bonus from the first table to the second one. Doing this helps you avoid going back and forth between the two tables.

This is a classic VLOOKUP problem for Excel users, but for SQL, you’ll need to use a JOIN statement.

VLOOKUP Solution

Table 1

Table 2

As you can see, no phone numbers are in the second table. This can be quickly changed for each employee using the VLOOKUP function shown below:

In the function VLOOKUP(A16:A25,$A$3:$D$12,4), the lookup_value is the range containing all the employee IDs, which can be substituted with individual employee IDs. The “$” signs keep the selected table range static when the formula is copied down the table. This is known as absolute referencing. Once the initial phone number is found, the formula can be copied downward in the typical Excel fashion.

SQL Solution (with INNER JOIN)

VLOOKUP offers a convenient solution but only works in Excel and other spreadsheet applications. There are also different ways of declaring the lookup_value and the table_array, which can lead to errors or confusion.

The same results can be achieved in SQL using an INNER JOIN, as shown below:

Step 1: Create the database and tables.

DROP DATABASE IF EXISTS `VLOOKUP_ETC`;
CREATE DATABASE `VLOOKUP_ETC`;
USE `VLOOKUP_ETC`;

CREATE TABLE employee_basic_info (
  employee_id INT NOT NULL,
  employee_name VARCHAR(50),
  address VARCHAR(50),
  phone_no VARCHAR(50),
  age INT(10),
  PRIMARY KEY (employee_id)
);

CREATE TABLE sales_performance (
  employee_id INT NOT NULL,
  employee_name VARCHAR(50),
  quarterly_sales_$ FLOAT,
  bonus_$ FLOAT
);

Step 2: Populate the tables.

INSERT INTO employee_basic_info (employee_id, employee_name, address, phone_no, age)
VALUES
(1,	'George Bishop', '85 Nimbus Drive',	'1 (740) 514-9360',	51),
(2,	'Tom Ludley', '6 Tomahawk RD', '1 (413) 884-4563', 36),
(3,	'Rebecca McDouglas', '18 Rodeo Drive', '1 (529) 212-7252', 41),
(4,	'Jerry Astley', '19 Albridge Lane',	'1 (982) 425-0023',	29),
(5,	'Sandy Perkins', '92 Albert Street', '1 (468) 818-4203', 34),
(6,	'Page Brooks', '16 Nimbus Drive', '1 (923) 805-7047', 23),
(7,	'Angela Warren', '12 Rooster Lane',	'1 (314) 657-2957',	53),
(8,	'Cindy Chow', '45 Duckwing Ave', '1 (199) 083-1605', 26),
(9,	'Donald Duckwing', '9 Layton Drive', '1 (316) 127-9364', 37),
(10, 'Austine Stevensons', '21 Albert Street', '1 (656) 331-0151', 40)
;

INSERT INTO sales_performance (employee_id, employee_name, quarterly_sales_$, bonus_$)
VALUES
(1,	'George Bishop', 48005,	1200),
(2,	'Tom Ludley', 94700, 2368),
(3,	'Rebecca McDouglas', 76580,	1915),
(4,	'Jerry Astley', 45000, 1125),
(5,	'Sandy Perkins', 102550, 2564),
(6,	'Page Brooks', 59350, 1484),
(7,	'Angela Warren', 37420,	936),
(8,	'Cindy Chow', 18450, 461),
(9,	'Donald Duckwing', 32850,	821),
(10, 'Austine Stevensons', 23000, 575)
;

Step 3: Combine information from the two tables using an INNER JOIN.

SELECT sales.employee_name, quarterly_sales_$, bonus_$, basics.phone_no
FROM sales_performance AS sales
INNER JOIN employee_basic_info AS basics
	ON sales.employee_id=basics.employee_id
;

The output of this query will be:

employee_name	quarterly_sales_$	bonus_$	phone_no
George Bishop	48005	1200	1 (740) 514-9360
Tom Ludley	94700	2368	1 (413) 884-4563
Rebecca McDouglas	76580	1915	1 (529) 212-7252
Jerry Astley	45000	1125	1 (982) 425-0023
Sandy Perkins	102550	2564	1 (468) 818-4203
Page Brooks	59350	1484	1 (923) 805-7047
Angela Warren	37420	936	1 (314) 657-2957
Cindy Chow	18450	461	1 (199) 083-1605
Donald Duckwing	32850	821	1 (316) 127-9364
Austine Stevensons	23000	575	1 (656) 331-0151

SQL Solution (with LEFT OUTER JOIN)

Although the INNER JOIN produced the same result as the VLOOKUP example above, there are cases when it won’t. If some of the sales table’s records don’t match those in the basic information table, an INNER JOIN will not display those without a match. For example, if the tables above are matched on names instead of employee ID and one of the names is blank, the output will only have nine records, as shown below, leaving one person without their bonus.

This type of error can easily go unnoticed in a table with hundreds of records. When the VLOOKUP function encounters this kind of error, and the optional range_lookup argument is set to FALSE (i.e., no approximations allowed), the output will be as shown below:

This ensures the mismatch between the two tables doesn’t go unnoticed. To get the same result in SQL, you should use the LEFT OUTER JOIN function instead. The code for this will be:

SELECT sales.employee_name, quarterly_sales_$, bonus_$, basics.phone_no
FROM sales_performance AS sales
LEFT OUTER JOIN employee_basic_info AS basics
	ON sales.employee_name=basics.employee_name
;

This will output the table shown below, clearly showing that one value is missing instead of eliminating the entire row and leaving no signs of missing information.

employee_name	quarterly_sales_$	bonus_$	phone_no
George Bishop	48005	1200	1 (740) 514-9360
Tom Ludley	94700	2368	1 (413) 884-4563
Rebecca McDouglas	76580	1915	1 (529) 212-7252
Jerry Astley	45000	1125	1 (982) 425-0023
Sandy Perkins	102550	2564	1 (468) 818-4203
Page Brooks	59350	1484	1 (923) 805-7047
Angela Warren	37420	936	NULL
Cindy Chow	18450	461	1 (199) 083-1605
Donald Duckwing	32850	821	1 (316) 127-9364
Austine Stevensons	23000	575	1 (656) 331-0151

VLOOKUP is often cited as one of the most useful functions in Excel, but with more people using SQL, there is a need for an alternative to this language. In SQL, you can use the WHERE clause, an INNER JOIN, or a LEFT OUTER JOIN to replicate the functionality of VLOOKUP. The best option among the three will be dictated by your desired result. When using an INNER JOIN, it’s important to remember that records that don’t match will be completely omitted from the results.

If you’re interested in improving your knowledge of SQL so you can handle data like a pro, Interview Query has plenty of resources to help. We have in-depth explainers for essential functions such as CASE WHEN and GROUP BY, and our SQL learning path can help you refresh your SQL skills by taking you through the basic and advanced concepts in this language. You can also try out the SQL interview questions if you’re preparing for an interview, or check out our company interview guides to see how different companies could test your SQL knowledge.

Excel functions made it easier to handle large-scale data processing in the past. If you’re new to SQL, we hope this guide will help you achieve the same results as VLOOKUP in SQL.