SELECT t1.c1 t1_c1, t1.c2 t1_c2, t2.c1 t2_c1, t2.c2 t2_c2
FROM t1, t2
WHERE t1.c1=t2.c1 AND t1.c2=t2.c2;
SELECT *
FROM t1 NATURAL JOIN t2
ON t1.c1=t2.c1;
The new syntax promises improved readability, portability to other RDMSs and the possibility to implement full outer joins:
SELECT *
FROM t1 ,t2
WHERE t1.c1(+)=t2.c1(+);
--ORA-01468: a predicate may reference only one outer-joined table
SELECT *
FROM t1 FULL OUTER JOIN t2 ON (t1.c1=t2.c2);
--possible
I recently came across some particularities of this syntax related to where conditions are placed, either in the from clause or in the where clause. Here is a test case with my explanation:
CREATE TABLE t1 (c1 VARCHAR2(1), c2 VARCHAR2(1));
CREATE TABLE t2 (c1 VARCHAR2(1), c2 VARCHAR2(1));
INSERT INTO t1 (c1,c2) VALUES ('1','a');
INSERT INTO t1 (c1,c2) VALUES ('2','b');
INSERT INTO t1 (c1,c2) VALUES ('3','c');
INSERT INTO t1 (c1,c2) VALUES (NULL,'d');
INSERT INTO t1 (c1,c2) VALUES ('5',NULL);
INSERT INTO t2 (c1,c2) VALUES ('1','v');
INSERT INTO t2 (c1,c2) VALUES ('2','w');
INSERT INTO t2 (c1,c2) VALUES ('3','x');
INSERT INTO t2 (c1,c2) VALUES (NULL,'y');
INSERT INTO t2 (c1,c2) VALUES ('5',NULL);
COMMIT;
SELECT t1.c1 t1_c1, t1.c2 t1_c2, t2.c1 t2_c1, t2.c2 t2_c2
FROM t1 JOIN t2
ON t1.c1=t2.c1
ORDER BY 1,2,3,4;
SELECT t1.c1 t1_c1, t1.c2 t1_c2, t2.c1 t2_c1, t2.c2 t2_c2
FROM t1 LEFT OUTER JOIN t2
ON t1.c1=t2.c1
ORDER BY 1,2,3,4;
--all rows from t1 are selected as t1 is on the left
SELECT t1.c1 t1_c1, t1.c2 t1_c2, t2.c1 t2_c1, t2.c2 t2_c2
FROM t1 LEFT OUTER JOIN t2
ON t1.c1=t2.c1
AND t1.c1 IS NOT NULL
ORDER BY 1,2,3,4;
--the and clause has no effect because all rows from t1 will be selected
--the left join part of the query overwrites the effect of the
-- t1.c1 is not null predicate
SELECT t1.c1 t1_c1, t1.c2 t1_c2, t2.c1 t2_c1, t2.c2 t2_c2
FROM t1 LEFT OUTER JOIN t2
ON t1.c1=t2.c1
AND t1.c1 = 1
ORDER BY 1,2,3,4;
--all row from t1 are selected, however only the rows from t2 that also
--satisfy the where clause on t1 are selected
SELECT *
FROM t1 LEFT OUTER JOIN t2
ON t1.c1=t2.c1
WHERE t1.c1 IS NOT NULL;
--as soon as the t1.c1 is not null predicate is moved to the where clause
--only the records that answer that request are selected
SELECT *
FROM t1 FULL OUTER JOIN t2
ON t1.c1=t2.c1
AND t1.c1 IS NOT NULL;
--all row from both tables are selected
SELECT *
FROM t1 FULL OUTER JOIN t2
ON t1.c1=t2.c1
WHERE t1.c1 IS NOT NULL;
--when moved to the where condition two rows are eliminated.
SELECT *
FROM t1 RIGHT OUTER JOIN t2
ON t1.c1=t2.c1;
--moving to a right outer join
SELECT *
FROM t1 RIGHT OUTER JOIN t2
ON t1.c1=t2.c1
AND t1.c1 IS NOT NULL;
--but when we switch to a right outer join the condition on the left table
--has no effect on the final result
SELECT *
FROM t1 RIGHT OUTER JOIN t2
ON t1.c1=t2.c1
WHERE t1.c1 IS NOT NULL;
--so even if the condition is on the table on the left it is filtering the final
--results
Run the test case and see for yourself. If you have any questions please don't hesitate to ask them on the blog or by email.
No comments:
Post a Comment