OUTER JOIN

Description

When performing an INNER JOIN, rows from either table that are unmatched in the other table are not returned. In an OUTER JOIN, unmatched rows in one or both tables can be returned. There are a few types of outer joins:

  • LEFT JOIN returns only unmatched rows from the left table. For more information, see LEFT JOIN.
  • RIGHT JOIN returns only unmatched rows from the right table.For more information, see RIGHT JOIN.
  • FULL OUTER JOIN returns unmatched rows from both tables.For more information, see FULL JOIN.

Examples

  1. create table t1 (a1 int, a2 char(3));
  2. insert into t1 values(10,'aaa'), (10,null), (10,'bbb'), (20,'zzz');
  3. create table t2(a1 char(3), a2 int, a3 real);
  4. insert into t2 values('AAA', 10, 0.5);
  5. insert into t2 values('BBB', 20, 1.0);
  6. select t1.a1, t1.a2, t2.a1, t2.a2 from t1 left outer join t2 on t1.a1=10;
  7. +------+------+------+------+
  8. | a1   | a2   | a1   | a2   |
  9. +------+------+------+------+
  10. |   10 | aaa  | AAA  |   10 |
  11. |   10 | aaa  | BBB  |   20 |
  12. |   10 | NULL | AAA  |   10 |
  13. |   10 | NULL | BBB  |   20 |
  14. |   10 | bbb  | AAA  |   10 |
  15. |   10 | bbb  | BBB  |   20 |
  16. |   20 | zzz  | NULL | NULL |
  17. +------+------+------+------+