CREATE DATABASE test_link DEFAULT CHARACTER SET 'utf8';

use test_link;

CREATE TABLE A (
 id ) NOT NULL,
 name ) NOT NULL,
 PRIMARY KEY (id)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE B (
 id ) NOT NULL,
 addr ) NOT NULL,
 a_id ) not null,
 PRIMARY KEY (id)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into A(id, name) values
(', 'zhang'),
(', 'li'),
(', 'wang');

insert into B(id, addr, a_id) values
('),
('),
(');

mysql> select * from A;
+----+-------+
| id | name  |
+----+-------+
  | zhang |
  | li    |
  | wang  |
+----+-------+
mysql> select * from B;
+----+----------+------+
| id | addr     | a_id |
+----+----------+------+
      |
      |
     |
+----+----------+------+
左右连接
mysql> select A.name, B.addr from A left join B on A.id = B.a_id;
+-------+----------+
| name  | addr     |
+-------+----------+
| zhang | beijing  |
| wang  | shanghai |
| li    | NULL     |
+-------+----------+
mysql> select A.name, B.addr from A right join B on A.id = B.a_id;
+-------+----------+
| name  | addr     |
+-------+----------+
| zhang | beijing  |
| wang  | shanghai |
| NULL  | nanjing  |
+-------+----------+
内连接inner join
mysql> select A.name, B.addr from A inner join B on A.id = B.a_id;
+-------+----------+
| name  | addr     |
+-------+----------+
| zhang | beijing  |
| wang  | shanghai |
+-------+----------+
自然连接
mysql> select A.name, B.addr from A, B where A.id = B.a_id;
+-------+----------+
| name  | addr     |
+-------+----------+
| zhang | beijing  |
| wang  | shanghai |
+-------+----------+
mysql> select * from A, B;
+----+-------+----+----------+------+
| id | name  | id | addr     | a_id |
+----+-------+----+----------+------+
        |
        |
        |
        |
        |
        |
       |
       |
       |
+----+-------+----+----------+------+
全连接
mysql> select A.name, B.addr from A full join B;
ERROR
mysql> select * from A full outer join B;
ERROR
mysql> select * from A full join B;
+----+-------+----+----------+------+
| id | name  | id | addr     | a_id |
+----+-------+----+----------+------+
        |
        |
        |
        |
        |
        |
       |
       |
       |
+----+-------+----+----------+------+
笛卡尔积连接
mysql> select * from A cross join B;
+----+-------+----+----------+------+
| id | name  | id | addr     | a_id |
+----+-------+----+----------+------+
        |
        |
        |
        |
        |
        |
       |
       |
       |
+----+-------+----+----------+------+