Perform MySQL LEFT JOIN on two tables?

MySQLMySQLi Database

Let us first create two tables and join them with foreign key constraint. The query to create the first table is as follows −

mysql> create table ParentTable
   -> (
   -> UniqueId int NOT NULL AUTO_INCREMENT PRIMARY KEY,
   -> EmployeeName varchar(10)
   -> );
Query OK, 0 rows affected (0.56 sec)

Insert some records in the first table using insert command. The query is as follows −

mysql> insert into ParentTable(EmployeeName) values('John');
Query OK, 1 row affected (0.15 sec)
mysql> insert into ParentTable(EmployeeName) values('Carol');
Query OK, 1 row affected (0.32 sec)
mysql> insert into ParentTable(EmployeeName) values('Sam');
Query OK, 1 row affected (0.18 sec)
mysql> insert into ParentTable(EmployeeName) values('Bob');
Query OK, 1 row affected (0.19 sec)

Now you can display all records from the table using select statement. The query is as follows −

mysql> select *from ParentTable;

The following is the output −

+----------+--------------+
| UniqueId | EmployeeName |
+----------+--------------+
|        1 | John         |
|        2 | Carol        |
|        3 | Sam          |
|        4 | Bob          |
+----------+--------------+
4 rows in set (0.00 sec)

The query to create the second table with foreign key constraints is as follows −

mysql> create table ChildTable
   -> (
   -> UniqueId int NOT NULL PRIMARY KEY,
   -> EmployeeAddress varchar(100),
   -> CONSTRAINT fk_uniqueId FOREIGN KEY(UniqueId) references ParentTable(UniqueId)
   -> );
Query OK, 0 rows affected (0.54 sec)

Now insert some records in the second table using insert command. The query is as follows −

mysql> insert into ChildTable values(1,'15 West Shady Lane Starkville, MS 39759');
Query OK, 1 row affected (0.19 sec)
mysql> insert into ChildTable values(2,'72 West Rock Creek St. Oxford, MS 38655');
Query OK, 1 row affected (0.18 sec)
mysql> insert into ChildTable(UniqueId) values(3);
Query OK, 1 row affected (0.41 sec)
mysql> insert into ChildTable values(4,'119 North Sierra St. Marysville, OH 43040');
Query OK, 1 row affected (0.16 sec)

Display all records from the table using select statement. The query is as follows −

mysql> select *from ChildTable;

The following is the output −

+----------+-------------------------------------------+
| UniqueId | EmployeeAddress                           |
+----------+-------------------------------------------+
|        1 | 15 West Shady Lane Starkville, MS 39759   |
|        2 | 72 West Rock Creek St. Oxford, MS 38655   |
|        3 | NULL                                      |
|        4 | 119 North Sierra St. Marysville, OH 43040 |
+----------+-------------------------------------------+
4 rows in set (0.00 sec)

Let us now join the tables using left join. The query is as follows −

mysql> select
ParentTable.UniqueId,ParentTable.EmployeeName,ChildTable.EmployeeAddress from ParentTable left join
   -> ChildTable on ParentTable.UniqueId=ChildTable.UniqueId;

The following is the output −

+----------+--------------+-------------------------------------------+
| UniqueId | EmployeeName | EmployeeAddress                           |
+----------+--------------+-------------------------------------------+
|        1 | John         | 15 West Shady Lane Starkville, MS 39759   |
|        2 | Carol        | 72 West Rock Creek St. Oxford, MS 38655   |
|        3 | Sam          | NULL                                      |
|        4 | Bob          | 119 North Sierra St. Marysville, OH 43040 |
+----------+--------------+-------------------------------------------+
4 rows in set (0.00 sec)
raja
Published on 26-Feb-2019 13:16:41
Advertisements