MySQL FULL JOIN

In the SQL language, the FULL JOIN (or FULL OUTER JOIN) command allows making a join between two tables. The use of this command allows combining the results of the two tables, associating them with each other using a condition, and filling with NULL values if the condition is not respected.

 
 

 

Syntax:

To return the rows of table1 and table2, it is necessary to use an SQL query with a syntax like this one:

SELECT *
FROM table1
FULL JOIN table2 ON table1.id = table2.fk_id

This query can also be written in this way:

SELECT *
FROM table1
FULL OUTER JOIN table2 ON table1.id = table2.fk_id

The condition shown here is to link the tables on an id, but the condition can be defined on other fields.
 

Example :

Let’s take the following example of a database that contains a “Users” table and “Orders” table that contains all the sales.
 
Users table:

> SELECT * FROM Users;

+------------+-----------+--------+-------------------------------+
|   UserID   |    Name   |  age   |             Address           |
+------------+-----------+--------+-------------------------------+
|     1      |    Alex   |   25   | 819 Saint Francis Way         |
|     2      |   Emily   |   15   | 171 Jarvisville Road Michigan |
|     3      |   Jean    |   35   | 188 Clay Street Indiana       |
|     4      |    Bob    |   40   | 285 Java Lane Missouri        |
+------------+-----------+--------+-------------------------------+

 

 
Orders table:

> SELECT * FROM Orders;

+---------+-----------+----------+-------------+
| OrderID |  NumOrder |   Total  |   UserID    |
+---------+-----------+----------+-------------+
|     1   |   9901    |  254.00  |      1      |
|     2   |   9902    |  300.96  |      1      |
|     3   |   9903    |  145.03  |      4      |
|     4   |   9904    |  744.33  |      5      |
+---------+-----------+----------+-------------+

It is possible to use FULL JOIN to list all users who have or have not made an order, and to list all orders that are associated or not with a user. The SQL query is as follows:

SELECT UserID, Name, NumOrder, Total
FROM Users
FULL JOIN Orders ON Users.UserID = Orders.UserID

 
Output:

+---------+-----------+----------+-------------+
|  UserID |   Name    | NumOrder |    Total    |
+---------+-----------+----------+-------------+
|     1   |   Alex    |  9901    |  254.00     |
|     1   |   Alex    |  9902    |  300.96     |
|     2   |   Emily   |  NULL    |  NULL       |
|     3   |   Jean    |  NULL    |  NULL       |
|     4   |   Bob     |  9903    |  145.03     |
|   NULL  |   NULL    |  9904    |  744.33     |
+---------+-----------+----------+-------------+

This result shows users number 2 and 3 who have not made any purchases. The result also returns the order 9904 which is associated with a user that does not exist (or no longer exists). In cases where there is no match with the other table, the column values are NULL.
 

MCQPractice competitive and technical Multiple Choice Questions and Answers (MCQs) with simple and logical explanations to prepare for tests and interviews.Read More