1
drop table if exists t1,t2,t3;
2
CREATE TABLE t1 (S1 INT);
3
CREATE TABLE t2 (S1 INT);
4
INSERT INTO t1 VALUES (1);
5
INSERT INTO t2 VALUES (2);
6
SELECT * FROM t1 JOIN t2;
9
SELECT * FROM t1 INNER JOIN t2;
12
SELECT * from t1 JOIN t2 USING (S1);
14
SELECT * FROM t1 INNER JOIN t2 USING (S1);
16
SELECT * from t1 CROSS JOIN t2;
19
SELECT * from t1 LEFT JOIN t2 USING(S1);
22
SELECT * from t1 LEFT JOIN t2 ON(t2.S1=2);
25
SELECT * from t1 RIGHT JOIN t2 USING(S1);
28
SELECT * from t1 RIGHT JOIN t2 ON(t1.S1=1);
32
create table t1 (id int primary key);
33
create table t2 (id int);
34
insert into t1 values (75);
35
insert into t1 values (79);
36
insert into t1 values (78);
37
insert into t1 values (77);
38
replace into t1 values (76);
39
replace into t1 values (76);
40
insert into t1 values (104);
41
insert into t1 values (103);
42
insert into t1 values (102);
43
insert into t1 values (101);
44
insert into t1 values (105);
45
insert into t1 values (106);
46
insert into t1 values (107);
47
insert into t2 values (107),(75),(1000);
48
select t1.id, t2.id from t1, t2 where t2.id = t1.id;
52
select t1.id, count(t2.id) from t1,t2 where t2.id = t1.id group by t1.id;
56
select t1.id, count(t2.id) from t1,t2 where t2.id = t1.id group by t2.id;
60
select t1.id,t2.id from t2 left join t1 on t1.id>=74 and t1.id<=0 where t2.id=75 and t1.id is null;
63
explain select t1.id,t2.id from t2 left join t1 on t1.id>=74 and t1.id<=0 where t2.id=75 and t1.id is null;
64
id select_type table type possible_keys key key_len ref rows Extra
65
1 SIMPLE t1 const PRIMARY NULL NULL NULL 1 Impossible ON condition
66
1 SIMPLE t2 ALL NULL NULL NULL NULL 3 Using where
67
explain select t1.id, t2.id from t1, t2 where t2.id = t1.id and t1.id <0 and t1.id > 0;
68
id select_type table type possible_keys key key_len ref rows Extra
69
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
72
id int(11) NOT NULL auto_increment,
73
token varchar(100) DEFAULT '' NOT NULL,
74
count int(11) DEFAULT '0' NOT NULL,
76
phone char(1) DEFAULT '' NOT NULL,
77
timestamp datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
79
KEY token (token(15)),
80
KEY timestamp (timestamp),
81
UNIQUE token_2 (token(75),count,phone)
83
INSERT INTO t1 VALUES (21,'e45703b64de71482360de8fec94c3ade',3,7800,'n','1999-12-23 17:22:21');
84
INSERT INTO t1 VALUES (22,'e45703b64de71482360de8fec94c3ade',4,5000,'y','1999-12-23 17:22:21');
85
INSERT INTO t1 VALUES (18,'346d1cb63c89285b2351f0ca4de40eda',3,13200,'b','1999-12-23 11:58:04');
86
INSERT INTO t1 VALUES (17,'ca6ddeb689e1b48a04146b1b5b6f936a',4,15000,'b','1999-12-23 11:36:53');
87
INSERT INTO t1 VALUES (16,'ca6ddeb689e1b48a04146b1b5b6f936a',3,13200,'b','1999-12-23 11:36:53');
88
INSERT INTO t1 VALUES (26,'a71250b7ed780f6ef3185bfffe027983',5,1500,'b','1999-12-27 09:44:24');
89
INSERT INTO t1 VALUES (24,'4d75906f3c37ecff478a1eb56637aa09',3,5400,'y','1999-12-23 17:29:12');
90
INSERT INTO t1 VALUES (25,'4d75906f3c37ecff478a1eb56637aa09',4,6500,'y','1999-12-23 17:29:12');
91
INSERT INTO t1 VALUES (27,'a71250b7ed780f6ef3185bfffe027983',3,6200,'b','1999-12-27 09:44:24');
92
INSERT INTO t1 VALUES (28,'a71250b7ed780f6ef3185bfffe027983',3,5400,'y','1999-12-27 09:44:36');
93
INSERT INTO t1 VALUES (29,'a71250b7ed780f6ef3185bfffe027983',4,17700,'b','1999-12-27 09:45:05');
95
id int(11) NOT NULL auto_increment,
96
category int(11) DEFAULT '0' NOT NULL,
97
county int(11) DEFAULT '0' NOT NULL,
98
state int(11) DEFAULT '0' NOT NULL,
99
phones int(11) DEFAULT '0' NOT NULL,
100
nophones int(11) DEFAULT '0' NOT NULL,
102
KEY category (category,county,state)
104
INSERT INTO t2 VALUES (3,2,11,12,5400,7800);
105
INSERT INTO t2 VALUES (4,2,25,12,6500,11200);
106
INSERT INTO t2 VALUES (5,1,37,6,10000,12000);
107
select a.id, b.category as catid, b.state as stateid, b.county as countyid from t1 a, t2 b ignore index (primary) where (a.token ='a71250b7ed780f6ef3185bfffe027983') and (a.count = b.id);
108
id catid stateid countyid
113
select a.id, b.category as catid, b.state as stateid, b.county as
114
countyid from t1 a, t2 b where (a.token =
115
'a71250b7ed780f6ef3185bfffe027983') and (a.count = b.id) order by a.id;
116
id catid stateid countyid
122
create table t1 (a int primary key);
123
insert into t1 values(1),(2);
124
select t1.a from t1 as t1 left join t1 as t2 using (a) left join t1 as t3 using (a) left join t1 as t4 using (a) left join t1 as t5 using (a) left join t1 as t6 using (a) left join t1 as t7 using (a) left join t1 as t8 using (a) left join t1 as t9 using (a) left join t1 as t10 using (a) left join t1 as t11 using (a) left join t1 as t12 using (a) left join t1 as t13 using (a) left join t1 as t14 using (a) left join t1 as t15 using (a) left join t1 as t16 using (a) left join t1 as t17 using (a) left join t1 as t18 using (a) left join t1 as t19 using (a) left join t1 as t20 using (a) left join t1 as t21 using (a) left join t1 as t22 using (a) left join t1 as t23 using (a) left join t1 as t24 using (a) left join t1 as t25 using (a) left join t1 as t26 using (a) left join t1 as t27 using (a) left join t1 as t28 using (a) left join t1 as t29 using (a) left join t1 as t30 using (a) left join t1 as t31 using (a);
128
select t1.a from t1 as t1 left join t1 as t2 using (a) left join t1 as t3 using (a) left join t1 as t4 using (a) left join t1 as t5 using (a) left join t1 as t6 using (a) left join t1 as t7 using (a) left join t1 as t8 using (a) left join t1 as t9 using (a) left join t1 as t10 using (a) left join t1 as t11 using (a) left join t1 as t12 using (a) left join t1 as t13 using (a) left join t1 as t14 using (a) left join t1 as t15 using (a) left join t1 as t16 using (a) left join t1 as t17 using (a) left join t1 as t18 using (a) left join t1 as t19 using (a) left join t1 as t20 using (a) left join t1 as t21 using (a) left join t1 as t22 using (a) left join t1 as t23 using (a) left join t1 as t24 using (a) left join t1 as t25 using (a) left join t1 as t26 using (a) left join t1 as t27 using (a) left join t1 as t28 using (a) left join t1 as t29 using (a) left join t1 as t30 using (a) left join t1 as t31 using (a) left join t1 as t32 using (a) left join t1 as t33 using (a) left join t1 as t34 using (a) left join t1 as t35 using (a) left join t1 as t36 using (a) left join t1 as t37 using (a) left join t1 as t38 using (a) left join t1 as t39 using (a) left join t1 as t40 using (a) left join t1 as t41 using (a) left join t1 as t42 using (a) left join t1 as t43 using (a) left join t1 as t44 using (a) left join t1 as t45 using (a) left join t1 as t46 using (a) left join t1 as t47 using (a) left join t1 as t48 using (a) left join t1 as t49 using (a) left join t1 as t50 using (a) left join t1 as t51 using (a) left join t1 as t52 using (a) left join t1 as t53 using (a) left join t1 as t54 using (a) left join t1 as t55 using (a) left join t1 as t56 using (a) left join t1 as t57 using (a) left join t1 as t58 using (a) left join t1 as t59 using (a) left join t1 as t60 using (a) left join t1 as t61 using (a) left join t1 as t62 using (a) left join t1 as t63 using (a) left join t1 as t64 using (a) left join t1 as t65 using (a);
129
ERROR HY000: Too many tables; MySQL can only use XX tables in a join
130
select a from t1 as t1 left join t1 as t2 using (a) left join t1 as t3 using (a) left join t1 as t4 using (a) left join t1 as t5 using (a) left join t1 as t6 using (a) left join t1 as t7 using (a) left join t1 as t8 using (a) left join t1 as t9 using (a) left join t1 as t10 using (a) left join t1 as t11 using (a) left join t1 as t12 using (a) left join t1 as t13 using (a) left join t1 as t14 using (a) left join t1 as t15 using (a) left join t1 as t16 using (a) left join t1 as t17 using (a) left join t1 as t18 using (a) left join t1 as t19 using (a) left join t1 as t20 using (a) left join t1 as t21 using (a) left join t1 as t22 using (a) left join t1 as t23 using (a) left join t1 as t24 using (a) left join t1 as t25 using (a) left join t1 as t26 using (a) left join t1 as t27 using (a) left join t1 as t28 using (a) left join t1 as t29 using (a) left join t1 as t30 using (a) left join t1 as t31 using (a);
134
select a from t1 as t1 left join t1 as t2 using (a) left join t1 as t3 using (a) left join t1 as t4 using (a) left join t1 as t5 using (a) left join t1 as t6 using (a) left join t1 as t7 using (a) left join t1 as t8 using (a) left join t1 as t9 using (a) left join t1 as t10 using (a) left join t1 as t11 using (a) left join t1 as t12 using (a) left join t1 as t13 using (a) left join t1 as t14 using (a) left join t1 as t15 using (a) left join t1 as t16 using (a) left join t1 as t17 using (a) left join t1 as t18 using (a) left join t1 as t19 using (a) left join t1 as t20 using (a) left join t1 as t21 using (a) left join t1 as t22 using (a) left join t1 as t23 using (a) left join t1 as t24 using (a) left join t1 as t25 using (a) left join t1 as t26 using (a) left join t1 as t27 using (a) left join t1 as t28 using (a) left join t1 as t29 using (a) left join t1 as t30 using (a) left join t1 as t31 using (a) left join t1 as t32 using (a) left join t1 as t33 using (a) left join t1 as t34 using (a) left join t1 as t35 using (a) left join t1 as t36 using (a) left join t1 as t37 using (a) left join t1 as t38 using (a) left join t1 as t39 using (a) left join t1 as t40 using (a) left join t1 as t41 using (a) left join t1 as t42 using (a) left join t1 as t43 using (a) left join t1 as t44 using (a) left join t1 as t45 using (a) left join t1 as t46 using (a) left join t1 as t47 using (a) left join t1 as t48 using (a) left join t1 as t49 using (a) left join t1 as t50 using (a) left join t1 as t51 using (a) left join t1 as t52 using (a) left join t1 as t53 using (a) left join t1 as t54 using (a) left join t1 as t55 using (a) left join t1 as t56 using (a) left join t1 as t57 using (a) left join t1 as t58 using (a) left join t1 as t59 using (a) left join t1 as t60 using (a) left join t1 as t61 using (a) left join t1 as t62 using (a) left join t1 as t63 using (a) left join t1 as t64 using (a) left join t1 as t65 using (a);
135
ERROR HY000: Too many tables; MySQL can only use XX tables in a join
142
INSERT INTO t1 VALUES (1,1),(1,2),(1,3),(1,4),(1,5),(1,6),(1,7),(2,3);
144
a int(11) default NULL
146
INSERT INTO t2 VALUES (2),(3);
147
SELECT t1.a,t2.a,b FROM t1,t2 WHERE t1.a=t2.a AND (t1.a=1 OR t1.a=2) AND b>=1 AND b<=3;
151
CREATE TABLE t1 (d DATE NOT NULL);
152
CREATE TABLE t2 (d DATE NOT NULL);
153
INSERT INTO t1 (d) VALUES ('2001-08-01'),('0000-00-00');
154
SELECT * FROM t1 LEFT JOIN t2 USING (d) WHERE t2.d IS NULL;
158
SELECT * FROM t1 LEFT JOIN t2 USING (d) WHERE d IS NULL;
161
SELECT * from t1 WHERE t1.d IS NULL;
164
SELECT * FROM t1 WHERE 1/0 IS NULL;
170
Document_ID varchar(50) NOT NULL default '',
171
Contractor_ID varchar(6) NOT NULL default '',
172
Language_ID char(3) NOT NULL default '',
173
Expiration_Date datetime default NULL,
174
Publishing_Date datetime default NULL,
176
Column_ID varchar(50) NOT NULL default '',
177
PRIMARY KEY (Language_ID,Document_ID,Contractor_ID)
179
INSERT INTO t1 VALUES ('xep80','1','ger','2001-12-31 20:00:00','2001-11-12 10:58:00','Kartenbestellung - jetzt auch online','anle'),('','999998','',NULL,NULL,NULL,'');
181
Contractor_ID char(6) NOT NULL default '',
182
Language_ID char(3) NOT NULL default '',
183
Document_ID char(50) NOT NULL default '',
184
CanRead char(1) default NULL,
185
Customer_ID int(11) NOT NULL default '0',
186
PRIMARY KEY (Contractor_ID,Language_ID,Document_ID,Customer_ID)
188
INSERT INTO t2 VALUES ('5','ger','xep80','1',999999),('1','ger','xep80','1',999999);
190
Language_ID char(3) NOT NULL default '',
191
Column_ID char(50) NOT NULL default '',
192
Contractor_ID char(6) NOT NULL default '',
193
CanRead char(1) default NULL,
194
Active char(1) default NULL,
195
PRIMARY KEY (Language_ID,Column_ID,Contractor_ID)
197
INSERT INTO t3 VALUES ('ger','home','1','1','1'),('ger','Test','1','0','0'),('ger','derclu','1','0','0'),('ger','clubne','1','0','0'),('ger','philos','1','0','0'),('ger','clubko','1','0','0'),('ger','clubim','1','1','1'),('ger','progra','1','0','0'),('ger','progvo','1','0','0'),('ger','progsp','1','0','0'),('ger','progau','1','0','0'),('ger','progku','1','0','0'),('ger','progss','1','0','0'),('ger','nachl','1','0','0'),('ger','mitgli','1','0','0'),('ger','mitsu','1','0','0'),('ger','mitbus','1','0','0'),('ger','ergmar','1','1','1'),('ger','home','4','1','1'),('ger','derclu','4','1','1'),('ger','clubne','4','0','0'),('ger','philos','4','1','1'),('ger','clubko','4','1','1'),('ger','clubim','4','1','1'),('ger','progra','4','1','1'),('ger','progvo','4','1','1'),('ger','progsp','4','1','1'),('ger','progau','4','0','0'),('ger','progku','4','1','1'),('ger','progss','4','1','1'),('ger','nachl','4','1','1'),('ger','mitgli','4','0','0'),('ger','mitsu','4','0','0'),('ger','mitbus','4','0','0'),('ger','ergmar','4','1','1'),('ger','progra2','1','0','0'),('ger','archiv','4','1','1'),('ger','anmeld','4','1','1'),('ger','thema','4','1','1'),('ger','edito','4','1','1'),('ger','madis','4','1','1'),('ger','enma','4','1','1'),('ger','madis','1','1','1'),('ger','enma','1','1','1'),('ger','vorsch','4','0','0'),('ger','veranst','4','0','0'),('ger','anle','4','1','1'),('ger','redak','4','1','1'),('ger','nele','4','1','1'),('ger','aukt','4','1','1'),('ger','callcenter','4','1','1'),('ger','anle','1','0','0');
198
delete from t1 where Contractor_ID='999998';
199
insert into t1 (Contractor_ID) Values ('999998');
200
SELECT DISTINCT COUNT(t1.Title) FROM t1,
202
t1.Document_ID='xep80' AND t1.Contractor_ID='1' AND
203
t1.Language_ID='ger' AND '2001-12-21 23:14:24' >=
204
Publishing_Date AND '2001-12-21 23:14:24' <= Expiration_Date AND
205
t1.Document_ID = t2.Document_ID AND
206
t1.Language_ID = t2.Language_ID AND
207
t1.Contractor_ID = t2.Contractor_ID AND (
208
t2.Customer_ID = '4' OR
209
t2.Customer_ID = '999999' OR
210
t2.Customer_ID = '1' )AND t2.CanRead
211
= '1' AND t1.Column_ID=t3.Column_ID AND
212
t1.Language_ID=t3.Language_ID AND (
213
t3.Contractor_ID = '4' OR
214
t3.Contractor_ID = '999999' OR
215
t3.Contractor_ID = '1') AND
216
t3.CanRead='1' AND t3.Active='1';
219
SELECT DISTINCT COUNT(t1.Title) FROM t1,
221
t1.Document_ID='xep80' AND t1.Contractor_ID='1' AND
222
t1.Language_ID='ger' AND '2001-12-21 23:14:24' >=
223
Publishing_Date AND '2001-12-21 23:14:24' <= Expiration_Date AND
224
t1.Document_ID = t2.Document_ID AND
225
t1.Language_ID = t2.Language_ID AND
226
t1.Contractor_ID = t2.Contractor_ID AND (
227
t2.Customer_ID = '4' OR
228
t2.Customer_ID = '999999' OR
229
t2.Customer_ID = '1' )AND t2.CanRead
230
= '1' AND t1.Column_ID=t3.Column_ID AND
231
t1.Language_ID=t3.Language_ID AND (
232
t3.Contractor_ID = '4' OR
233
t3.Contractor_ID = '999999' OR
234
t3.Contractor_ID = '1') AND
235
t3.CanRead='1' AND t3.Active='1';
240
t1_id int(11) default NULL,
241
t2_id int(11) default NULL,
242
type enum('Cost','Percent') default NULL,
243
cost_unit enum('Cost','Unit') default NULL,
244
min_value double default NULL,
245
max_value double default NULL,
246
t3_id int(11) default NULL,
247
item_id int(11) default NULL
249
INSERT INTO t1 VALUES (12,5,'Percent','Cost',-1,0,-1,-1),(14,4,'Percent','Cost',-1,0,-1,-1),(18,5,'Percent','Cost',-1,0,-1,-1),(19,4,'Percent','Cost',-1,0,-1,-1),(20,5,'Percent','Cost',100,-1,22,291),(21,5,'Percent','Cost',100,-1,18,291),(22,1,'Percent','Cost',100,-1,6,291),(23,1,'Percent','Cost',100,-1,21,291),(24,1,'Percent','Cost',100,-1,9,291),(25,1,'Percent','Cost',100,-1,4,291),(26,1,'Percent','Cost',100,-1,20,291),(27,4,'Percent','Cost',100,-1,7,202),(28,1,'Percent','Cost',50,-1,-1,137),(29,2,'Percent','Cost',100,-1,4,354),(30,2,'Percent','Cost',100,-1,9,137),(93,2,'Cost','Cost',-1,10000000,-1,-1);
251
id int(10) unsigned NOT NULL auto_increment,
252
name varchar(255) default NULL,
255
INSERT INTO t2 VALUES (1,'s1'),(2,'s2'),(3,'s3'),(4,'s4'),(5,'s5');
256
select t1.*, t2.* from t1, t2 where t2.id=t1.t2_id limit 2;
257
t1_id t2_id type cost_unit min_value max_value t3_id item_id id name
258
22 1 Percent Cost 100 -1 6 291 1 s1
259
23 1 Percent Cost 100 -1 21 291 1 s1
262
siteid varchar(25) NOT NULL default '',
263
emp_id varchar(30) NOT NULL default '',
264
rate_code varchar(10) default NULL,
265
UNIQUE KEY site_emp (siteid,emp_id),
268
INSERT INTO t1 VALUES ('rivercats','psmith','cust'), ('rivercats','KWalker','cust');
270
siteid varchar(25) NOT NULL default '',
271
rate_code varchar(10) NOT NULL default '',
272
base_rate float NOT NULL default '0',
273
PRIMARY KEY (siteid,rate_code)
275
INSERT INTO t2 VALUES ('rivercats','cust',20);
276
SELECT emp.rate_code, lr.base_rate FROM t1 AS emp LEFT JOIN t2 AS lr USING (siteid, rate_code) WHERE emp.emp_id = 'psmith' AND lr.siteid = 'rivercats';
279
SELECT emp.rate_code, lr.base_rate FROM t1 AS emp LEFT JOIN t2 AS lr USING (siteid, rate_code) WHERE lr.siteid = 'rivercats' AND emp.emp_id = 'psmith';
282
SELECT rate_code, lr.base_rate FROM t1 AS emp LEFT JOIN t2 AS lr USING (siteid, rate_code) WHERE emp.emp_id = 'psmith' AND siteid = 'rivercats';
285
SELECT rate_code, lr.base_rate FROM t1 AS emp LEFT JOIN t2 AS lr USING (siteid, rate_code) WHERE siteid = 'rivercats' AND emp.emp_id = 'psmith';
289
CREATE TABLE t1 (ID INTEGER NOT NULL PRIMARY KEY, Value1 VARCHAR(255));
290
CREATE TABLE t2 (ID INTEGER NOT NULL PRIMARY KEY, Value2 VARCHAR(255));
291
INSERT INTO t1 VALUES (1, 'A');
292
INSERT INTO t2 VALUES (1, 'B');
293
SELECT * FROM t1 NATURAL JOIN t2 WHERE 1 AND (Value1 = 'A' AND Value2 <> 'B');
295
SELECT * FROM t1 NATURAL JOIN t2 WHERE 1 AND Value1 = 'A' AND Value2 <> 'B';
297
SELECT * FROM t1 NATURAL JOIN t2 WHERE (Value1 = 'A' AND Value2 <> 'B') AND 1;
300
CREATE TABLE t1 (a int);
301
CREATE TABLE t2 (b int);
302
CREATE TABLE t3 (c int);
303
SELECT * FROM t1 NATURAL JOIN t2 NATURAL JOIN t3;
305
DROP TABLE t1, t2, t3;
306
create table t1 (i int);
307
create table t2 (i int);
308
create table t3 (i int);
309
insert into t1 values(1),(2);
310
insert into t2 values(2),(3);
311
insert into t3 values (2),(4);
312
select * from t1 natural left join t2;
316
select * from t1 left join t2 on (t1.i=t2.i);
320
select * from t1 natural left join t2 natural left join t3;
324
select * from t1 left join t2 on (t1.i=t2.i) left join t3 on (t2.i=t3.i);
328
select * from t3 natural right join t2;
332
select * from t3 right join t2 on (t3.i=t2.i);
336
select * from t3 natural right join t2 natural right join t1;
340
select * from t3 right join t2 on (t3.i=t2.i) right join t1 on (t2.i=t1.i);
344
select * from t1,t2 natural left join t3 order by t1.i,t2.i,t3.i;
350
select * from t1,t2 left join t3 on (t2.i=t3.i) order by t1.i,t2.i,t3.i;
356
select t1.i,t2.i,t3.i from t2 natural left join t3,t1 order by t1.i,t2.i,t3.i;
362
select t1.i,t2.i,t3.i from t2 left join t3 on (t2.i=t3.i),t1 order by t1.i,t2.i,t3.i;
368
select * from t1,t2 natural right join t3 order by t1.i,t2.i,t3.i;
374
select * from t1,t2 right join t3 on (t2.i=t3.i) order by t1.i,t2.i,t3.i;
380
select t1.i,t2.i,t3.i from t2 natural right join t3,t1 order by t1.i,t2.i,t3.i;
386
select t1.i,t2.i,t3.i from t2 right join t3 on (t2.i=t3.i),t1 order by t1.i,t2.i,t3.i;
393
CREATE TABLE t1 (a int, b int default 0, c int default 1);
394
INSERT INTO t1 (a) VALUES (1),(2),(3),(4),(5),(6),(7),(8);
395
INSERT INTO t1 (a) SELECT a + 8 FROM t1;
396
INSERT INTO t1 (a) SELECT a + 16 FROM t1;
397
CREATE TABLE t2 (a int, d int, e int default 0);
398
INSERT INTO t2 (a, d) VALUES (1,1),(2,2),(3,3),(4,4);
399
INSERT INTO t2 (a, d) SELECT a+4, a+4 FROM t2;
400
INSERT INTO t2 (a, d) SELECT a+8, a+8 FROM t2;
402
SELECT STRAIGHT_JOIN t2.e FROM t1,t2 WHERE t2.d=1 AND t1.b=t2.e
404
id select_type table type possible_keys key key_len ref rows Extra
405
1 SIMPLE t1 ALL NULL NULL NULL NULL 32 Using temporary; Using filesort
406
1 SIMPLE t2 ALL NULL NULL NULL NULL 16 Using where; Using join buffer
407
SELECT STRAIGHT_JOIN t2.e FROM t1,t2 WHERE t2.d=1 AND t1.b=t2.e
443
create table t1 (c int, b int);
444
create table t2 (a int, b int);
445
create table t3 (b int, c int);
446
create table t4 (y int, c int);
447
create table t5 (y int, z int);
448
create table t6 (a int, c int);
449
insert into t1 values (10,1);
450
insert into t1 values (3 ,1);
451
insert into t1 values (3 ,2);
452
insert into t2 values (2, 1);
453
insert into t3 values (1, 3);
454
insert into t3 values (1,10);
455
insert into t4 values (11,3);
456
insert into t4 values (2, 3);
457
insert into t5 values (11,4);
458
insert into t6 values (2, 3);
459
select * from t1 natural join t2;
463
select * from t1 natural join t2;
467
select b as a, c as b, a as c from t1 natural join t2;
471
select a as c, c as b, b as a from t1 natural join t2;
475
select t1.c, t1.b, t2.a from t1 join (t2 join t4 on b + 1 = y) on t1.c = t4.c;
479
select t1.c as b, t1.b as a, t2.a as c
480
from t1 join (t2 join t4 on b + 1 = y) on t1.c = t4.c;
484
select * from t1 natural join (t2 natural join t3);
488
select * from (t1 natural join t2) natural join (t3 natural join t4);
492
select * from (t1 natural join t2) natural left join (t3 natural join t4);
497
select * from (t3 natural join t4) natural right join (t1 natural join t2);
502
select * from (t1 natural left join t2) natural left join (t3 natural left join t4);
508
select * from (t4 natural right join t3) natural right join (t2 natural right join t1);
514
select * from t1 natural join t2 natural join t3 natural join t4;
518
select * from ((t1 natural join t2) natural join t3) natural join t4;
522
select * from t1 natural join (t2 natural join (t3 natural join t4));
526
select * from t5 natural right join (t4 natural right join ((t2 natural right join t1) natural right join t3));
531
select * from (t1 natural join t2), (t3 natural join t4);
537
select * from t5 natural join ((t1 natural join t2), (t3 natural join t4));
541
select * from ((t1 natural join t2), (t3 natural join t4)) natural join t5;
545
select * from t5 natural join ((t1 natural join t2) cross join (t3 natural join t4));
549
select * from ((t1 natural join t2) cross join (t3 natural join t4)) natural join t5;
553
select * from (t1 join t2 using (b)) join (t3 join t4 using (c)) using (c);
557
select * from (t1 join t2 using (b)) natural join (t3 join t4 using (c));
561
select a,b,c from (t1 natural join t2) natural join (t3 natural join t4)
562
where b + 1 = y or b + 10 = y group by b,c,a having min(b) < max(y) order by a;
565
select * from (t1 natural join t2) natural left join (t3 natural join t4)
566
where b + 1 = y or b + 10 = y group by b,c,a,y having min(b) < max(y) order by a, y;
570
select * from (t3 natural join t4) natural right join (t1 natural join t2)
571
where b + 1 = y or b + 10 = y group by b,c,a,y having min(b) < max(y) order by a, y;
575
select * from t1 natural join t2 where t1.c > t2.a;
579
select * from t1 natural join t2 where t1.b > t2.b;
581
select * from t1 natural left join (t4 natural join t5) where t5.z is not NULL;
585
select * from t1 join (t2 join t4 on b + 1 = y) on t1.c = t4.c;
589
select * from (t2 join t4 on b + 1 = y) join t1 on t1.c = t4.c;
593
select * from t1 natural join (t2 join t4 on b + 1 = y);
596
select * from (t1 cross join t2) join (t3 cross join t4) on (a < y and t2.b < t3.c);
604
select * from (t1, t2) join (t3, t4) on (a < y and t2.b < t3.c);
612
select * from (t1 natural join t2) join (t3 natural join t4) on a = y;
616
select * from ((t3 join (t1 join t2 on c > a) on t3.b < t2.a) join t4 on y > t1.c) join t5 on z = t1.b + 3;
618
1 3 10 1 2 1 11 3 11 4
619
1 10 10 1 2 1 11 3 11 4
620
1 3 3 1 2 1 11 3 11 4
621
1 10 3 1 2 1 11 3 11 4
622
select * from t1 natural join t2 where t1.b > 0;
626
select * from t1 natural join (t4 natural join t5) where t4.y > 7;
630
select * from (t4 natural join t5) natural join t1 where t4.y > 7;
634
select * from t1 natural left join (t4 natural join t5) where t4.y > 7;
638
select * from (t4 natural join t5) natural right join t1 where t4.y > 7;
642
select * from (t1 natural join t2) join (t3 natural join t4) on t1.b = t3.b;
648
select t1.*, t2.* from t1 natural join t2;
652
select t1.*, t2.*, t3.*, t4.* from (t1 natural join t2) natural join (t3 natural join t4);
656
select * from (select * from t1 natural join t2) as t12
658
(select * from t3 natural join t4) as t34;
662
select * from (select * from t1 natural join t2) as t12
664
(select * from t3 natural join t4) as t34;
669
select * from (select * from t3 natural join t4) as t34
671
(select * from t1 natural join t2) as t12;
676
select * from t1 natural join (t3 cross join t4);
677
ERROR 23000: Column 'c' in from clause is ambiguous
678
select * from (t3 cross join t4) natural join t1;
679
ERROR 23000: Column 'c' in from clause is ambiguous
680
select * from t1 join (t2, t3) using (b);
681
ERROR 23000: Column 'b' in from clause is ambiguous
682
select * from ((t1 natural join t2), (t3 natural join t4)) natural join t6;
683
ERROR 23000: Column 'c' in from clause is ambiguous
684
select * from ((t1 natural join t2), (t3 natural join t4)) natural join t6;
685
ERROR 23000: Column 'c' in from clause is ambiguous
686
select * from t6 natural join ((t1 natural join t2), (t3 natural join t4));
687
ERROR 23000: Column 'c' in from clause is ambiguous
688
select * from (t1 join t2 on t1.b=t2.b) natural join (t3 natural join t4);
689
ERROR 23000: Column 'b' in from clause is ambiguous
690
select * from (t3 natural join t4) natural join (t1 join t2 on t1.b=t2.b);
691
ERROR 23000: Column 'b' in from clause is ambiguous
692
select * from (t3 join (t4 natural join t5) on (b < z))
694
(t1 natural join t2);
695
ERROR 23000: Column 'c' in from clause is ambiguous
696
select * from (t1 natural join t2) natural join (t3 join (t4 natural join t5) on (b < z));
697
ERROR 23000: Column 'c' in from clause is ambiguous
698
select * from information_schema.statistics join information_schema.columns
699
using(table_name,column_name) where table_name='user';
722
CHARACTER_MAXIMUM_LENGTH 60
723
CHARACTER_OCTET_LENGTH 240
724
NUMERIC_PRECISION NULL
726
CHARACTER_SET_NAME utf8
727
COLLATION_NAME utf8_bin
757
CHARACTER_MAXIMUM_LENGTH 16
758
CHARACTER_OCTET_LENGTH 64
759
NUMERIC_PRECISION NULL
761
CHARACTER_SET_NAME utf8
762
COLLATION_NAME utf8_bin
776
create table t1 (a1 int, a2 int);
777
create table t2 (a1 int, b int);
778
create table t3 (c1 int, c2 int);
779
create table t4 (c2 int);
780
insert into t1 values (1,1);
781
insert into t2 values (1,1);
782
insert into t3 values (1,1);
783
insert into t4 values (1);
784
select * from t1 join t2 using (a1) join t3 on b=c1 join t4 using (c2);
787
select * from t3 join (t1 join t2 using (a1)) on b=c1 join t4 using (c2);
790
select a2 from t1 join t2 using (a1) join t3 on b=c1 join t4 using (c2);
793
select a2 from t3 join (t1 join t2 using (a1)) on b=c1 join t4 using (c2);
796
select a2 from ((t1 join t2 using (a1)) join t3 on b=c1) join t4 using (c2);
799
select a2 from ((t1 natural join t2) join t3 on b=c1) natural join t4;
802
drop table t1,t2,t3,t4;
803
create table t1 (c int, b int);
804
create table t2 (a int, b int);
805
create table t3 (b int, c int);
806
create table t4 (y int, c int);
807
create table t5 (y int, z int);
808
insert into t1 values (3,2);
809
insert into t2 values (1,2);
810
insert into t3 values (2,3);
811
insert into t4 values (1,3);
812
insert into t5 values (1,4);
813
select * from ((t3 natural join (t1 natural join t2)) natural join t4)
817
drop table t1, t2, t3, t4, t5;
818
create table t1 (a int, b int);
819
insert into t1 values
824
create table t2 (a int not null, primary key(a));
825
insert into t2 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
826
create table t3 (a int not null, primary key(a));
827
insert into t3 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
829
select * from t1, t2, t3 where t3.a=t1.a and t2.a=t1.b;
831
explain select * from t1, t2, t3 where t3.a=t1.a and t2.a=t1.b;
832
id select_type table type possible_keys key key_len ref rows Extra
833
1 SIMPLE t1 ALL NULL NULL NULL NULL 4
834
1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.b 1 Using index
835
1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 Using index
836
We expect rnd_next=5, and read_key must be 0 because of short-cutting:
837
show status like 'Handler_read%';
844
Handler_read_rnd_next 5
845
drop table t1, t2, t3;
846
create table t1 (a int);
847
insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
848
create table t2 (a int, b int, filler char(100), key(a), key(b));
849
create table t3 (a int, b int, filler char(100), key(a), key(b));
851
select @a:= A.a + 10*(B.a + 10*C.a), @a, 'filler' from t1 A, t1 B, t1 C;
852
insert into t3 select * from t2 where a < 800;
853
explain select * from t2,t3 where t2.a < 200 and t2.b=t3.b;
854
id select_type table type possible_keys key key_len ref rows Extra
855
1 SIMPLE t2 ALL a,b NULL NULL NULL 1000 Using where
856
1 SIMPLE t3 ref b b 5 test.t2.b 1
857
drop table t1, t2, t3;
858
create table t1 (a int);
859
insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
860
create table t2 (a int, b int, primary key(a));
861
insert into t2 select @v:=A.a+10*B.a, @v from t1 A, t1 B;
862
explain select * from t1;
863
id select_type table type possible_keys key key_len ref rows Extra
864
1 SIMPLE t1 ALL NULL NULL NULL NULL 10
865
show status like '%cost%';
867
Last_query_cost 4.016090
868
select 'The cost of accessing t1 (dont care if it changes' '^';
869
The cost of accessing t1 (dont care if it changes
870
The cost of accessing t1 (dont care if it changes^
871
select 'vv: Following query must use ALL(t1), eq_ref(A), eq_ref(B): vv' Z;
873
vv: Following query must use ALL(t1), eq_ref(A), eq_ref(B): vv
874
explain select * from t1, t2 A, t2 B where A.a = t1.a and B.a=A.b;
875
id select_type table type possible_keys key key_len ref rows Extra
876
1 SIMPLE t1 ALL NULL NULL NULL NULL 10
877
1 SIMPLE A eq_ref PRIMARY PRIMARY 4 test.t1.a 1
878
1 SIMPLE B eq_ref PRIMARY PRIMARY 4 test.A.b 1
879
show status like '%cost%';
881
Last_query_cost 24.016090
882
select '^^: The above should be ~= 20 + cost(select * from t1). Value less than 20 is an error' Z;
884
^^: The above should be ~= 20 + cost(select * from t1). Value less than 20 is an error
886
CREATE TABLE t1 (a INT PRIMARY KEY, b INT);
887
CREATE TABLE t2 (c INT PRIMARY KEY, d INT);
888
INSERT INTO t1 VALUES(1,NULL),(2,NULL),(3,NULL),(4,NULL);
889
INSERT INTO t1 SELECT a + 4, b FROM t1;
890
INSERT INTO t1 SELECT a + 8, b FROM t1;
891
INSERT INTO t1 SELECT a + 16, b FROM t1;
892
INSERT INTO t1 SELECT a + 32, b FROM t1;
893
INSERT INTO t1 SELECT a + 64, b FROM t1;
894
INSERT INTO t2 SELECT a, b FROM t1;
895
EXPLAIN SELECT * FROM t1 JOIN t2 ON b=c ORDER BY a LIMIT 2;
896
id select_type table type possible_keys key key_len ref rows Extra
897
1 SIMPLE t1 index NULL PRIMARY 4 NULL 2
898
1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.b 1
899
EXPLAIN SELECT * FROM t1 JOIN t2 ON a=c ORDER BY a LIMIT 2;
900
id select_type table type possible_keys key key_len ref rows Extra
901
1 SIMPLE t1 index PRIMARY PRIMARY 4 NULL 2
902
1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.a 1
903
SELECT * FROM t1 JOIN t2 ON b=c ORDER BY a LIMIT 2;
905
SELECT * FROM t1 JOIN t2 ON a=c ORDER BY a LIMIT 2;
909
EXPLAIN SELECT * FROM t1 JOIN t2 ON b=c ORDER BY a;
910
id select_type table type possible_keys key key_len ref rows Extra
911
1 SIMPLE t1 ALL NULL NULL NULL NULL 128 Using filesort
912
1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.b 1
913
EXPLAIN SELECT * FROM t1 JOIN t2 ON a=c ORDER BY a;
914
id select_type table type possible_keys key key_len ref rows Extra
915
1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 128 Using filesort
916
1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.a 1
917
SELECT * FROM t1 JOIN t2 ON b=c ORDER BY a;
919
SELECT * FROM t1 JOIN t2 ON a=c ORDER BY a;
1049
DROP TABLE IF EXISTS t1,t2;
added
removed
mysql-test/r/join.result
