MySQL Insert with Join

This article will look into how to insert rows to a MySQL table using the JOINs. JOINs help us retrieve records from more than one table that is logically related to each other.

Let us get started by making the sample data. We will be creating three tables employee_designation, employee_rating, and employee_details, followed by inserting a few rows into employee_designation and employee_rating table.

#create table employee_designation
CREATE TABLE employee_designation
(
  id int NOT NULL AUTO_INCREMENT,
  emp_id int DEFAULT NULL,
  emp_name varchar(255) DEFAULT NULL,
  emp_designation varchar(255) DEFAULT NULL,
  primary key(id)
);
# insert rows into table employee_designation
INSERT INTO employee_designation (emp_id,emp_name,emp_designation) 
 VALUES(1,"Henry","Assistant Manager"),
 (2,"Richa","Executive"),
 (3,"Veronica","Executive"),
 (4,"George","Executive"),
 (5,"Harsh","Assistant Manager"),
 (6,"William","Assistant Manager"),
 (7,"Rebecca","Senior Manager"),
 (8,"Paul","Assistant Manager"),
 (9,"Pualou","Executive");
#create table employee_rating
 CREATE TABLE employee_rating
(
   id int AUTO_INCREMENT,
   emp_id int,
   emp_name VARCHAR(255),
   emp_rating int,
   primary key(id)
);
#insert rows into table employee_rating
INSERT INTO employee_rating (emp_id,emp_name,emp_rating) 
 VALUES(1,"Henry",1),
 (2,"Richa",3),
 (3,"Veronica",4),
 (4,"George",1),
 (5,"Harsh",2),
 (6,"William",2),
 (7,"Rebecca",2),
 (8,"Paul",2);
#create table employee_detail
 CREATE TABLE employee_details
(
  emp_id int  NOT NULL AUTO_INCREMENT,
  emp_name varchar(255) DEFAULT NULL,
  emp_designation varchar(255) DEFAULT NULL,
  emp_rating int,
  primary key(emp_id)
);

To view the data in employee_designation, employee_rating table execute:

SELECT * FROM employee_designation;

Output:-

SELECT * FROM employee_rating;

Output:-

We will now look into the examples for a better understanding.

Example1: Insert records to employee_details table with columns emp_id, emp_name, emp_designation and emp_rating.

Observe the below code for the solution.

 INSERT INTO employee_details
  (emp_id, emp_name, emp_designation, emp_rating)
  SELECT
  ed.emp_id, ed.emp_name, ed.emp_designation, er.emp_rating 
  FROM employee_designation ed LEFT JOIN employee_rating er
  ON ed.emp_id = er.emp_id;  

Action Output:-

To view what got inserted to employee_details, let us execute:

 SELECT * FROM employee_details;

Output:-

Output in image_4 shows that all the columns from both the tables got inserted successfully.

Explanation:-

• The above insert query is fetching the values from tables employee_designation and employee_rating.
• Since the query uses INNER JOIN, the rows in the employee_details table are inserted till emp_id = 8. These are the rows common to both employee_designation and employee_rating tables.

Example2: Insert records to a temporary table employee_bonus with columns emp_name and emp_bonus. The employees with rating-1 get 10000$ as bonus, with rating-2 get 5000$, rating-3 gets 2000$, and rating-4 gets 0$.

Temporary tables are the MySQL tables which are created within a current session.

READ MORE : Create Temporary Table in MySQL

Observe the below solution.

#create temporary table employee_bonus
CREATE TEMPORARY TABLE employee_bonus
(
  id int auto_increment ,
  emp_name varchar(255),
  emp_bonus decimal(10,2),
  primary key(id)
);
#insert to employee_bonus using JOIN
 INSERT INTO employee_bonus
  (emp_name, emp_bonus)
  SELECT
  ed.emp_name,
      IF(er.emp_rating >3,0,
      IF(er.emp_rating =3 ,2000,
      IF(er.emp_rating =2 ,5000,
   10000)))
  FROM employee_designation ed INNER JOIN employee_rating er
  ON ed.emp_id = er.emp_id;    

Action Output:-

To view what got inserted to employee_bonus, let us execute:

 SELECT * FROM employee_bonus;

Output:-

Output in image_6 shows that the rows got inserted successfully.

READ MORE:

• MySQL: Insert in a loop
• INSERT record if NOT EXISTS in MySQL
• Insert into a MySQL table or update if exists
• MYSQL INSERT INTO
• MYSQL INSERT WITH SELECT

We hope this article helped you in understanding MySQL INSERT with JOIN. Good Luck!!!