WL#1604: Support FULL [OUTER] JOIN by rewriting with UNION
Affects: Prototype Only
—
Status: Un-Assigned
SELECT ... FROM a FULL [OUTER] JOIN b ON a.id=b.id is equivalent to (SELECT ... FROM a LEFT JOIN b ON a.id=b.id) UNION (SELECT ... FROM a RIGHT JOIN b ON a.id=b.id) (the UNION without ALL takes care of eliminating the dups caused by rows actually matching the join condition) [As PeterG noted: It might do more than that. mysql> create table t1 (s1 int); mysql> create table t2 (s1 int); mysql> insert into t1 values (1); mysql> insert into t1 values (1); mysql> select * from t1 left join t2 on (t1.s1=t2.s1); +------+------+ | s1 | s1 | +------+------+ | 1 | NULL | | 1 | NULL | +------+------+ 2 rows in set (0.00 sec) mysql> select * from t1 left join t2 on (t1.s1=t2.s1) union select * from t1 right join t2 on (t1.s1=t2.s1); +------+------+ | s1 | s1 | +------+------+ | 1 | NULL | +------+------+ 1 row in set (0.01 sec) So the "full join" returns fewer rows than the left join.] As a full outer join can't be optimized much anyway, the above appears to be a pretty decent solution. If the joined fields are indexed, we could utilize a method similar to our new "merge index" optimisation to intersect the two indexes and optimize the operation that way. Feature requests: BUG#18003 FULL OUTER JOIN (no syntax to cover)
Copyright (c) 2000, 2024, Oracle Corporation and/or its affiliates. All rights reserved.