FULL JOIN
A full join is a type of outer join. It returns all rows from both tables, regardless of whether they match.
Background information
An outer join compares data from two tables using a comparison operator. The result of an outer join includes rows that match the join condition and rows that do not match the join condition.
Outer joins include full joins, left joins, and right joins. An outer join returns all rows that satisfy the join condition and any unmatched rows from one of the tables, with NULL values in the corresponding columns of the other table.
This topic provides examples of full joins. For examples of left joins, see LEFT JOIN. For examples of right joins, see RIGHT JOIN.
FULL JOIN syntax
In an outer join statement, the left table appears on the left side of the JOIN clause, and the right table appears on the right side of the JOIN clause.
The syntax for a 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 ];
In this syntax, table_name1 is the left table, and table_name2 is the right table.
Examples
Create sample tables and insert appropriate data.
CREATE TABLE tbl_a(id INT NOT NULL PRIMARY KEY, name VARCHAR(50));
Query OK, 0 rows affected
CREATE TABLE tbl_b(number INT NOT NULL PRIMARY KEY, value INT);
Query OK, 0 rows affected
INSERT INTO tbl_a VALUES(1,'ab'),(2,'cd'),(3,'ef'),(4,'gh');
Query OK, 4 rows affected
Records: 4 Duplicates: 0 Warnings: 0
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
In a full join query, if a row in the left table or the right table does not have a matching row, the system automatically fills the corresponding columns with NULL.
Example: Perform a full join on tables tbl_a and tbl_b and obtain the query result.
SELECT * FROM tbl_a;
+------+------+
| id | name |
+------+------+
| 1 | ab |
| 2 | cd |
| 3 | ef |
| 4 | gh |
+------+------+
4 rows in set
SELECT * FROM tbl_b;
+--------+-------+
| number | value |
+--------+-------+
| 1 | 1001 |
| 3 | 1003 |
| 5 | 1005 |
+--------+-------+
3 rows in set
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 in table tbl_a with a value of 5. In the query result, the corresponding column is filled with NULL. Similarly, there are no rows in table tbl_b with values of 2 or 4. In the query result, these two rows are filled with NULL.
Use a full join with a WHERE clause
You can use a full join to obtain the join result and then use a WHERE clause to filter the join result.
Example 2: Perform a full join on tables tbl_a and tbl_b and return the data in tbl_b where value=1003.
SELECT * FROM tbl_a;
+------+------+
| id | name |
+------+------+
| 1 | ab |
| 2 | cd |
| 3 | ef |
| 4 | gh |
+------+------+
4 rows in set
SELECT * FROM tbl_b;
+--------+-------+
| number | value |
+--------+-------+
| 1 | 1001 |
| 3 | 1003 |
| 5 | 1005 |
+--------+-------+
3 rows in set
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