A FULL JOIN query is a full (outer) join query that returns all rows in all joins, no matter whether they match.
Background information
An OUTER JOIN query uses a comparison operator to compare the data in two tables. The join results include the rows that meet the join conditions and those do not meet the join conditions.
OUTER JOIN queries include FULL JOIN queries, LEFT JOIN queries, and RIGHT JOIN queries. An outer join returns all the rows that meet the join conditions. In addition, it returns unused rows of one table and fills NULL in the corresponding positions in the other table.
This topic describes how to use FULL JOIN queries. For more information about LEFT JOIN queries and RIGHT JOIN queries, see LEFT JOIN queries and RIGHT JOIN queries.
Syntax
Generally, an OUTER JOIN statement contains a left-side table and a right-side table. The leftmost table in the JOIN clause is the left-side table, and the rightmost table in the JOIN clause is the right-side table.
The syntax 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-side table and table_name2 is the right-side table.
Examples
Create a table and insert proper data into the table.
obclient [SYS]> CREATE TABLE tbl_a(id NUMBER NOT NULL PRIMARY KEY, name VARCHAR2(50));
Query OK, 0 rows affected
obclient [SYS]> CREATE TABLE tbl_b(num NUMBER NOT NULL PRIMARY KEY, value NUMBER);
Query OK, 0 rows affected
obclient [SYS]> 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 [SYS]> 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 queries
If no matching row can be found in the left-side table or right-side table for a FULL JOIN query, the system will automatically fill in NULL.
For example, execute a FULL JOIN query on the tbl_a and tbl_b tables and obtain the results.
obclient [SYS]> SELECT * FROM tbl_a;
+----+------+
| ID | NAME |
+----+------+
| 1 | ab |
| 2 | cd |
| 3 | ef |
| 4 | gh |
+----+------+
4 rows in set
obclient [SYS]> SELECT * FROM tbl_b;
+-----+-------+
| NUM | VALUE |
+-----+-------+
| 1 | 1001 |
| 3 | 1003 |
| 5 | 1005 |
+-----+-------+
3 rows in set
obclient [SYS]> SELECT * FROM tbl_a FULL JOIN tbl_b ON tbl_a.id=tbl_b.num;
+------+------+--------+-------+
| ID | NAME | NUM | 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
In this example, the tbl_a table does not contain any row with the value 5. Therefore, this row is filled with NULL in the returned result. The tbl_b table does not contain any row with the value 2 or 4. Therefore, these two rows are filled with NULL in the returned result.
FULL JOIN queries with a WHERE clause
You can use a FULL JOIN query to obtain the join result and then use the WHERE clause to filter the result.
For example, execute a FULL JOIN query on the tbl_a and tbl_b tables and return the data that meets the value=1003 condition in the tbl_b table.
obclient [SYS]> SELECT * FROM tbl_a;
+----+------+
| ID | NAME |
+----+------+
| 1 | ab |
| 2 | cd |
| 3 | ef |
| 4 | gh |
+----+------+
4 rows in set
obclient [SYS]> SELECT * FROM tbl_b;
+-----+-------+
| NUM | VALUE |
+-----+-------+
| 1 | 1001 |
| 3 | 1003 |
| 5 | 1005 |
+-----+-------+
3 rows in set
obclient [SYS]> SELECT * FROM tbl_a FULL JOIN tbl_b ON tbl_a.id=tbl_b.num WHERE tbl_b.value=1003;
+----+------+-----+-------+
| ID | NAME | NUM | VALUE |
+----+------+-----+-------+
| 3 | ef | 3 | 1003 |
+----+------+-----+-------+
1 row in set