MySQL INSERT with LEFT JOIN

Education is not limited to just classrooms. It can be gained anytime, anywhere... - Ravi Ranjan (M.Tech-NIT)

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

+---------+-------------------------------------+-------------+---------+--------+--------+------------+----------+---------+------------+
| book_id | book_name                           | isbn_no     | cate_id | aut_id | pub_id | dt_of_pub  | pub_lang | no_page | book_price |
+---------+-------------------------------------+-------------+---------+--------+--------+------------+----------+---------+------------+
| BK001   | Introduction to Electrodynamics     | 0000979001  | CA001   | AUT001 | P003   | 2001-05-08 | English  |     201 |      85.00 |
| BK002   | Understanding of Steel Construction | 0000979002  | CA002   | AUT002 | P001   | 2003-07-15 | English  |     300 |     105.50 |
| BK003   | Guide to Networking                 | 0000979003  | CA003   | AUT003 | P002   | 2002-09-10 | Hindi    |     510 |     200.00 |
| BK004   | Transfer  of Heat and Mass          | 0000979004  | CA002   | AUT004 | P004   | 2004-02-16 | English  |     600 |     250.00 |
| BK005   | Conceptual Physics                  | 0000979005  | CA001   | AUT005 | P006   | 2003-07-16 | NULL     |     345 |     145.00 |
| BK006   | Fundamentals of Heat                | 0000979006  | CA001   | AUT006 | P005   | 2003-08-10 | German   |     247 |     112.00 |
| BK007   | Advanced 3d Graphics                | 0000979007  | CA003   | AUT007 | P002   | 2004-02-16 | Hindi    |     165 |      56.00 |
| BK008   | Human Anatomy                       | 0000979008  | CA005   | AUT008 | P006   | 2001-05-17 | German   |      88 |      50.50 |
| BK009   | Mental Health Nursing               | 0000979009  | CA005   | AUT009 | P007   | 2004-02-10 | English  |     350 |     145.00 |
| BK010   | Fundamentals of Thermodynamics      | 0000979010  | CA002   | AUT010 | P007   | 2002-10-14 | English  |     400 |     225.00 |
| BK011   | The Experimental Analysis of Cat    | 0000979011  | CA004   | AUT011 | P005   | 2007-06-09 | French   |     225 |      95.00 |
| BK012   | The Nature  of World                | 0000979012  | CA004   | AUT005 | P008   | 2005-12-20 | English  |     350 |      88.00 |
| BK013   | Environment a Sustainable Future    | 0000979013  | CA004   | AUT012 | P001   | 2003-10-27 | German   |     165 |     100.00 |
| BK014   | Concepts in Health                  | 0000979014  | CA005   | AUT013 | P004   | 2001-08-25 | NULL     |     320 |     180.00 |
| BK015   | Anatomy & Physiology                | 0000979015  | CA005   | AUT014 | P008   | 2000-10-10 | Hindi    |     225 |     135.00 |
| BK016   | Networks and Telecommunications     | 00009790_16 | CA003   | AUT015 | P003   | 2002-01-01 | French   |      95 |      45.00 |
+---------+-------------------------------------+-------------+---------+--------+--------+------------+----------+---------+------------+

Sample table : author

+--------+----------------------+-----------+----------------+
| aut_id | aut_name             | country   | home_city      |
+--------+----------------------+-----------+----------------+
| AUT001 | William Norton       | UK        | Cambridge      |
| AUT002 | William Maugham      | Canada    | Toronto        |
| AUT003 | William Anthony      | UK        | Leeds          |
| AUT004 | S.B.Swaminathan      | India     | Bangalore      |
| AUT005 | Thomas Morgan        | Germany   | Arnsberg       |
| AUT006 | Thomas Merton        | USA       | New York       |
| AUT007 | Piers Gibson         | UK        | London         |
| AUT008 | Nikolai Dewey        | USA       | Atlanta        |
| AUT009 | Marquis de Ellis     | Brazil    | Rio De Janerio |
| AUT010 | Joseph Milton        | USA       | Houston        |
| AUT011 | John Betjeman Hunter | Australia | Sydney         |
| AUT012 | Evan Hayek           | Canada    | Vancouver      |
| AUT013 | E. Howard            | Australia | Adelaide       |
| AUT014 | C. J. Wilde          | UK        | London         |
| AUT015 | Butler Andre         | USA       | Florida        |
+--------+----------------------+-----------+----------------+

Code :

  1. INSERT INTO  authorinfo   
  2. SELECT book_mast.aut_id,book_mast.book_name,author.aut_name,author.country   
  3. FROM book_mast   
  4. LEFT JOIN author  
  5. ON book_mast.aut_id=author.aut_id;  

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 -

  1. SELECT   aut_id,book_name,aut_name    
  2. 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)