A full join, also known as a complete outer join, is used to return all rows from all joined tables, regardless of whether they have matching records.
Background information
An outer join uses comparison operators to compare data in two tables. The join result includes not only rows that meet the join conditions but also rows that do not.
Outer joins include full join (FULL JOIN), left join (LEFT JOIN), and right join (RIGHT JOIN). An outer join returns all rows that meet the join conditions, and returns unmatched rows from one table with NULL filled in the corresponding positions of the other table.
This topic provides examples of full joins. For examples of left joins, see LEFT JOIN, and for examples related to right joins, see RIGHT JOIN.
FULL JOIN syntax
An outer join statement generally includes a left table and a right table. The table appearing farthest to the left in the JOIN clause is the left table, and the table appearing farthest to the right is the right table.
The syntax of FULL JOIN is as follows:
SELECT select_list FROM table_name1 FULL JOIN table_name2 ON join_condition
[ WHERE query_condition ]
[ ORDER BY column_list ];
Here, table_name1 is the left table, and table_name2 is the right table.
Examples
Create sample tables and insert appropriate data.
obclient [info]> CREATE TABLE tbl_a(id INT NOT NULL PRIMARY KEY, name VARCHAR(50));
Query OK, 0 rows affected
obclient [info]> CREATE TABLE tbl_b(number INT NOT NULL PRIMARY KEY, value INT);
Query OK, 0 rows affected
obclient [info]> INSERT INTO tbl_a VALUES(1,'ab'),(2,'cd'),(3,'ef'),(4,'gh');
Query OK, 6 rows affected
Records: 6 Duplicates: 0 Warnings: 0
obclient [info]> INSERT INTO tbl_b VALUES(1,1001),(3,1003),(5,1005);
Query OK, 4 rows affected
Records: 4 Duplicates: 0 Warnings: 0
Simple full join query
When using a full join query, if a row in the left or right table has no matching row, the system automatically fills in NULL.
Example 1: Perform a full join query on tables tbl_a and tbl_b and obtain the result.
obclient> SELECT * FROM tbl_a;
+------+------+
| id | name |
+------+------+
| 1 | ab |
| 2 | cd |
| 3 | ef |
| 4 | gh |
+------+------+
4 rows in set
obclient> SELECT * FROM tbl_b;
+--------+-------+
| number | value |
+--------+-------+
| 1 | 1001 |
| 3 | 1003 |
| 5 | 1005 |
+--------+-------+
3 rows in set
obclient> SELECT * FROM tbl_a FULL JOIN tbl_b ON tbl_a.id=tbl_b.number;
+------+------+--------+-------+
| id | name | number | value |
+------+------+--------+-------+
| 1 | ab | 1 | 1001 |
| 2 | cd | NULL | NULL |
| 3 | ef | 3 | 1003 |
| 4 | gh | NULL | NULL |
| NULL | NULL | 5 | 1005 |
+------+------+--------+-------+
5 rows in set
As you can see, there is no row with value 5 in table tbl_a, so its corresponding row in the result is filled with NULL. Similarly, there are no rows with values 2 or 4 in table tbl_b, so those two rows in the result are filled with NULL.
Combined use of full join and WHERE clause
You can use a full join to obtain the join result, then use a WHERE clause to filter the join result.
Example 2: After performing a full join query on tables tbl_a and tbl_b, return the data where value=1003 in table tbl_b.
obclient> SELECT * FROM tbl_a;
+------+------+
| id | name |
+------+------+
| 1 | ab |
| 2 | cd |
| 3 | ef |
| 4 | gh |
+------+------+
4 rows in set
obclient> SELECT * FROM tbl_b;
+--------+-------+
| number | value |
+--------+-------+
| 1 | 1001 |
| 3 | 1003 |
| 5 | 1005 |
+--------+-------+
3 rows in set
obclient> SELECT * FROM tbl_a FULL JOIN tbl_b ON tbl_a.id=tbl_b.number WHERE tbl_b.value=1003;
+------+------+--------+-------+
| id | name | number | value |
+------+------+--------+-------+
| 3 | ef | 3 | 1003 |
+------+------+--------+-------+
1 row in set