基本表
create table info(post char(16),id int); create table user(name char(16),id int);
mysql> select * from user;
+------+------+
| name | id |
+------+------+
| ff | 0 |
| ll | 1 |
| tt | 2 |
| hh | 3 |
| rr | 3 |
| nn | 2 |
| mm | 4 |
| ss | 5 |
+------+------+
8 rows in set (0.00 sec)
mysql> select * from info;
+---------+------+
| post | id |
+---------+------+
| student | 0 |
| teacher | 1 |
| worker | 2 |
| farmer | 3 |
+---------+------+
4 rows in set (0.00 sec)
交叉连接(Cross Join)
mysql> select * from user cross join info;
+------+------+---------+------+
| name | id | post | id |
+------+------+---------+------+
| ff | 0 | student | 0 |
| ff | 0 | teacher | 1 |
| ff | 0 | worker | 2 |
| ff | 0 | farmer | 3 |
| ll | 1 | student | 0 |
| ll | 1 | teacher | 1 |
| ll | 1 | worker | 2 |
| ll | 1 | farmer | 3 |
| tt | 2 | student | 0 |
| tt | 2 | teacher | 1 |
| tt | 2 | worker | 2 |
| tt | 2 | farmer | 3 |
| hh | 3 | student | 0 |
| hh | 3 | teacher | 1 |
| hh | 3 | worker | 2 |
| hh | 3 | farmer | 3 |
| rr | 3 | student | 0 |
| rr | 3 | teacher | 1 |
| rr | 3 | worker | 2 |
| rr | 3 | farmer | 3 |
| nn | 2 | student | 0 |
| nn | 2 | teacher | 1 |
| nn | 2 | worker | 2 |
| nn | 2 | farmer | 3 |
| mm | 4 | student | 0 |
| mm | 4 | teacher | 1 |
| mm | 4 | worker | 2 |
| mm | 4 | farmer | 3 |
| ss | 5 | student | 0 |
| ss | 5 | teacher | 1 |
| ss | 5 | worker | 2 |
| ss | 5 | farmer | 3 |
+------+------+---------+------+
32 rows in set (0.00 sec)
内连接(inner join)
mysql> select * from user inner join info on user.id = info.id;
+------+------+---------+------+
| name | id | post | id |
+------+------+---------+------+
| ff | 0 | student | 0 |
| ll | 1 | teacher | 1 |
| tt | 2 | worker | 2 |
| hh | 3 | farmer | 3 |
| rr | 3 | farmer | 3 |
| nn | 2 | worker | 2 |
+------+------+---------+------+
6 rows in set (0.00 sec)
左外连接(left join)
mysql> select * from user left join info on user.id = info.id;
+------+------+---------+------+
| name | id | post | id |
+------+------+---------+------+
| ff | 0 | student | 0 |
| ll | 1 | teacher | 1 |
| tt | 2 | worker | 2 |
| nn | 2 | worker | 2 |
| hh | 3 | farmer | 3 |
| rr | 3 | farmer | 3 |
| mm | 4 | NULL | NULL |
| ss | 5 | NULL | NULL |
+------+------+---------+------+
8 rows in set (0.00 sec)
右外连接(right join)
mysql> select * from user right join info on user.id = info.id;
+------+------+---------+------+
| name | id | post | id |
+------+------+---------+------+
| ff | 0 | student | 0 |
| ll | 1 | teacher | 1 |
| tt | 2 | worker | 2 |
| hh | 3 | farmer | 3 |
| rr | 3 | farmer | 3 |
| nn | 2 | worker | 2 |
+------+------+---------+------+
6 rows in set (0.00 sec)
联合查询(union(去重) union all)
mysql> select * from user union select * from info; +---------+------+ | name | id | +---------+------+ | ff | 0 | | ll | 1 | | tt | 2 | | hh | 3 | | rr | 3 | | nn | 2 | | mm | 4 | | ss | 5 | | student | 0 | | teacher | 1 | | worker | 2 | | farmer | 3 | +---------+------+ 12 rows in set (0.00 sec) mysql> select * from user union all select * from info; +---------+------+ | name | id | +---------+------+ | ff | 0 | | ll | 1 | | tt | 2 | | hh | 3 | | rr | 3 | | nn | 2 | | mm | 4 | | ss | 5 | | student | 0 | | teacher | 1 | | worker | 2 | | farmer | 3 | +---------+------+ 12 rows in set (0.00 sec)
全连接(full join)[mysql不支持全连接,但是可用union或者union all间接实现]
mysql> select * from user left join info on user.id = info.id
union
select * from user right join info on user.id = info.id; +------+------+---------+------+ | name | id | post | id | +------+------+---------+------+ | ff | 0 | student | 0 | | ll | 1 | teacher | 1 | | tt | 2 | worker | 2 | | nn | 2 | worker | 2 | | hh | 3 | farmer | 3 | | rr | 3 | farmer | 3 | | mm | 4 | NULL | NULL | | ss | 5 | NULL | NULL | +------+------+---------+------+ 8 rows in set (0.00 sec)