一,直接方式 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?
解决方案 »
- mysql-5.5.25a-winx64 to mysql-5.5.29数据升级
- cause: 连线被拒,请检查主机名称和埠号,并确定 postmaster 可以接受 TCP/IP 连线。
- 疑难sql问题请高手指教 (100分)
- 急!关于从mysql4.0导出数据然后导入到mysql5.0中文全变成乱码的问题!
- 请教高手,关于字符串完全匹配,100分解决马上给!
- 如何用php实现postgres数据库的备份?不好意思,没分了。
- 请问postgresql数据库中 如何计算两个日期间的差距间隔
- mysql读写分离如何实现
- Mysql5.6.10安装失败了,试了很多方法都没有解决,求大神
- 数据库差别
- 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.