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-80540980002Brooklyn, New York, USAF
+33-1479961013Grenelle, Paris, FranceM
+31-2011503194Geuzenveld, Amsterdam, NetherlandsF
+86-10997324586Yizhuangzhen, Beijing, ChinaM
+65-672348247Yishun, SingaporeM
+81-3577990728Koto 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
    Marry24Brooklyn, New York, USA
    Jo27Grenelle, Paris, France
    Kim30Geuzenveld, Amsterdam, Netherlands
    Ramesh28
    Huang28Yizhuangzhen, Beijing, China
    Yishun, Singapore
    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 York242750Brooklyn, New York, USA
    3JoParis272800Grenelle, Paris, France
    4KimAmsterdam303100Geuzenveld, Amsterdam, Netherlands
    5RameshNew Delhi283000
    6HuangChina282800Yizhuangzhen, Beijing, China
    Yishun, Singapore
    Koto City, Tokyo, Japan