Facebook Page Twitter Page LinkedIn Page
× SQL Q&A


First of all, the SQL FULL JOIN and SQL FULL OUTER JOIN keywords are same and MySQL does not have these keywords. But this can be achieved by using three keywords: LEFT JOIN, RIGHT JOIN and UNION keywords.

Example:

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

Table 1: Employee table

EmpIDNameAgeSalary
1John253000
2Marry242750
3Jo272800
4Kim303100
5Ramesh283000
6Huang282800

Table 2: Contact_Info table

Phone_NumberEmpIDAddress
+1-80XXXXX0002XXX, Brooklyn, New York, USA
+33-14XXXXX013XXX, Grenelle, Paris, France
+31-20XXXXX194XXX, Geuzenveld, Amsterdam, Netherlands
+86-10XXXXX4586XXX, Yizhuangzhen, Beijing, China
+65-67XXXXX47XXX, Yishun, Singapore
+81-35XXXXX728XXX, Koto City, Tokyo, Japan

  • To implement full join in MySQL with Employee and Contact_Info tables based on matching column EmpID, the SQL code 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
    John25NULL
    Marry24XXX, Brooklyn, New York, USA
    Jo27XXX, Grenelle, Paris, France
    Kim30XXX, Geuzenveld, Amsterdam, Netherlands
    Ramesh28NULL
    Huang28XXX, Yizhuangzhen, Beijing, China
    NULLNULLXXX, Yishun, Singapore
    NULLNULLXXX, 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 below SQL code:

    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:

    EmpIDNameAgeSalaryAddress
    1John253000NULL
    2Marry242750XXX, Brooklyn, New York, USA
    3Jo272800XXX, Grenelle, Paris, France
    4Kim303100XXX, Geuzenveld, Amsterdam, Netherlands
    5Ramesh283000NULL
    6Huang282800XXX, Yizhuangzhen, Beijing, China
    NULLNULLNULLNULLXXX, Yishun, Singapore
    NULLNULLNULLNULLXXX, Koto City, Tokyo, Japan

5