MySQL INSERT with LEFT JOIN

INSERT with LEFT JOIN

In this page, we have discussed how to insert values of one table into another table using MySQL INSERT INTO statement and MySQL LEFT JOIN.

The MySQL LEFT JOIN will preserve the records of the "left" table. MySQL starts with the left table and scans to the right table and store the value in the left table which matches the condition. For unmatched rows, it returns null. Each item in the left table will show up in a MySQL result, even if there isn't a match with the other table that it is being joined to.

Example:

Sample table: book_mast

Sample table: author

Code:

-- This SQL statement inserts data into the table authorinfo
INSERT INTO authorinfo 
-- Selecting specific columns from the tables book_mast and author
SELECT book_mast.aut_id, book_mast.book_name, author.aut_name, author.country 
-- From the table book_mast
FROM book_mast 
-- Performing a LEFT JOIN between the tables book_mast and author based on the aut_id column
LEFT JOIN author
-- Matching rows where the aut_id column in book_mast equals the aut_id column in author
ON book_mast.aut_id = author.aut_id;

Explanation:

• The purpose of this SQL code is to populate the authorinfo table with information extracted from the book_mast and author tables, joining them based on the aut_id column.


• INSERT INTO authorinfo: This line specifies the target table where the data will be inserted, which is authorinfo.


• SELECT book_mast.aut_id, book_mast.book_name, author.aut_name, author.country: This line specifies the columns that will be selected from the book_mast and author tables. Specifically, it selects the aut_id and book_name columns from book_mast, and the aut_name and country columns from author.


• FROM book_mast: This line specifies the source table from which the book_mast.aut_id and book_mast.book_name columns are being selected.


• LEFT JOIN author: This line performs a LEFT JOIN operation between the book_mast and author tables. It combines rows from book_mast with matching rows from author based on the aut_id column.


• ON book_mast.aut_id = author.aut_id: This line specifies the condition for the join, ensuring that rows are matched where the aut_id column in book_mast equals the aut_id column in author.

Relational Algebra Expression:

Relational Algebra Tree:

Explanation:

The above statement has performed the following operations -

1. the 'aut_id' and 'book_name' of 'book_mast' table and 'aut_name' and 'country' of 'author' table will join together based upon 'aut_id' columns of both of the tables,
2. and inserted into the 'authorinfo' table.

To see some specific columns from inserted rows here is the code below -

-- This SQL statement selects specific columns from the table authorinfo
SELECT aut_id, book_name, aut_name  
-- From the table authorinfo
FROM authorinfo;

Explanation:

• The purpose of this SQL code is to retrieve specific columns (aut_id, book_name, and aut_name) from the authorinfo table.


• SELECT aut_id, book_name, aut_name: This line specifies the columns that will be selected from the authorinfo table. Specifically, it selects the aut_id, book_name, and aut_name columns.


• FROM authorinfo: This line specifies the source table from which the columns are being selected, which is authorinfo.

Output:

mysql> SELECT   aut_id,book_name,aut_name  
    -> FROM authorinfo;
+--------+-------------------------------------+----------------------+
| aut_id | book_name                           | aut_name             |
+--------+-------------------------------------+----------------------+
| AUT001 | Introduction to Electrodynamics     | William Norton       | 
| AUT002 | Understanding of Steel Construction | William Maugham      | 
| AUT003 | Guide to Networking                 | William Anthony      | 
| AUT004 | Transfer  of Heat and Mass          | S.B.Swaminathan      | 
| AUT005 | Conceptual Physics                  | Thomas Morgan        | 
| AUT006 | Fundamentals of Heat                | Thomas Merton        | 
| AUT007 | Advanced 3d Graphics                | Piers Gibson         | 
| AUT008 | Human Anatomy                       | Nikolai Dewey        | 
| AUT009 | Mental Health Nursing               | Marquis de Ellis     | 
| AUT010 | Fundamentals of Thermodynamics      | Joseph Milton        | 
| AUT011 | The Experimental Analysis of Cat    | John Betjeman Hunter | 
| AUT005 | The Nature  of World                | Thomas Morgan        | 
| AUT012 | Environment a Sustainable Future    | Evan Hayek           | 
| AUT013 | Concepts in Health                  | E. Howard            | 
| AUT014 | Anatomy & Physiology                | C. J. Wilde          | 
| AUT015 | Networks and Telecommunications     | Butler Andre         | 
+--------+-------------------------------------+----------------------+
16 rows in set (0.00 sec)

Previous: INSERT records with GROUP BY and ORDER BY
Next: MySQL Update