一,直接方式 select b.bigclass,a.bigname from a,b where a.bigid=b.bigid二, join方式 select b,bigclass, a.bigname from a right join b on a.bigid=b.bigid
when i explain the two command ,it is show me the same resultIs there any different between the two command?
解决方案 »
- linux mysql 权限设置 引出问题 急!!!
- MYSQL触发器小问题
- 关于mysql时间戳字段的疑问
- 是否建立索引的问题
- 为什么安装了Linux自带的mysql中没有mysqld_safe
- 急!red hat9.0 如何卸载mysql 或者 查找密码?
- 请教PostgreSQL语法过滤的实现
- 问个mysql启动的简单问题,见笑了。
- 在redhat9.0装了MySQL-server-4.0.20-0.i386.rpm,怎么没有mysql命令呢?我怎么来创建我自己的数据库呢?
- MYSQL初用者来送分了,关于JDBC与MYSQL的连接问题
- mysql数据库,查看和修改concurrent_insert的问题
- 求一条有关更新记录的mysql语句。
结果是一样的吧.我特意去测试了,一样的结果.是不是 wwwwa 大哥搞错了.
Database changed
mysql> select * from t2;
+---+------+------+
| a | b | c |
+---+------+------+
| 1 | 1 | 1 |
| 3 | 3 | 3 |
+---+------+------+
2 rows in set (0.10 sec)mysql> select * from t3;
+------+------+------+
| a | b | c |
+------+------+------+
| 1 | 1 | 888 |
| 2 | 2 | 888 |
| 2 | 2 | 888 |
| 2 | 2 | 888 |
| 2 | 2 | 888 |
| 2 | 2 | 888 |
| 0 | 1 | 888 |
| 0 | 1 | 888 |
| 1 | 1 | 888 |
| 1 | 1 | 888 |
| 1 | 1 | 888 |
| 1 | 1 | 888 |
+------+------+------+
12 rows in set (0.07 sec)mysql> select * from t2,t3 where t2.a=t3.a;
+---+------+------+------+------+------+
| a | b | c | a | b | c |
+---+------+------+------+------+------+
| 1 | 1 | 1 | 1 | 1 | 888 |
| 1 | 1 | 1 | 1 | 1 | 888 |
| 1 | 1 | 1 | 1 | 1 | 888 |
| 1 | 1 | 1 | 1 | 1 | 888 |
| 1 | 1 | 1 | 1 | 1 | 888 |
+---+------+------+------+------+------+
5 rows in set (0.03 sec)mysql> select * from t2 inner join t3 on t2.a=t3.a;
+---+------+------+------+------+------+
| a | b | c | a | b | c |
+---+------+------+------+------+------+
| 1 | 1 | 1 | 1 | 1 | 888 |
| 1 | 1 | 1 | 1 | 1 | 888 |
| 1 | 1 | 1 | 1 | 1 | 888 |
| 1 | 1 | 1 | 1 | 1 | 888 |
| 1 | 1 | 1 | 1 | 1 | 888 |
+---+------+------+------+------+------+
5 rows in set (0.00 sec)look at my test,it's the same result of the two command.
这里明明是right join 嘛
I am so sorry,look at floor 4,that's the real test.
My real question is:select a.* from a,b where a.id=b.id;
select a.* from a inner join b on a.id=b.id;Is there any different from the two command?
mysql> explain EXTENDED select * from t2,t3 where t2.a=t3.a;
+----+-------------+-------+------+---------------+-------+---------+-----------+------+----------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------+---------------+-------+---------+-----------+------+----------+-------------+
| 1 | SIMPLE | t2 | ALL | a,idx_a | NULL | NULL | NULL | 2 | 100.00 | |
| 1 | SIMPLE | t3 | ref | idx_a | idx_a | 5 | test.t2.a | 2 | 100.00 | Using where |
+----+-------------+-------+------+---------------+-------+---------+-----------+------+----------+-------------+
2 rows in set, 1 warning (0.02 sec)mysql> show warnings;
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------+
| Level | Code | Message
|
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------+
| Note | 1003 | select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t3`.`c
` AS `c` from `test`.`t2` join `test`.`t3` where (`test`.`t2`.`a` = `test`.`t3`.`a`) |
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------+
1 row in set (0.00 sec)mysql> explain extended select * from t2 inner join t3 on t2.a=t3.a;
+----+-------------+-------+------+---------------+-------+---------+-----------+------+----------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------+---------------+-------+---------+-----------+------+----------+-------------+
| 1 | SIMPLE | t2 | ALL | a,idx_a | NULL | NULL | NULL | 2 | 100.00 | |
| 1 | SIMPLE | t3 | ref | idx_a | idx_a | 5 | test.t2.a | 2 | 100.00 | Using where |
+----+-------------+-------+------+---------------+-------+---------+-----------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)mysql> show warnings;
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------+
| Level | Code | Message
|
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------+
| Note | 1003 | select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t3`.`c
` AS `c` from `test`.`t2` join `test`.`t3` where (`test`.`t2`.`a` = `test`.`t3`.`a`) |
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
在我看来,这2个SQL是一模一样的。就好比select * from a where id<1 和select * from a where 1>id
在SQL92的时候并没有JOIN语句,只支持这种自然连接。其后在SQL2003中加入了JOIN。 但在数据库优化的时候是完全等同的。
My english is poor.