Oracle SQL internal connection left Outer connection right outer connection full outer connection

1. Create a test table and prepare the test data
[Email protected]> CREATE Table A (a number (1), b number (1), c number (1));
[Email protected]> CREATE TABLE B (a number (1), D number (1), E number (1));
[Email protected]> INSERT into a values (1,1,1);
[Email protected]> INSERT into a values (2,2,2);
[Email protected]> INSERT into a values (3,3,3);
[Email protected]> insert INTO B values (1,4,4);
[Email protected]> insert INTO B values (2,5,5);
[Email protected]> insert INTO B values (4,6,6);
[Email protected]> commit;

[Email protected]> select * from A;

A B C
---------- ---------- ----------

1 1 1

2 2 2

3 3 3


[Email protected]> select * from B;


A D E
---------- ---------- ----------

1 4 4

2 5 5

4 6 6

2. Internal connection
[Email protected]> Select * from a, b where a.a=b.a;
Another way to do this is
[Email protected]> SELECT * from a INNER join B on A.A=B.A;


A B C a D E

-------- ---------- ---------- ---------- ---------- ----------

1 1 1 1 4 4

2 2 2 2 5 5

3. Left Outer connection
[Email protected]> select * from a b where A.A=B.A (+);
Another way to do this is
[Email protected]> select * from a LEFT outer join B on A.A=B.A;


A B C a D E
---------- ---------- ---------- ---------- ---------- ----------

1 1 1 1 4 4

2 2 2 2 5 5

3 3 3

4. Right outer connection
[Email protected]> select * from a b where A.A (+) =B.A;
Another way to do this is
[Email protected]> Select * from a right outer join B on A.A=B.A;


A B C a D E
---------- ---------- ---------- ---------- ---------- ----------

1 1 1 1 4 4

2 2 2 2 5 5

4 6 6
5. Full-Outer connection
[Email protected]> SELECT * from a full outer join B on A.A=B.A;


A B C a D E
---------- ---------- ---------- ---------- ---------- ----------

1 1 1 1 4 4

2 2 2 2 5 5

3 3 3

4 6 6

Oracle SQL internal connection left Outer connection right outer connection full outer connection