public
Last active

example of bugs with natural join in oracle

  • Download Gist
gistfile1.sql
SQL
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64
-- 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
*/

Please sign in to comment on this gist.

Something went wrong with that request. Please try again.