-- 建立测试表和数据
CREATETABLE customers
(
customer_id VARCHAR2 (10),
city VARCHAR2 (10)
);
INSERTINTO customers VALUES ('163', 'HangZhou');
INSERTINTO customers VALUES ('9you', 'ShangHai');
INSERTINTO customers VALUES ('TX', 'HangZhou');
INSERTINTO customers VALUES ('baidu', 'HangZhou');
COMMIT;
CREATETABLE orders
(
order_id INT,
customer_id VARCHAR2 (10)
);
INSERTINTO orders VALUES (1, '163');
INSERTINTO orders VALUES (2, '163');
INSERTINTO orders VALUES (3, '9you');
INSERTINTO orders VALUES (4, '9you');
INSERTINTO orders VALUES (5, '9you');
INSERTINTO orders VALUES (6, 'TX');
INSERTINTO orders VALUES (7, NULL);
COMMIT;
SELECT *
FROM customers c
LEFTJOIN
orders o
ON c.customer_id = o.customer_id AND c.city = 'HangZhou';
/***
163 HangZhou 1 163
163 HangZhou 2 163
9you ShangHai
TX HangZhou 6 TX
baidu HangZhou
***/
SELECT *
FROM customers c LEFTJOIN orders o ON c.customer_id = o.customer_id
WHERE c.city = 'HangZhou';
/***
163 HangZhou 1 163
163 HangZhou 2 163
TX HangZhou 6 TX
baidu HangZhou
***/
on的逻辑查询处理顺序：
1. 执行笛卡尔积
163 HangZhou 1 163
163 HangZhou 2 163
163 HangZhou 3 9you
163 HangZhou 4 9you
163 HangZhou 5 9you
163 HangZhou 6 TX
163 HangZhou 7
9you ShangHai 1 163
9you ShangHai 2 163
9you ShangHai 3 9you
9you ShangHai 4 9you
9you ShangHai 5 9you
9you ShangHai 6 TX
9you ShangHai 7
TX HangZhou 1 163
TX HangZhou 2 163
TX HangZhou 3 9you
TX HangZhou 4 9you
TX HangZhou 5 9you
TX HangZhou 6 TX
TX HangZhou 7
baidu HangZhou 1 163
baidu HangZhou 2 163
baidu HangZhou 3 9you
baidu HangZhou 4 9you
baidu HangZhou 5 9you
baidu HangZhou 6 TX
baidu HangZhou 7
2. 应用on过滤器：ON c.customer_id = o.customer_id AND c.city = 'HangZhou'
163 HangZhou 1 163
163 HangZhou 2 163
TX HangZhou 6 TX
3. 添加外部行：customers为保留表
163 HangZhou 1 163
163 HangZhou 2 163
TX HangZhou 6 TX
9you ShangHai
baidu HangZhou
where的逻辑查询处理顺序：
1. 执行笛卡尔积
163 HangZhou 1 163
163 HangZhou 2 163
163 HangZhou 3 9you
163 HangZhou 4 9you
163 HangZhou 5 9you
163 HangZhou 6 TX
163 HangZhou 7
9you ShangHai 1 163
9you ShangHai 2 163
9you ShangHai 3 9you
9you ShangHai 4 9you
9you ShangHai 5 9you
9you ShangHai 6 TX
9you ShangHai 7
TX HangZhou 1 163
TX HangZhou 2 163
TX HangZhou 3 9you
TX HangZhou 4 9you
TX HangZhou 5 9you
TX HangZhou 6 TX
TX HangZhou 7
baidu HangZhou 1 163
baidu HangZhou 2 163
baidu HangZhou 3 9you
baidu HangZhou 4 9you
baidu HangZhou 5 9you
baidu HangZhou 6 TX
baidu HangZhou 7
2. 应用on过滤器：ON c.customer_id = o.customer_id
163 HangZhou 1 163
163 HangZhou 2 163
9you ShangHai 3 9you
9you ShangHai 4 9you
9you ShangHai 5 9you
TX HangZhou 6 TX
3. 添加外部行：customers为保留表
163 HangZhou 1 163
163 HangZhou 2 163
9you ShangHai 3 9you
9you ShangHai 4 9you
9you ShangHai 5 9you
TX HangZhou 6 TX
baidu HangZhou
4. 应用where过滤器：WHERE c.city = 'HangZhou'
163 HangZhou 1 163
163 HangZhou 2 163
TX HangZhou 6 TX
baidu HangZhou
--转自
当前位置：
论坛首页 »
自由讨论区 »
MySQL, Oracle及数据库讨论区
»
发起投票
技术讨论
加好友
发消息
2015-8-24 9:28:19 |
赞(
操作
