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 to extend the results of INNER JOIN.

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. The records in the secondary table matching those in the primary table are returned with their values. This part of the query result equals the result of an INNER JOIN query. The records in the secondary table matching no records in the primary table are returned with the NULL value.

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 data from the left table that meets the filter condition specified for the left table but does not meet the join condition, you can use LEFT OUTER JOIN, or LEFT JOIN for short. In a LEFT JOIN query, the records in the left table matching no records in the right table are returned with the NULL value.

Example 1: Execute a LEFT JOIN query

obclient> SELECT tbl1.id, tbl1.name, tbl2.id, tbl2.name FROM tbl1 LEFT JOIN tbl2 ON tbl1.id=tbl2.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 tbl1.id, tbl1.name, tbl2.id, tbl2.name FROM tbl1 LEFT JOIN tbl2 ON tbl1.id=tbl2.id WHERE tbl2.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 data from the right table that meets the filter condition specified for the right 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 table matching no records in the left table are returned with the NULL value.

Example 1: Execute a RIGHT JOIN query

obclient> SELECT tbl1.id, tbl1.name, tbl2.id, tbl2.name FROM tbl1 RIGHT JOIN tbl2 ON tbl1.id=tbl2.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 tbl1.id, tbl1.name, tbl2.id, tbl2.name FROM tbl1 LEFT JOIN tbl2 ON tbl1.id=tbl2.id WHERE tbl1.id IS NOT NULL;
+----+------+----+------+
| id | name | id | name |
+----+------+----+------+
|  1 | A1   |  1 | B2   |
|  6 | F1   |  6 | F2   |
+----+------+----+------+
2 rows in set