cross join 、left/right join的比较

表1 course

?

1 2 3 4 5 6 7 8 9 10 11

mysql> select * from course; +----+-------------+ | id | course_name | +----+-------------+ |  1 | Java        | |  2 | MySQL       | |  3 | Python      | |  4 | Go          | |  5 | C++         | +----+-------------+ 5 rows in set (0.00 sec)

　　表2 students

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

mysql> select id,name,course_id from students; +------+--------+-----------+ | id   | name   | course_id | +------+--------+-----------+ |    1 | Danny  |         1 | |    2 | Green  |         2 | |    3 | Henry  |         1 | |    4 | Jane   |         3 | |    5 | Jim    |         2 | |    6 | John   |         4 | |    7 | Lily   |         4 | |    8 | Susan  |         5 | |    9 | Thomas |         6 | |   10 | Tom    |         5 | +------+--------+-----------+ 10 rows in set (0.01 sec)

 

 交叉连接，

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

mysql> select s.id,s.name, c.course_name from students s cross join course c on s.course_i d = c.id; +------+-------+-------------+ | id   | name  | course_name | +------+-------+-------------+ |    1 | Danny | Java        | |    2 | Green | MySQL       | |    3 | Henry | Java        | |    4 | Jane  | Python      | |    5 | Jim   | MySQL       | |    6 | John  | Go          | |    7 | Lily  | Go          | |    8 | Susan | C++         | |   10 | Tom   | C++         | +------+-------+-------------+ 9 rows in set (0.00 sec)

　　

左连接   表course 是基表，表students是参考表，如果基表中某行没有在参考表中相匹配的行，在参考中返回NULL

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

mysql> select s.id,s.name, c.course_name from students s left join course c on s.course_id = c.id; +------+--------+-------------+ | id   | name   | course_name | +------+--------+-------------+ |    1 | Danny  | Java        | |    2 | Green  | MySQL       | |    3 | Henry  | Java        | |    4 | Jane   | Python      | |    5 | Jim    | MySQL       | |    6 | John   | Go          | |    7 | Lily   | Go          | |    8 | Susan  | C++         | |    9 | Thomas | NULL        | |   10 | Tom    | C++         | +------+--------+-------------+ 10 rows in set (0.00 sec)

　　

右连接 表course 是参考表，表students是基表，

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

mysql> select s.id,s.name, c.course_name from students s right join course c on s.course_id = c.id; +------+-------+-------------+ | id   | name  | course_name | +------+-------+-------------+ |    3 | Henry | Java        | |    1 | Danny | Java        | |    5 | Jim   | MySQL       | |    2 | Green | MySQL       | |    4 | Jane  | Python      | |    7 | Lily  | Go          | |    6 | John  | Go          | |   10 | Tom   | C++         | |    8 | Susan | C++         | +------+-------+-------------+ 9 rows in set (0.00 sec)