MySQL Tutorial MySQL Advanced MySQL Database Account Management MySQL References

MySQL FULL JOIN



MySQL does not support FULL JOIN keyword. But, a FULL JOIN on two tables can be achieved by using three keywords: LEFT JOIN, RIGHT JOIN and UNION keywords.

Example:

Consider a database containing tables called Employee and Contact_Info with the following records:

Table 1: Employee table

EmpIDNameCityAgeSalary
1JohnLondon253000
2MarryNew York242750
3JoParis272800
4KimAmsterdam303100
5RameshNew Delhi283000
6HuangBeijing282800

Table 2: Contact_Info table

Phone_NumberEmpIDAddressGender
+1-80XXXXX0002XXX, Brooklyn, New York, USAF
+33-14XXXXX013XXX, Grenelle, Paris, FranceM
+31-20XXXXX194XXX, Geuzenveld, Amsterdam, NetherlandsF
+86-10XXXXX4586XXX, Yizhuangzhen, Beijing, ChinaM
+65-67XXXXX47XXX, Yishun, SingaporeM
+81-35XXXXX728XXX, Koto City, Tokyo, JapanM

  • To implement full join in MySQL with Employee and Contact_Info tables based on matching column EmpID, the query is given below. This will fetch Name and Age columns from Employee table and Address column from Contact_Info table.

    SELECT Employee.Name, Employee.Age, Contact_Info.Address 
    FROM Employee
    LEFT JOIN Contact_Info
    ON Employee.EmpID = Contact_Info.EmpID
    
    UNION
    
    SELECT Employee.Name, Employee.Age, Contact_Info.Address 
    FROM Employee
    RIGHT JOIN Contact_Info
    ON Employee.EmpID = Contact_Info.EmpID;
    

    This will produce the result as shown below:

    NameAgeAddress
    John25
    Marry24XXX, Brooklyn, New York, USA
    Jo27XXX, Grenelle, Paris, France
    Kim30XXX, Geuzenveld, Amsterdam, Netherlands
    Ramesh28
    Huang28XXX, Yizhuangzhen, Beijing, China
    XXX, Yishun, Singapore
    XXX, Koto City, Tokyo, Japan
  • To fetch all fields of a table, table.* keyword is used, for example - to fetch all fields of the Employee table, Employee.* is used in the query given below:

    SELECT Employee.*, Contact_Info.Address 
    FROM Employee
    LEFT JOIN Contact_Info
    ON Employee.EmpID = Contact_Info.EmpID
    
    UNION
    
    SELECT Employee.*, Contact_Info.Address 
    FROM Employee
    RIGHT JOIN Contact_Info
    ON Employee.EmpID = Contact_Info.EmpID;
    

    This result of the following code will be:

    EmpIDNameCityAgeSalaryAddress
    1JohnLondon253000
    2MarryNew York242750XXX, Brooklyn, New York, USA
    3JoParis272800XXX, Grenelle, Paris, France
    4KimAmsterdam303100XXX, Geuzenveld, Amsterdam, Netherlands
    5RameshNew Delhi283000
    6HuangChina282800XXX, Yizhuangzhen, Beijing, China
    XXX, Yishun, Singapore
    XXX, Koto City, Tokyo, Japan

5