LEFT JOIN using SQL

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

Applying a 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):

Selecting subset of fields

At times, you may need to select a subset of fields from the joined tables.
For example, you can 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:

Applying a Left Join Across Multiple Tables using SQL

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):

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:

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: