MYSQL 两表关联的区别

[zuoxingyu]

一，直接方式

    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 result

Is there any different between the two command?

[小小小小周]

实现的功能没差别.
最大的差别一个正规点,一个不正规点

[WWWWA]

1个内连接，1个右连接，结果不同，虽然EXPLAIN相同

[小小小小周]

[Quote=引用 2 楼 wwwwa 的回复:]

1个内连接，1个右连接，结果不同，虽然EXPLAIN相同
[/Quote]

结果是一样的吧.我特意去测试了,一样的结果.是不是 wwwwa 大哥搞错了.

[zuoxingyu]

TO WWWWA:

mysql> use test;
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.

[vipper23]

[Quote=引用楼主 zuoxingyu 的回复:]
SQL code

一，直接方式

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 t……
[/Quote]

这里明明是right join 嘛

[zuoxingyu]

to vipper23:
I am so sorry,look at floor 4,that's the real test.

[zuoxingyu]

I am so sorry,copy the command from website.
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?

[rucypli]

[Quote=引用 7 楼 zuoxingyu 的回复:]
I am so sorry,copy the command from website.
My real question is:


SQL code
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 th……
[/Quote]
完全没区别

哥你汉语输入法有什么问题吗

[zuoxingyu]

To rucypli:

Yes,i can't write chinese,thank you for your reponse.

[zuoxingyu]

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)

[ACMAIN_CHM]

一、两个语句并不等同。 RIGHT JOIN， 你在B表中加一些A表中没有记录试一下就知道了。

[zuoxingyu]

[Quote=引用 11 楼 acmain_chm 的回复:]

一、两个语句并不等同。 RIGHT JOIN， 你在B表中加一些A表中没有记录试一下就知道了。
[/Quote]
狼头个，我1楼的写错了。你从第4楼开始看下.

另外第10楼，我解析出优化器重构后的SQL，两个都是一样的。
在我看来，这2个SQL是一模一样的。就好比select * from a where id<1 和select * from a where 1>id

[ACMAIN_CHM]

inner join 则和自然连接完全相同。除了写法上不同，本质相同。
在SQL92的时候并没有JOIN语句，只支持这种自然连接。其后在SQL2003中加入了JOIN。 但在数据库优化的时候是完全等同的。

[rucypli]

恭喜lz可以写汉字啦 看得真亲切

[加油馒头]

楼主的英语水平真一般啊，连我这么菜的人 都看懂你说的了

[zuoxingyu]

[Quote=引用 15 楼 jaylongli 的回复:]
楼主的英语水平真一般啊，连我这么菜的人 都看懂你说的了
[/Quote]
I am sorry.
My english is poor.

[rucypli]

[Quote=引用 16 楼 zuoxingyu 的回复:]
引用 15 楼 jaylongli 的回复:
楼主的英语水平真一般啊，连我这么菜的人 都看懂你说的了

I am sorry.
My english is poor.
[/Quote]
又来了。。。。。

[w_zhen_x]

哈哈，这英文，我居然也看的毫无阻碍，
这两个语句我也试了试，确实没啥区别。。