SQL语句执行顺序的疑问
以前看过一些关于MS SQL的执行顺序的帖子,大致如下:
(8)SELECT (9)DISTINCT (11)<Top Num> <select list>
(1)FROM [left_table]
(3)<join_type> JOIN <right_table>
(2)ON <join_condition>
(4)WHERE <where_condition>
(5)GROUP BY <group_by_list>
(6)WITH <CUBE | RollUP>
(7)HAVING <having_condition>
(10)ORDER BY <order_by_list>
开始的时候先把FROM的表做笛卡尔积, 经过2,3步后再用where筛选数据
前段时间看了一些mysql的资料,提到mysql 用的是Nested Loop Join算法
select m.subject msg_subject, c.content msg_content
from user_group g,group_message m,group_message_content c
where g.user_id = 1
and m.group_id = g.group_id
and c.group_msg_id = m.id
资料里介绍,上面的SQL可以用下面的表达式来表示
for each record g_rec in table user_group that g_rec.user_id=1{
for each record m_rec in group_message that m_rec.group_id=g_rec.group_id{
for each record c_rec in group_message_content that c_rec.group_msg_id=m_rec.id
pass the (g_rec.user_id, m_rec.subject, c_rec.content) row
combination to output;
}
}
那是不是mysql的执行顺序和ms sql不同?
msql 是先做笛卡尔积 后再根据where来筛选
mysql是先根据where筛选,再把from里的表进行关联
不知道这样理解正不正确呢。