LEFT JOIN using SQL (examples included)

February 12, 2022

You can use a LEFT JOIN to get all the records from the left table, and the records from the right table where there is a match (if there is no match, you’ll get NULL under the selected fields from the right table):

SELECT 
tb1.*,
tb2.*
FROM left_table_name tb1
LEFT JOIN right_table_name tb2 ON tb1.id = tb2.id

Let’s see how to apply a left join by reviewing few examples.

Applying Left Join Between Two Tables using SQL

In the following example, you’ll see how to apply a left join between:

  • The left table called products; and
  • The right table called prices

You can create the ‘products‘ table using the following query:

CREATE TABLE products (
	product_id int primary key,
	product_name nvarchar(50)
)

And then insert 5 records into the table:

INSERT INTO products (product_id, product_name)

VALUES

(1, 'Laptop'),
(2, 'Tablet'),
(3, 'Monitor'),
(4, 'Printer'),
(5, 'Keyboard')

You can then create the ‘prices‘ table using the following syntax:

CREATE TABLE prices (
	product_id int primary key,
	product_price int
)

Then, insert 5 records into the table:

INSERT INTO prices (product_id, product_price)

VALUES

(1, 1200),
(2, 300),
(3, 500),
(4, 150),
(5, 80)

Finally, you can apply a LEFT JOIN using the query below. Note that the product_id field (which exists in both tables) will be used to link the tables together:

SELECT 
tb_1.*,
tb_2.*
FROM products tb_1
LEFT JOIN prices tb_2 ON tb_1.product_id = tb_2.product_id

In green, you’ll get all the records and fields from the products table (the “left” table), and in yellow you’ll get all the records and fields from the prices table (the “right” table):

product_id product_name product_id product_price
1 Laptop 1 1200
2 Tablet 2 300
3 Monitor 3 500
4 Printer 4 150
5 Keyboard 5 80

Selecting subset of fields

At times, you may need to select a subset of fields from the joined tables.
For example, let’s select:

  • The product_id and product_name fields from the ‘products’ table; and
  • The product_price field from the ‘prices’ table

Here is the query:

SELECT 
tb_1.product_id,
tb_1.product_name,
tb_2.product_price
FROM products tb_1
LEFT JOIN prices tb_2 ON tb_1.product_id = tb_2.product_id

You’ll now see only the selected fields from the tables:

product_id product_name product_price
1 Laptop 1200
2 Tablet 300
3 Monitor 500
4 Printer 150
5 Keyboard 80

Applying Left Join Across Multiple Tables using SQL

Let’s now create a third table called ‘brands‘:

CREATE TABLE brands (
	product_id int primary key,
	product_brand nvarchar(50)
)

And then insert 5 records:

INSERT INTO brands (product_id, product_brand)

VALUES

(1, 'DD'),
(2, 'HH'),
(3, 'MM'),
(4, 'PP'),
(5, 'RR')

Suppose that you want to perform a left join across multiple tables (for our examples, the 3 tables are: products, prices and brands).

In that case, you can run the following query:

SELECT 
tb_1.product_id,
tb_1.product_name,
tb_2.product_price,
tb_3.product_brand
FROM products tb_1
LEFT JOIN prices tb_2 ON tb_1.product_id = tb_2.product_id
LEFT JOIN brands tb_3 ON tb_1.product_id = tb_3.product_id

As you can see, the first and second fields were selected from the products table (in green), the third field was selected from the prices table (in yellow), while the fourth field was selected from the brands table (in orange):

product_id product_name product_price product_brand
1 Laptop 1200 DD
2 Tablet 300 HH
3 Monitor 500 MM
4 Printer 150 PP
5 Keyboard 80 RR

Another way to write the above query:

SELECT 
tb_1.product_id,
tb_1.product_name,
tb_2.product_price,
tb_3.product_brand
FROM

	(
	SELECT 
	* 
	FROM products
	) tb_1

LEFT JOIN

	((
	SELECT 
	* 
	FROM prices
	)) tb_2 ON tb_1.product_id = tb_2.product_id

LEFT JOIN

	(((
	SELECT 
	* 
	FROM brands
	))) tb_3 ON tb_1.product_id = tb_3.product_id

You’ll get the same results as before:

product_id product_name product_price product_brand
1 Laptop 1200 DD
2 Tablet 300 HH
3 Monitor 500 MM
4 Printer 150 PP
5 Keyboard 80 RR

And yet another way to write the above query:

SELECT 
mix.product_id,
mix.product_name,
mix.product_price,
tb_3.product_brand
FROM

	(
	SELECT 
	tb_1.product_id,
	tb_1.product_name,
	tb_2.product_price
	FROM products tb_1
	LEFT JOIN prices tb_2 ON tb_1.product_id = tb_2.product_id
	) mix

LEFT JOIN

	(((
	SELECT 
	* 
	FROM brands
	))) tb_3 ON mix.product_id = tb_3.product_id

You’ll see the same results again:

product_id product_name product_price product_brand
1 Laptop 1200 DD
2 Tablet 300 HH
3 Monitor 500 MM
4 Printer 150 PP
5 Keyboard 80 RR