OUTER JOIN queries

OUTER JOIN queries include LEFT JOIN and RIGHT JOIN queries. An OUTER JOIN query returns all rows that meet the join conditions and all or a part of rows in a table. The rows of another table that do not meet the join conditions are also returned. This extends the results of INNER JOIN queries.

Syntax

An OUTER JOIN query involves a primary table and a secondary table. In an OUTER JOIN query, all records in the primary table are returned. For records in the secondary table that match those in the primary table, their values are returned. This part of the query result equals the result of an INNER JOIN query. For records in the secondary table that match no records in the primary table, NULL values are returned.

Syntax for an OUTER JOIN query:

SELECT select_list FROM table_name1 [ LEFT|RIGHT ] JOIN table_name2 ON join_condition
[ WHERE query_condition ]
[ ORDER BY column_list ]

table_name1 indicates the primary table, and table_name2 indicates the secondary table.

LEFT JOIN

To use a JOIN clause to return data that meets the join and filter conditions and also to return data from the left-side table that meets the filter condition specified for the left-side table but does not meet the join condition, you can use LEFT OUTER JOIN, or LEFT JOIN for short. In a LEFT JOIN query, for records in the left-side table that match no records in the right-side table, NULL values are returned.

Example 1: Create tables, insert data, and execute a LEFT JOIN query

1. Create a table named tbl1.

   obclient> CREATE TABLE t1(id NUMBER NOT NULL PRIMARY KEY, name VARCHAR(50));
   Query OK, 0 rows affected
   

2. Create a table named tbl2.

   obclient> CREATE TABLE t2(id NUMBER NOT NULL PRIMARY KEY, name VARCHAR(50));
   Query OK, 0 rows affected
   

3. Insert data into table tbl1.

   obclient> INSERT INTO t1 VALUES(1,'A1'),(2,'B1'),(4,'D1'),(6,'F1'),(8,'H1'),(10,'J1');
   Query OK, 6 rows affected
   Records: 6  Duplicates: 0  Warnings: 0
   

4. Insert data into table tbl2.

   obclient> INSERT INTO t2 VALUES(1,'B2'),(3,'C2'),(6,'F2'),(9,'I2');
   Query OK, 4 rows affected
   Records: 4  Duplicates: 0  Warnings: 0
   

5. Query data inserted into table tbl1.

   obclient> SELECT * FROM t1;
   +------+------+
   | COL1 | COL2 |
   +------+------+
   |    1 | A1   |
   |    2 | B1   |
   |    4 | D1   |
   |    6 | F1   |
   |    8 | H1   |
   |   10 | J1   |
   +------+------+
   6 rows in set
   

6. Query data inserted into table tbl2.

   obclient> SELECT * FROM t2;
   +----+------+
   | ID | NAME |
   +----+------+
   |  1 | B2   |
   |  3 | C2   |
   |  6 | F2   |
   |  9 | I2   |
   +----+------+
   4 rows in set
   

7. Query data by using LEFT JOIN.

   obclient> SELECT t1.id, t1.name, t2.id, t2.name FROM t1 LEFT JOIN t2 on (t1.id=t2.id) ;
   +----+------+----+------+
   | ID | NAME | ID | NAME |
   +----+------+----+------+
   |  1 | A1   |  1 | B2   |
   |  2 | B1   | NULL | NULL |
   |  4 | D1   | NULL | NULL |
   |  6 | F1   |  6 | F2   |
   |  8 | H1   | NULL | NULL |
   | 10 | J1   | NULL | NULL |
   +----+------+----+------+
   6 rows in set
   

Example 2: Use a LEFT JOIN query to obtain the join result and then use the WHERE clause to filter the result

obclient> SELECT t1.id, t1.name, t2.id, t2.name FROM t1
          LEFT JOIN t2 ON t1.id=t2.id WHERE t2.id IS NOT NULL;
+----+------+----+------+
| ID | NAME | ID | NAME |
+----+------+----+------+
|  1 | A1   |  1 | B2   |
|  6 | F1   |  6 | F2   |
+----+------+----+------+
2 rows in set

RIGHT JOIN

To use a JOIN clause to return data that meets the join and filter conditions and also to return data from the right-side table that meets the filter condition specified for the right-side table but does not meet the join condition, you can use RIGHT OUTER JOIN, or RIGHT JOIN for short. In a RIGHT JOIN query, the records in the right-side table matching no records in the left-side table are returned with the NULL value.

Example 1: Execute a RIGHT JOIN query

obclient> SELECT t1.id, t1.name, t2.id, t2.name FROM t1 RIGHT JOIN t2 ON t1.id=t2.id;
+----+------+----+------+
| ID | NAME | ID | NAME |
+----+------+----+------+
|  1 | A1   |  1 | B2   |
| NULL | NULL |  3 | C2   |
|  6 | F1   |  6 | F2   |
| NULL | NULL |  9 | I2   |
+----+------+----+------+
4 rows in set

Example 2: Use a RIGHT JOIN query to obtain the join result and then use the WHERE clause to filter the result

obclient> SELECT t1.id, t1.name, t2.id, t2.name FROM t1
          LEFT JOIN t2 ON t1.id=t2.id WHERE t1.id IS NOT NULL;
+----+------+----+------+
| ID | NAME | ID | NAME |
+----+------+----+------+
|  1 | A1   |  1 | B2   |
|  2 | B1   | NULL | NULL |
|  4 | D1   | NULL | NULL |
|  6 | F1   |  6 | F2   |
|  8 | H1   | NULL | NULL |
| 10 | J1   | NULL | NULL |
+----+------+----+------+
6 rows in set