Skip to content

@rampion /gist:72614
Created

Embed URL

HTTPS clone URL

Subversion checkout URL

You can clone with
or
.
Download ZIP
example of bugs with natural join in oracle
-- on Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
CREATE TABLE left(lid INTEGER, pid INTEGER);
INSERT INTO left VALUES (1,1);
INSERT INTO left VALUES (2,2);
INSERT INTO left VALUES (3,3);
INSERT INTO left VALUES (4,4);
INSERT INTO left VALUES (5,5);
CREATE TABLE right(rid INTEGER, pid INTEGER);
INSERT INTO right VALUES (1,1);
INSERT INTO right VALUES (2,2);
INSERT INTO right VALUES (3,3);
INSERT INTO right VALUES (4,4);
CREATE TABLE there(tid INTEGER, rid INTEGER);
INSERT INTO there VALUES (1,1);
INSERT INTO there VALUES (2,2);
INSERT INTO there VALUES (3,3);
-- natural joins broken
SELECT * FROM (left NATURAL JOIN right) NATURAL JOIN there;
/* results in:
RID PID LID TID
---------- ---------- ---------- ----------
1 1 1 1
2 2 2 2
3 3 3 3
*/
SELECT lid, tid FROM (left NATURAL JOIN right) NATURAL JOIN there;
/* results in:
LID TID
---------- ----------
1 3
1 2
1 1
2 3
2 2
2 1
3 3
3 2
3 1
4 3
4 2
4 1
*/
-- inner joins not
SELECT * FROM (left INNER JOIN right USING (pid)) INNER JOIN there USING (rid);
/* results in:
RID PID LID TID
---------- ---------- ---------- ----------
1 1 1 1
2 2 2 2
3 3 3 3
*/
SELECT lid, tid FROM (left INNER JOIN right USING (pid)) INNER JOIN there USING (rid);
/* results in:
LID TID
---------- ----------
1 1
2 2
3 3
*/
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Something went wrong with that request. Please try again.