# **OUTER JOIN**

## **Grammar Description**

In OUTER JOIN, you can return mismatched rows in one or two tables. ``OUT JOIN`` Please refer to:

- ``LEFT JOIN`` keyword returns all rows from the left table (table1). See [LEFT JOIN](left-join.md).
- ``RIGHT JOIN`` keyword returns all rows from the right table (table2). See [RIGHT JOIN](right-join.md).
- ``FULL OUTER JOIN`` keyword returns rows as long as there is a match in one of the left table (table1) and right table (table2). See [FULL JOIN](full-join.md).

## **Example**

```text
create table t1 (a1 int, a2 char(3));
insert into t1 values(10,'aaa'), (10,null), (10,'bbb'), (20,'zzz');
create table t2(a1 char(3), a2 int, a3 real);
insert into t2 values('AAA', 10, 0.5);
insert into t2 values('BBB', 20, 1.0);

mysql> select t1.a1, t1.a2, t2.a1, t2.a2 from t1 left outer join t2 on t1.a1=10;
+------+-----+-----+
| a1 | a2 | a1 | a2 |
+------+-----+-----+
| 10 | aaa | AAA | 10 |
| 10 | aaa | BBB | 20 |
| 10 | NULL | AAA | 10 |
| 10 | NULL | BBB | 20 |
| 10 | bbb | AAA | 10 |
| 10 | bbb | BBB | 20 |
| 20 | zzz | NULL | NULL |
+------+-----+-----+
```