JOIN的区别

CREATE TABLE `j1` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `c1` varchar(20) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

CREATE TABLE `j2` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `c1` varchar(20) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

mysql> select * from j1;
+----+-------+
| id | c1    |
+----+-------+
|  1 | user1 |
|  2 | user2 |
|  3 | user3 |
|  4 | user4 |
|  5 | user5 |
+----+-------+
5 rows in set (0.01 sec)

mysql> select * from j2;
+----+--------------+
| id | c1           |
+----+--------------+
|  1 | user_detail1 |
|  3 | user_detail3 |
|  4 | user_detail4 |
|  6 | user_detail6 |
|  8 | user_detail8 |
+----+--------------+
5 rows in set (0.00 sec)

 

 mysql> select * from j1 left join j2 on j1.id = j2.id;
+----+-------+------+--------------+
| id | c1    | id   | c1           |
+----+-------+------+--------------+
|  1 | user1 |    1 | user_detail1 |
|  2 | user2 | NULL | NULL         |
|  3 | user3 |    3 | user_detail3 |
|  4 | user4 |    4 | user_detail4 |
|  5 | user5 | NULL | NULL         |
+----+-------+------+--------------+
5 rows in set (0.00 sec)

 

mysql> select * from j1 right join j2 on j1.id = j2.id;
+------+-------+----+--------------+
| id   | c1    | id | c1           |
+------+-------+----+--------------+
|    1 | user1 |  1 | user_detail1 |
|    3 | user3 |  3 | user_detail3 |
|    4 | user4 |  4 | user_detail4 |
| NULL | NULL  |  6 | user_detail6 |
| NULL | NULL  |  8 | user_detail8 |
+------+-------+----+--------------+
5 rows in set (0.00 sec)

 

 mysql> select * from j1 inner join j2 on j1.id = j2.id;
+----+-------+----+--------------+
| id | c1    | id | c1           |
+----+-------+----+--------------+
|  1 | user1 |  1 | user_detail1 |
|  3 | user3 |  3 | user_detail3 |
|  4 | user4 |  4 | user_detail4 |
+----+-------+----+--------------+
3 rows in set (0.00 sec)

 

mysql> desc select * from j1 left join j2 on j1.id = j2.id where j2.id is not null;
+----+-------------+-------+------------+--------+---------------+---------+---------+--------------+------+----------+-------------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref          | rows | filtered | Extra       |
+----+-------------+-------+------------+--------+---------------+---------+---------+--------------+------+----------+-------------+
|  1 | SIMPLE      | j2    | NULL       | ALL    | PRIMARY       | NULL    | NULL    | NULL         |    5 |    80.00 | Using where |
|  1 | SIMPLE      | j1    | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | mytest.j2.id |    1 |   100.00 | NULL        |
+----+-------------+-------+------------+--------+---------------+---------+---------+--------------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)

mysql> desc select * from j2 left join j1 on j1.id = j2.id where j1.id is not null;
+----+-------------+-------+------------+--------+---------------+---------+---------+--------------+------+----------+-------------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref          | rows | filtered | Extra       |
+----+-------------+-------+------------+--------+---------------+---------+---------+--------------+------+----------+-------------+
|  1 | SIMPLE      | j1    | NULL       | ALL    | PRIMARY       | NULL    | NULL    | NULL         |    5 |    80.00 | Using where |
|  1 | SIMPLE      | j2    | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | mytest.j1.id |    1 |   100.00 | NULL        |
+----+-------------+-------+------------+--------+---------------+---------+---------+--------------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)