join_nested.result

这是linux下运行的mysql软件包,可用于linux 下安装 php + mysql + apach 的网络配置

SELECT t2.a,t2.b,t3.a,t3.b
FROM t2 LEFT JOIN t3 ON t2.b=t3.b
WHERE t2.a = 4 OR (t2.a > 4 AND t3.a IS NULL);
a	b	a	b
4	2	1	2
4	2	2	2
5	3	NULL	NULL
SELECT t2.a,t2.b,t3.a,t3.b
FROM t2 LEFT JOIN (t3) ON t2.b=t3.b
WHERE t2.a = 4 OR (t2.a > 4 AND t3.a IS NULL);
a	b	a	b
4	2	1	2
4	2	2	2
5	3	NULL	NULL
ALTER TABLE t3
CHANGE COLUMN a a1 int,
CHANGE COLUMN c c1 int;
SELECT t2.a,t2.b,t3.a1,t3.b
FROM t2 LEFT JOIN t3 ON t2.b=t3.b
WHERE t2.a = 4 OR (t2.a > 4 AND t3.a1 IS NULL);
a	b	a1	b
4	2	1	2
4	2	2	2
5	3	NULL	NULL
SELECT t2.a,t2.b,t3.a1,t3.b
FROM t2 NATURAL LEFT JOIN t3
WHERE t2.a = 4 OR (t2.a > 4 AND t3.a1 IS NULL);
a	b	a1	b
4	2	1	2
4	2	2	2
5	3	NULL	NULL
DROP TABLE t0,t1,t2,t3,t4,t5,t6,t7,t8,t9;
CREATE TABLE t1 (a int);
CREATE TABLE t2 (a int);
CREATE TABLE t3 (a int);
INSERT INTO t1 VALUES (1);
INSERT INTO t2 VALUES (2);
INSERT INTO t3 VALUES (2);
INSERT INTO t1 VALUES (2);
SELECT * FROM t1 LEFT JOIN (t2 LEFT JOIN t3 ON t2.a=t3.a) ON t1.a=t3.a;
a	a	a
1	NULL	NULL
2	2	2
SELECT * FROM t1 LEFT JOIN t2 LEFT JOIN t3 ON t2.a=t3.a ON t1.a=t3.a;
a	a	a
1	NULL	NULL
2	2	2
DELETE FROM t1 WHERE a=2;
SELECT * FROM t1 LEFT JOIN t2 LEFT JOIN t3 ON t2.a=t3.a ON t1.a=t3.a;
a	a	a
1	NULL	NULL
DELETE FROM t2;
SELECT * FROM t1 LEFT JOIN t2 LEFT JOIN t3 ON t2.a=t3.a ON t1.a=t3.a;
a	a	a
1	NULL	NULL
DROP TABLE t1,t2,t3;
CREATE TABLE t1(a int, key (a));
CREATE TABLE t2(b int, key (b));
CREATE TABLE t3(c int, key (c));
INSERT INTO t1 VALUES (NULL), (0), (1), (2), (3), (4), (5), (6), (7), (8), (9),
(10), (11), (12), (13), (14), (15), (16), (17), (18), (19);
INSERT INTO t2 VALUES (NULL), (0), (1), (2), (3), (4), (5), (6), (7), (8), (9),
(10), (11), (12), (13), (14), (15), (16), (17), (18), (19);
INSERT INTO t3 VALUES (0), (1), (2), (3), (4), (5);
EXPLAIN SELECT a, b, c FROM t1 LEFT JOIN (t2, t3) ON c < 3 and b = c;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	index	NULL	a	5	NULL	21	Using index
1	SIMPLE	t3	index	c	c	5	NULL	6	Using index
1	SIMPLE	t2	ref	b	b	5	test.t3.c	2	Using index
EXPLAIN SELECT a, b, c FROM t1 LEFT JOIN (t2, t3) ON b < 3 and b = c;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	index	NULL	a	5	NULL	21	Using index
1	SIMPLE	t3	index	c	c	5	NULL	6	Using index
1	SIMPLE	t2	ref	b	b	5	test.t3.c	2	Using index
SELECT a, b, c FROM t1 LEFT JOIN (t2, t3) ON b < 3 and b = c;
a	b	c
NULL	0	0
NULL	1	1
NULL	2	2
0	0	0
0	1	1
0	2	2
1	0	0
1	1	1
1	2	2
2	0	0
2	1	1
2	2	2
3	0	0
3	1	1
3	2	2
4	0	0
4	1	1
4	2	2
5	0	0
5	1	1
5	2	2
6	0	0
6	1	1
6	2	2
7	0	0
7	1	1
7	2	2
8	0	0
8	1	1
8	2	2
9	0	0
9	1	1
9	2	2
10	0	0
10	1	1
10	2	2
11	0	0
11	1	1
11	2	2
12	0	0
12	1	1
12	2	2
13	0	0
13	1	1
13	2	2
14	0	0
14	1	1
14	2	2
15	0	0
15	1	1
15	2	2
16	0	0
16	1	1
16	2	2
17	0	0
17	1	1
17	2	2
18	0	0
18	1	1
18	2	2
19	0	0
19	1	1
19	2	2
DELETE FROM t3;
EXPLAIN SELECT a, b, c FROM t1 LEFT JOIN (t2, t3) ON b < 3 and b = c;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	index	NULL	a	5	NULL	21	Using index
1	SIMPLE	t3	index	c	c	5	NULL	0	Using index
1	SIMPLE	t2	ref	b	b	5	test.t3.c	2	Using index
SELECT a, b, c FROM t1 LEFT JOIN (t2, t3) ON b < 3 and b = c;
a	b	c
NULL	NULL	NULL
0	NULL	NULL
1	NULL	NULL
2	NULL	NULL
3	NULL	NULL
4	NULL	NULL
5	NULL	NULL
6	NULL	NULL
7	NULL	NULL
8	NULL	NULL
9	NULL	NULL
10	NULL	NULL
11	NULL	NULL
12	NULL	NULL
13	NULL	NULL
14	NULL	NULL
15	NULL	NULL
16	NULL	NULL
17	NULL	NULL
18	NULL	NULL
19	NULL	NULL
DROP TABLE t1,t2,t3;
CREATE TABLE t1 (c11 int);
CREATE TABLE t2 (c21 int);
CREATE TABLE t3 (c31 int);
INSERT INTO t1 VALUES (4), (5);
SELECT * FROM t1 LEFT JOIN t2 ON c11=c21;
c11	c21
4	NULL
5	NULL
EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON c11=c21;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t2	system	NULL	NULL	NULL	NULL	0	const row not found
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	
SELECT * FROM t1 LEFT JOIN (t2 LEFT JOIN t3 ON c21=c31) ON c11=c21;
c11	c21	c31
4	NULL	NULL
5	NULL	NULL
EXPLAIN SELECT * FROM t1 LEFT JOIN (t2 LEFT JOIN t3 ON c21=c31) ON c11=c21;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	
1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	0	
1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	0	
DROP TABLE t1,t2,t3;
CREATE TABLE t1 (goods int(12) NOT NULL, price varchar(128) NOT NULL);
INSERT INTO t1 VALUES (23, 2340), (26, 9900);
CREATE TABLE t2 (goods int(12), name varchar(50), shop char(2));
INSERT INTO t2 VALUES (23, 'as300', 'fr'), (26, 'as600', 'fr');
create table t3 (groupid int(12) NOT NULL, goodsid int(12) NOT NULL);
INSERT INTO t3 VALUES (3,23), (6,26);
CREATE TABLE t4 (groupid int(12));
INSERT INTO t4 VALUES (1), (2), (3), (4), (5), (6);
SELECT * FROM
(SELECT DISTINCT gl.groupid, gp.price
FROM t4 gl 
LEFT JOIN
(t3 g INNER JOIN t2 p ON g.goodsid = p.goods 
INNER JOIN t1 gp ON p.goods = gp.goods)
ON gl.groupid = g.groupid and p.shop = 'fr') t;
groupid	price
1	NULL
2	NULL
3	2340
4	NULL
5	NULL
6	9900
CREATE VIEW v1 AS
SELECT g.groupid groupid, p.goods goods,  
p.name name, p.shop shop, 
gp.price price
FROM t3 g INNER JOIN t2 p ON g.goodsid = p.goods
INNER JOIN t1 gp on p.goods = gp.goods;
CREATE VIEW v2 AS
SELECT DISTINCT g.groupid, fr.price
FROM t4 g
LEFT JOIN
v1 fr on g.groupid = fr.groupid and fr.shop = 'fr';
SELECT * FROM v2;
groupid	price
1	NULL
2	NULL
3	2340
4	NULL
5	NULL
6	9900
SELECT * FROM 
(SELECT DISTINCT g.groupid, fr.price
FROM t4 g
LEFT JOIN
v1 fr on g.groupid = fr.groupid and fr.shop = 'fr') t;
groupid	price
1	NULL
2	NULL
3	2340
4	NULL
5	NULL
6	9900
DROP VIEW v1,v2;
DROP TABLE t1,t2,t3,t4;
CREATE TABLE t1(a int);
CREATE TABLE t2(b int);
CREATE TABLE t3(c int, d int);
CREATE TABLE t4(d int);
CREATE TABLE t5(e int, f int);
CREATE TABLE t6(f int);
CREATE VIEW v1 AS 
SELECT e FROM t5 JOIN t6 ON t5.e=t6.f;
CREATE VIEW v2 AS 
SELECT e FROM t5 NATURAL JOIN t6;
SELECT t1.a FROM t1 JOIN t2 ON a=b JOIN t3 ON a=c JOIN t4 USING(d);
a
SELECT t1.x FROM t1 JOIN t2 ON a=b JOIN t3 ON a=c JOIN t4 USING(d);
ERROR 42S22: Unknown column 't1.x' in 'field list'
SELECT t1.a FROM t1 JOIN t2 ON a=b JOIN t3 ON a=c NATURAL JOIN t4;
a
SELECT t1.x FROM t1 JOIN t2 ON a=b JOIN t3 ON a=c NATURAL JOIN t4;
ERROR 42S22: Unknown column 't1.x' in 'field list'
SELECT v1.e FROM v1 JOIN t2 ON e=b JOIN t3 ON e=c JOIN t4 USING(d);
e
SELECT v1.x FROM v1 JOIN t2 ON e=b JOIN t3 ON e=c JOIN t4 USING(d);
ERROR 42S22: Unknown column 'v1.x' in 'field list'
SELECT v2.e FROM v2 JOIN t2 ON e=b JOIN t3 ON e=c JOIN t4 USING(d);
e
SELECT v2.x FROM v2 JOIN t2 ON e=b JOIN t3 ON e=c JOIN t4 USING(d);
ERROR 42S22: Unknown column 'v2.x' in 'field list'
DROP VIEW v1, v2;
DROP TABLE t1, t2, t3, t4, t5, t6;
create table t1 (id1 int(11) not null);
insert into t1 values (1),(2);
create table t2 (id2 int(11) not null);
insert into t2 values (1),(2),(3),(4);
create table t3 (id3 char(16) not null);
insert into t3 values ('100');
create table t4 (id2 int(11) not null, id3 char(16));
create table t5 (id1 int(11) not null, key (id1));
insert into t5 values (1),(2),(1);
create view v1 as
select t4.id3 from t4 join t2 on t4.id2 = t2.id2;
select t1.id1 from t1 inner join (t3 left join v1 on t3.id3 = v1.id3);
id1
1
2
drop view v1;
drop table t1, t2, t3, t4, t5;
create table t0 (a int);
insert into t0 values (0),(1),(2),(3);
create table t1(a int);
insert into t1 select A.a + 10*(B.a) from t0 A, t0 B;
create table t2 (a int, b int);
insert into t2 values (1,1), (2,2), (3,3);
create table t3(a int, b int, filler char(200), key(a));
insert into t3 select a,a,'filler' from t1;
insert into t3 select a,a,'filler' from t1;
create table t4 like t3;
insert into t4 select * from t3;
insert into t4 select * from t3;
create table t5 like t4;
insert into t5 select * from t4;
insert into t5 select * from t4;
create table t6 like t5;
insert into t6 select * from t5;
insert into t6 select * from t5;
create table t7 like t6;
insert into t7 select * from t6;
insert into t7 select * from t6;
explain select * from t4 join 
t2 left join (t3 join t5 on t5.a=t3.b) on t3.a=t2.b where t4.a<=>t3.b;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	X	
1	SIMPLE	t3	ref	a	a	5	test.t2.b	X	
1	SIMPLE	t5	ref	a	a	5	test.t3.b	X	
1	SIMPLE	t4	ref	a	a	5	test.t3.b	X	Using where
explain select * from (t4 join t6 on t6.a=t4.b) right join t3 on t4.a=t3.b
join t2 left join (t5 join t7 on t7.a=t5.b) on t5.a=t2.b where t3.a<=>t2.b;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	X	
1	SIMPLE	t3	ref	a	a	5	test.t2.b	X	Using where
1	SIMPLE	t4	ref	a	a	5	test.t3.b	X	
1	SIMPLE	t6	ref	a	a	5	test.t4.b	X	
1	SIMPLE	t5	ref	a	a	5	test.t2.b	X	
1	SIMPLE	t7	ref	a	a	5	test.t5.b	X	
explain select * from t2 left join
(t3 left join (t4 join t6 on t6.a=t4.b) on t4.a=t3.b 
join t5 on t5.a=t3.b) on t3.a=t2.b;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	X	
1	SIMPLE	t3	ref	a	a	5	test.t2.b	X	
1	SIMPLE	t4	ref	a	a	5	test.t3.b	X	
1	SIMPLE	t6	ref	a	a	5	test.t4.b	X	
1	SIMPLE	t5	ref	a	a	5	test.t3.b	X	
drop table t0, t1, t2, t3, t4, t5, t6, t7;
create table t1 (a int);
insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
create table t2 (a int, filler char(100), key(a));
insert into t2 select A.a + 10*B.a, '' from t1 A, t1 B;
create table t3 like t2;
insert into t3 select * from t2;
explain select * from t1 left join 
(t2 left join t3 on (t2.a = t3.a)) 
on (t1.a = t2.a);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	10	
1	SIMPLE	t2	ref	a	a	5	test.t1.a	1	
1	SIMPLE	t3	ref	a	a	5	test.t2.a	1	
drop table t1, t2, t3;
CREATE TABLE t1 (id int NOT NULL PRIMARY KEY, type varchar(10));
CREATE TABLE t2 (pid int NOT NULL PRIMARY KEY, type varchar(10));
CREATE TABLE t3 (cid int NOT NULL PRIMARY KEY,
id int NOT NULL,
pid int NOT NULL);
INSERT INTO t1 VALUES (1, 'A'), (3, 'C');
INSERT INTO t2 VALUES (1, 'A'), (3, 'C');
INSERT INTO t3 VALUES (1, 1, 1), (3, 3, 3);
SELECT * FROM t1 p LEFT JOIN (t3 JOIN t1)
ON (t1.id=t3.id AND t1.type='B' AND p.id=t3.id)
LEFT JOIN t2 ON (t3.pid=t2.pid)
WHERE p.id=1;
id	type	cid	id	pid	id	type	pid	type
1	A	NULL	NULL	NULL	NULL	NULL	NULL	NULL
CREATE VIEW v1 AS
SELECT t3.* FROM t3 JOIN t1 ON t1.id=t3.id AND t1.type='B';
SELECT * FROM t1 p LEFT JOIN v1 ON p.id=v1.id
LEFT JOIN t2 ON v1.pid=t2.pid
WHERE p.id=1;
id	type	cid	id	pid	pid	type
1	A	NULL	NULL	NULL	NULL	NULL
DROP VIEW v1;
DROP TABLE t1,t2,t3;