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
- Weekly Trends
- Python Interview Questions and Answers: Comprehensive Guide
- Scala Exercises, Practice, Solution
- Kotlin Exercises practice with solution
- MongoDB Exercises, Practice, Solution
- SQL Exercises, Practice, Solution - JOINS
- Java Basic Programming Exercises
- SQL Subqueries
- Adventureworks Database Exercises
- C# Sharp Basic Exercises
- SQL COUNT() with distinct
- JavaScript String Exercises
- JavaScript HTML Form Validation
- Java Collection Exercises
- SQL COUNT() function
- SQL Inner Join