SQL Left Join Example

This article explains SQL LEFT JOIN syntax and gives an example on how to use LEFT JOIN

Left join returns all the values from the left table, plus matched values from the right table. If there are no matches in the right table for given record from the left table, the resulting value will be returned as NULL.

LEFT JOIN and LEFT OUTER JOIN are the same in terms of terminology.

You can use following links to view the different types of SQL JOINs :

1. Inner Join
2. Left Join
3. Right Join
4. Outer Join

Left Join Visual Representation

Left Join Syntax

This query will return all of the records in the left table (table A) regardless if any of those records have a match in the right table (table B)

SELECT Table_A.column1, Table_B.column2...
FROM Table_A A
LEFT JOIN Table_B B
ON A.Key = B.Key

Left Join Example

We will create 3 tables

1. CUSTOMER
2. PRODUCT
3. ORDER

A customer can order products. In the ORDER table we hold the customer ID and the quantity of each product the customer has ordered.

Use following SQL scripts to create the three tables.

Create CUSTOMER table

CREATE TABLE `CUSTOMER` (
  `id` INT NOT NULL,
  `name` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`id`));

Create PRODUCT table

CREATE TABLE `PRODUCT` (
  `id` INT NOT NULL,
  `name` VARCHAR(45) NOT NULL,
  `price` DECIMAL(7,2) NOT NULL,
  PRIMARY KEY (`id`));

Create ORDER table

CREATE TABLE `ORDER` (
  `id` INT NOT NULL,
  `date` DATETIME NOT NULL,
  `customer_id` INT NOT NULL,
  `product_id` INT NOT NULL,
  `quantity` INT NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `product_id_idx` (`product_id` ASC) VISIBLE,
  INDEX `customer_id_idx` (`customer_id` ASC) VISIBLE,
  CONSTRAINT `customer_id`
    FOREIGN KEY (`customer_id`)
    REFERENCES `CUSTOMER` (`id`),
  CONSTRAINT `product_id`
    FOREIGN KEY (`product_id`)
    REFERENCES `PRODUCT` (`id`));

Insert data in CUSTOMER table

INSERT INTO `CUSTOMER` (`id`, `name`) VALUES ('1', 'Jon Snow');
INSERT INTO `CUSTOMER` (`id`, `name`) VALUES ('2', 'Daenerys Targaryen');
INSERT INTO `CUSTOMER` (`id`, `name`) VALUES ('3', 'Sansa Stark');
INSERT INTO `CUSTOMER` (`id`, `name`) VALUES ('4', 'Arya Stark');
INSERT INTO `CUSTOMER` (`id`, `name`) VALUES ('5', 'Jorah Mormont');
INSERT INTO `CUSTOMER` (`id`, `name`) VALUES ('6', 'Bronn of the Blackwater');

Insert data in PRODUCT table

INSERT INTO `PRODUCT` (`id`, `name`, `price`) VALUES ('1', 'Dragon', '5000');
INSERT INTO `PRODUCT` (`id`, `name`, `price`) VALUES ('2', 'Castle', '1000');
INSERT INTO `PRODUCT` (`id`, `name`, `price`) VALUES ('3', 'Sword', '5');

Insert data in ORDER table

INSERT INTO `ORDER` (`id`, `date`, `customer_id`, `product_id`, `quantity`) VALUES ('1', '2019-01-08 00:00:00', '2', '1', '3');
INSERT INTO `ORDER` (`id`, `date`, `customer_id`, `product_id`, `quantity`) VALUES ('2', '2019-01-22 00:00:00', '6', '3', '1');
INSERT INTO `ORDER` (`id`, `date`, `customer_id`, `product_id`, `quantity`) VALUES ('3', '2019-02-15 00:00:00', '6', '2', '1');
INSERT INTO `ORDER` (`id`, `date`, `customer_id`, `product_id`, `quantity`) VALUES ('4', '2019-02-16 00:00:00', '1', '3', '1');

Database tables

And now that’s what we have in our tables:

Left join tables

Now, let us join these tables using LEFT JOIN

This query returns customer details and purchase date

SELECT `CUSTOMER`.id, `CUSTOMER`.name, `ORDER`.date
FROM `CUSTOMER`
LEFT JOIN `ORDER` 
ON `CUSTOMER`.id = `ORDER`.customer_id;

Disclaimer: The example shown above has been tested with MySQL. Depending on your SQL database the CREATE TABLE syntax may vary.