SQL的各种join
2016-02-01
times read
times read
Contents
测试表数据
t1
sky=# select * from t1;
id | name
----+------
1 | n1
2 | n2
3 | n3
4 | n4
5 | n5
6 | n6
7 | n7
8 | n8
9 | n9
10 | n10
1 | n11
(11 rows)
sky=#
t2
sky=# select * from t2;
id | age | t1_id
----+-----+-------
1 | 11 | 1
2 | 12 | 1
7 | 17 | 2
3 | 13 | 4
4 | 14 | 4
5 | 15 | 5
6 | 16 | 5
8 | 18 | 7
9 | 19 | 7
10 | 20 | 7
(10 rows)
sky=#
inner join
sky=# explain analyze verbose select * from t1 inner join t2 on t1.id = t2.t1_id;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------
Merge Join (cost=223.19..406.47 rows=11832 width=54) (actual time=0.015..0.018 rows=12 loops=1)
Output: t1.id, t1.name, t2.id, t2.age, t2.t1_id
Merge Cond: (t1.id = t2.t1_id)
-> Sort (cost=80.64..83.54 rows=1160 width=42) (actual time=0.010..0.011 rows=9 loops=1)
Output: t1.id, t1.name
Sort Key: t1.id
Sort Method: quicksort Memory: 25kB
-> Seq Scan on public.t1 (cost=0.00..21.60 rows=1160 width=42) (actual time=0.003..0.005 rows=11 loops=1)
Output: t1.id, t1.name
-> Sort (cost=142.54..147.64 rows=2040 width=12) (actual time=0.003..0.004 rows=12 loops=1)
Output: t2.id, t2.age, t2.t1_id
Sort Key: t2.t1_id
Sort Method: quicksort Memory: 25kB
-> Seq Scan on public.t2 (cost=0.00..30.40 rows=2040 width=12) (actual time=0.002..0.002 rows=10 loops=1)
Output: t2.id, t2.age, t2.t1_id
Planning time: 0.055 ms
Execution time: 0.035 ms
(17 rows)
sky=#
sky=# explain analyze verbose select * from t1, t2 where t1.id = t2.t1_id;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------
Merge Join (cost=223.19..406.47 rows=11832 width=54) (actual time=0.016..0.020 rows=12 loops=1)
Output: t1.id, t1.name, t2.id, t2.age, t2.t1_id
Merge Cond: (t1.id = t2.t1_id)
-> Sort (cost=80.64..83.54 rows=1160 width=42) (actual time=0.009..0.009 rows=9 loops=1)
Output: t1.id, t1.name
Sort Key: t1.id
Sort Method: quicksort Memory: 25kB
-> Seq Scan on public.t1 (cost=0.00..21.60 rows=1160 width=42) (actual time=0.004..0.005 rows=11 loops=1)
Output: t1.id, t1.name
-> Sort (cost=142.54..147.64 rows=2040 width=12) (actual time=0.004..0.005 rows=12 loops=1)
Output: t2.id, t2.age, t2.t1_id
Sort Key: t2.t1_id
Sort Method: quicksort Memory: 25kB
-> Seq Scan on public.t2 (cost=0.00..30.40 rows=2040 width=12) (actual time=0.001..0.001 rows=10 loops=1)
Output: t2.id, t2.age, t2.t1_id
Planning time: 0.052 ms
Execution time: 0.036 ms
(17 rows)
sky=#
由此可知：inner join与from t1,t2 where t1.xx = t2.xx是一样的.
它的结果行数=SUM(t1中的每一行 * t2中的每一行中t2.xx=t1.xx)
比如，上面的结果()，它就是（结果行数12 = t1中1的为2行，t2中的1也为2行即2*2=4,
t1中的2为1行, t2中的2也为1行，即1*1 = 1
t1中的4为1行，t2中的4为2行,即1*2 = 2
t1中的5为1行，t2中的5为2行，即1*2 = 2
t1中的7为1行，t2中的7为3行，即1*3 = 3
所以，总行数=4+1+2+2+3=12行.
left join
sky=# explain analyze verbose select * from t1 left join t2 on t1.id = t2.t1_id;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------
Merge Left Join (cost=223.19..406.47 rows=11832 width=54) (actual time=0.025..0.028 rows=17 loops=1)
Output: t1.id, t1.name, t2.id, t2.age, t2.t1_id
Merge Cond: (t1.id = t2.t1_id)
-> Sort (cost=80.64..83.54 rows=1160 width=42) (actual time=0.018..0.018 rows=11 loops=1)
Output: t1.id, t1.name
Sort Key: t1.id
Sort Method: quicksort Memory: 25kB
-> Seq Scan on public.t1 (cost=0.00..21.60 rows=1160 width=42) (actual time=0.012..0.013 rows=11 loops=1)
Output: t1.id, t1.name
-> Sort (cost=142.54..147.64 rows=2040 width=12) (actual time=0.004..0.004 rows=12 loops=1)
Output: t2.id, t2.age, t2.t1_id
Sort Key: t2.t1_id
Sort Method: quicksort Memory: 25kB
-> Seq Scan on public.t2 (cost=0.00..30.40 rows=2040 width=12) (actual time=0.002..0.002 rows=10 loops=1)
Output: t2.id, t2.age, t2.t1_id
Planning time: 0.060 ms
Execution time: 0.046 ms
(17 rows)
sky=#