数据准备
1、建2张表
# 姓名表 create table table_name( id int(11) primary key auto_increment, user_id int(11) default 0, name varchar(5) default '' ); # 年龄表 create table table_age( id int(11) primary key auto_increment, user_id int(11) default 0, age int(11) default 0 );
2、原始数据
# user_id, name, age (1, "小赵", 21), (2, "小钱", 22), (3, "小孙", 23),
将6条数据分为两部分插入到数据库中
# 名字表少一条 user_id = 3 insert into table_name(user_id, name) values(1, "小赵"), (2, "小钱"); # 年龄表少一条 user_id = 2 insert into table_age(user_id, age) values(1, 21), (3, 23);
3、查看数据
mysql> select * from table_name; +----+---------+--------+ | id | user_id | name | +----+---------+--------+ | 1 | 1 | 小赵 | | 2 | 2 | 小钱 | +----+---------+--------+ mysql> select * from table_age; +----+---------+------+ | id | user_id | age | +----+---------+------+ | 1 | 1 | 21 | | 3 | 3 | 23 | +----+---------+------+
1、INNER JOIN（内连接）
mysql> select a.user_id, name, age -> from table_name as a inner join table_age as b -> on a.user_id=b.user_id; +---------+--------+------+ | user_id | name | age | +---------+--------+------+ | 1 | 小赵 | 21 | +---------+--------+------+
2、LEFT JOIN （左连接）
mysql> select a.user_id, name, age from table_name as a left join table_age as b on a.user_id=b.user_id; +---------+--------+------+ | user_id | name | age | +---------+--------+------+ | 1 | 小赵 | 21 | | 2 | 小钱 | NULL | +---------+--------+------+
3、RIGHT JOIN（右连接）
mysql> select b.user_id, name, age from table_name as a right join table_age as b on a.user_id=b.user_id; +---------+--------+------+ | user_id | name | age | +---------+--------+------+ | 1 | 小赵 | 21 | | 3 | NULL | 23 | +---------+--------+------+
4、UNION（全连接）
mysql 没有outer join 用union替代
mysql> select a.user_id, name, age from table_name as a left join table_age as b on a.user_id =b.user_id union select b.user_id, name, age from table_name as a right join table_age as b on a.user_id =b.user_id; +---------+--------+------+ | user_id | name | age | +---------+--------+------+ | 1 | 小赵 | 21 | | 2 | 小钱 | NULL | | 3 | NULL | 23 | +---------+--------+------+
5、LEFT JOIN EXCLUDING INNER JOIN（左连接-内连接）
mysql> select a.user_id, name, age -> from table_name as a left join table_age as b -> on a.user_id=b.user_id -> where b.user_id is null; +---------+--------+------+ | user_id | name | age | +---------+--------+------+ | 2 | 小钱 | NULL | +---------+--------+------+
6.RIGHT JOIN EXCLUDING INNER JOIN（右连接-内连接）
mysql> select b.user_id, name, age -> from table_name as a right join table_age as b -> on a.user_id=b.user_id -> where a.user_id is null; +---------+------+------+ | user_id | name | age | +---------+------+------+ | 3 | NULL | 23 | +---------+------+------+
7、OUTER JOIN EXCLUDING INNER JOIN（外连接-内连接）
mysql> select a.user_id, name, age -> from table_name as a left join table_age as b -> on a.user_id =b.user_id -> where b.user_id is null -> union -> select b.user_id, name, age -> from table_name as a right join table_age as b -> on a.user_id =b.user_id -> where a.user_id is null; +---------+--------+------+ | user_id | name | age | +---------+--------+------+ | 2 | 小钱 | NULL | | 3 | NULL | 23 | +---------+--------+------+
8、笛卡尔积
mysql> select * from table_name join table_age;
+----+---------+--------+----+---------+------+ | id | user_id | name | id | user_id | age | +----+---------+--------+----+---------+------+ | 1 | 1 | 小赵 | 1 | 1 | 21 | | 2 | 2 | 小钱 | 1 | 1 | 21 | | 1 | 1 | 小赵 | 2 | 3 | 23 | | 2 | 2 | 小钱 | 2 | 3 | 23 | +----+---------+--------+----+---------+------+
总结
参考
1、一张图看懂 SQL 的各种 join 用法
2、mysql中的几种join 及 full join问题
