diff options
Diffstat (limited to 'mysql-test/r/join_outer.result')
| -rw-r--r-- | mysql-test/r/join_outer.result | 135 |
1 files changed, 134 insertions, 1 deletions
diff --git a/mysql-test/r/join_outer.result b/mysql-test/r/join_outer.result index d4a20209162..92b352aa608 100644 --- a/mysql-test/r/join_outer.result +++ b/mysql-test/r/join_outer.result @@ -200,7 +200,7 @@ INSERT INTO t1 VALUES (10363,'Tecniques de Comunicacio Oral i Escrita','Tecnicas INSERT INTO t1 VALUES (11403,'Projecte Fi de Carrera','Proyecto Fin de Carrera','Projecte Fi de Carrera','PFC',9.0,NULL,NULL,NULL); INSERT INTO t1 VALUES (11404,'+lgebra lineal','Algebra lineal','+lgebra lineal','+lgebra lineal',15.0,NULL,NULL,NULL); INSERT INTO t1 VALUES (11405,'+lgebra lineal','Algebra lineal','+lgebra lineal','+lgebra lineal',18.0,NULL,NULL,NULL); -INSERT INTO t1 VALUES (11406,'Calcul Infinitesimal','Cßlculo Infinitesimal','Calcul Infinitesimal','Calcul Infinitesimal',15.0,NULL,NULL,NULL); +INSERT INTO t1 VALUES (11406,'Calcul Infinitesimal','Cßlculo Infinitesimal','Calcul Infinitesimal','Calcul Infinitesimal',15.0,NULL,NULL,NULL); CREATE TABLE t2 ( idAssignatura int(11) DEFAULT '0' NOT NULL, Grup int(11) DEFAULT '0' NOT NULL, @@ -1001,3 +1001,136 @@ SELECT * FROM t1 LEFT JOIN t2 ON (c11=c21 AND c21=30) WHERE c11=40; c11 c21 40 NULL DROP TABLE t1, t2; +CREATE TABLE t1 (a int PRIMARY KEY, b int); +CREATE TABLE t2 (a int PRIMARY KEY, b int); +INSERT INTO t1 VALUES (1,2), (2,1), (3,2), (4,3), (5,6), (6,5), (7,8), (8,7), (9,10); +INSERT INTO t2 VALUES (3,0), (4,1), (6,4), (7,5); +SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t2.b <= t1.a AND t1.a <= t1.b; +a b a b +7 8 7 5 +SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a BETWEEN t2.b AND t1.b; +a b a b +7 8 7 5 +SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE NOT(t1.a NOT BETWEEN t2.b AND t1.b); +a b a b +7 8 7 5 +SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t2.b > t1.a OR t1.a > t1.b; +a b a b +2 1 NULL NULL +3 2 3 0 +4 3 4 1 +6 5 6 4 +8 7 NULL NULL +SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a NOT BETWEEN t2.b AND t1.b; +a b a b +2 1 NULL NULL +3 2 3 0 +4 3 4 1 +6 5 6 4 +8 7 NULL NULL +SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE NOT(t1.a BETWEEN t2.b AND t1.b); +a b a b +2 1 NULL NULL +3 2 3 0 +4 3 4 1 +6 5 6 4 +8 7 NULL NULL +SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a = t2.a OR t2.b > t1.a OR t1.a > t1.b; +a b a b +2 1 NULL NULL +3 2 3 0 +4 3 4 1 +6 5 6 4 +7 8 7 5 +8 7 NULL NULL +SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE NOT(t1.a != t2.a AND t1.a BETWEEN t2.b AND t1.b); +a b a b +2 1 NULL NULL +3 2 3 0 +4 3 4 1 +6 5 6 4 +7 8 7 5 +8 7 NULL NULL +SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a = t2.a AND (t2.b > t1.a OR t1.a > t1.b); +a b a b +3 2 3 0 +4 3 4 1 +6 5 6 4 +SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE NOT(t1.a != t2.a OR t1.a BETWEEN t2.b AND t1.b); +a b a b +3 2 3 0 +4 3 4 1 +6 5 6 4 +SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a = t2.a OR t1.a = t2.b; +a b a b +3 2 3 0 +4 3 4 1 +6 5 6 4 +7 8 7 5 +SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a IN(t2.a, t2.b); +a b a b +3 2 3 0 +4 3 4 1 +6 5 6 4 +7 8 7 5 +SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE NOT(t1.a NOT IN(t2.a, t2.b)); +a b a b +3 2 3 0 +4 3 4 1 +6 5 6 4 +7 8 7 5 +SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a != t1.b AND t1.a != t2.b; +a b a b +3 2 3 0 +4 3 4 1 +6 5 6 4 +7 8 7 5 +SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a NOT IN(t1.b, t2.b); +a b a b +3 2 3 0 +4 3 4 1 +6 5 6 4 +7 8 7 5 +SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE NOT(t1.a IN(t1.b, t2.b)); +a b a b +3 2 3 0 +4 3 4 1 +6 5 6 4 +7 8 7 5 +SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t2.a != t2.b OR (t1.a != t2.a AND t1.a != t2.b); +a b a b +3 2 3 0 +4 3 4 1 +6 5 6 4 +7 8 7 5 +SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE NOT(t2.a = t2.b AND t1.a IN(t2.a, t2.b)); +a b a b +3 2 3 0 +4 3 4 1 +6 5 6 4 +7 8 7 5 +SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t2.a != t2.b AND t1.a != t1.b AND t1.a != t2.b; +a b a b +3 2 3 0 +4 3 4 1 +6 5 6 4 +7 8 7 5 +SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE NOT(t2.a = t2.b OR t1.a IN(t1.b, t2.b)); +a b a b +3 2 3 0 +4 3 4 1 +6 5 6 4 +7 8 7 5 +EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a = t2.a OR t1.a = t2.b; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ALL PRIMARY NULL NULL NULL 4 Using where +1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.a 1 +EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a IN(t2.a, t2.b); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ALL PRIMARY NULL NULL NULL 4 Using where +1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.a 1 +EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a > IF(t1.a = t2.b-2, t2.b, t2.b-1); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ALL PRIMARY NULL NULL NULL 4 Using where +1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.a 1 +DROP TABLE t1,t2; |