Oracle left Outer Join tests

Oracle left Outer Join tests

For more in-depth left outer join, we did some tests. There are several forms of external join writing. We can track the final SQL conversion form through 10053.

-- Initialize data

Create table

(
Id number,
Age number
);
Create table B
(
Id number,
Age number
);
Insert into A values (1, 10 );
Insert into A values (2, 20 );
Insert into A values (3, 30 );
Insert into B values (1, 10 );
Insert into B values (2, 20 );
Commit;

-- Use 10053 to find the final converted SQL

Alter session set session_cached_cursors = 0;
Alter session set events '10053 trace name context forever, level 1 ';
Explain plan for select * from A left join B on A. id = B. id and A. age> 5;
Explain plan for select * from A left join B on A. id = B. id WHERE A. age> 5;
Explain plan for select * from A left join B on A. id = B. id and B. age> 5;
Explain plan for select * from A left join B on A. id = B. id where B. age> 5;
Alter session set events '10053 trace name context off ';


Select * from A left join B on A. id = B. id and A. age> 5;
ID AGE
----------------------------------------
1 10 1 10
2 20 2 20
3 30
-- Final query after transformations:
SELECT "". "ID" "ID", "". "AGE" "AGE", "B ". "ID" "ID", "B ". "AGE" "AGE"
FROM "GG_TEST". "A" "A", "GG_TEST". "B" "B"
WHERE "A". "ID" = "B". "ID" (+)
AND "A". "AGE"> case when ("B". "ID" (+) IS NOT NULL) THEN 5 ELSE 5 END


Select * from A left join B on A. id = B. id WHERE A. age> 5;
ID AGE
----------------------------------------
1 10 1 10
2 20 2 20
3 30
-- Final query after transformations:
SELECT "". "ID" "ID", "". "AGE" "AGE", "B ". "ID" "ID", "B ". "AGE" "AGE"
FROM "GG_TEST". "A" "A", "GG_TEST". "B" "B"
WHERE "A". "AGE"> 5
AND "A". "ID" = "B". "ID" (+ );


Select * from A left join B on A. id = B. id and B. age> 5;
ID AGE
----------------------------------------
1 10 1 10
2 20 2 20
3 30
-- Final query after transformations:
SELECT "". "ID" "ID", "". "AGE" "AGE", "B ". "ID" "ID", "B ". "AGE" "AGE"
FROM "GG_TEST". "A" "A", "GG_TEST". "B" "B"
WHERE "A". "ID" = "B". "ID" (+)
AND "B". "AGE" (+)> 5

-- In this form, you can see that the external connection is invalid, and CBO is still very smart.
Select * from A left join B on A. id = B. id where B. age> 5;
ID AGE
----------------------------------------
1 10 1 10
2 20 2 20
-- Final query after transformations:
SELECT "". "ID" "ID", "". "AGE" "AGE", "B ". "ID" "ID", "B ". "AGE" "AGE"
FROM "GG_TEST". "A" "A", "GG_TEST". "B" "B"
WHERE "B". "AGE"> 5
AND "A". "ID" = "B". "ID ";