SQL Inner Join Example

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

The most frequently used of the joins is the INNER JOIN. It creates a new result table by combining column values of two or more tables.

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

Inner Join Visual Representation

Inner Join Syntax

This query will return all of the records in the left table (table A) that have a matching record in the right table (table B). This Join is written as follows:

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

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

Inner join tables

Now, let us join these tables using INNER JOIN

This query returns customer details, purchase date and quantity

SELECT C.id, C.name, O.date, O.quantity
FROM `CUSTOMER` C
INNER JOIN `ORDER` O
ON C.id = O.customer_id;

Join Multiple Tables

Next example shows how to join 3 tables into one result set. Following query will return the whole purchase joined, displaying the name of the customer, date of purchase, the name and quantity of the product

SELECT C.name, O.date, P.name, O.quantity
FROM `ORDER` O 
JOIN `CUSTOMER` C ON O.customer_id = C.id 
JOIN `PRODUCT` P ON O.product_id = P.id;

The result of query execution:

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