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. 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 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: 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 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 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