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:

INSERT INTO  authorinfo 
SELECT book_mast.aut_id,book_mast.book_name,author.aut_name,author.country 
FROM book_mast 
LEFT JOIN author
ON book_mast.aut_id=author.aut_id;

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 -

SELECT   aut_id,book_name,aut_name  
FROM 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