Oracle connection tests and Oracle connection 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 ";
What is the left connection and connection of oracle?
This is the primary and secondary tables. When the two tables match, the primary table is used as the primary table. Go to Baidu for a tutorial.
What are the differences between left and right connections in ORACLE and left and right outer connections?
The left join is the same as the left Outer Join. Left join = left outer join.
T. TABLE_ID = TT. TABLE_ID (+) is equivalent to left join.
The left link displays no data corresponding to the right link.
