cross join 、left/right join的比较

表1 course

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

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)

 

 交叉连接，

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

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是基表，

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)