Oracle left outer connection, right outer connection, all outer connections-instance description

After reading some examples on the internet, I changed the settings and did some experiments. I hope I can illustrate this.

Create a table and insert data:

-- Department table create table departments (depid number (), depname varchar2 (20), delflag number (); -- employee table create table employees (empid number ), empname varchar2 (20), depid number (), delflag number (); Delete from employees Delete from orders insert into orders ments values (1, 'finacle', 0 ); insert into attributes values (2, 'marketing ', 0); insert into attributes values (3, 'hr', 1); insert into attributes values (4, 'it ', 0); insert into employees values (001, 'wbq', 1, 0); insert into employees values (002, 'czh ', 2, 0); insert into employees values (003, 'chh',); insert into employees values (004, 'wal',); insert into employees values (005, 'ddd ); insert into employees values (006, 'ddd ', 5, 0); insert into employees values (007, 'ddd', 6, 0); Commit;

 

Check the left Outer Join:

-- Lists the Department and employee information with the department ID 3, regardless of whether the Department has employees/* if the employee table is left as the external link, all the data must be the data in the employee table, that is, query the data in the employee table, get the data, and then go to the department table on the right for a query, null if none */-- Test 1 select D. depid, D. depname, E. empid, E. empname from orders ments d left Outer Join employees e on D. depid = E. depid order by D. depid; -- Test 2 -- equivalent to select d above. depid, D. depname, E. empid, E. empname from attributes D, employees e where D. depid = E. depid (+) order by D. depid ;-------------------------------------------------

The data result set is as follows:

Depid	Depname	Empid	Empname
1	Finacle	1	Wbq
1	Finacle	3	CHH
2	Marketing	4	Wal
2	Marketing	2	Czh
3	HR	5	Ddd
4	It

 

Check out the right outer join:

---------------------------------------------/* If the department table is left as the external link, all the data must be the data in the department table, that is, query the data in the department table first, after obtaining the data, go to the employee table on the right to find it one by one. If there is any data, it will be displayed. If not, null */-- Test 3 select D. depid, D. depname, E. empid, E. empname from employees e left Outer Join orders ments D on E. depid = D. depid order by D. depid; -- equivalent to the above -- Test 4 select D. depid, D. depname, E. empid, E. empname from employees e, orders ments d Where E. depid = D. depid (+) order by D. depid;/* Summary : The left Outer Join uses the data on the left as the query condition and queries the right one by one. If yes, it is displayed. If no, It is null. */

 

Result set:

Depid	Depname	Empid	Empname
1	Finacle	3	CHH
1	Finacle	1	Wbq
2	Marketing	4	Wal
2	Marketing	2	Czh
3	HR	5	Ddd
		6	Ddd
		7	Ddd

 

-----------------------------------------------------------/* If you have learned to create an external link, you will be connected to the outer right, that is, changing the order of the two tables. Therefore, you do not need to exercise the external, when you practice it, you will feel confused. If you want to use it, just put the order in pairs and use the left Outer Join. The following is the right outer link test */-- test the left outer link of 1 select D. depid, D. depname, E. empid, E. empname from orders ments d left Outer Join employees e on D. depid = E. depid order by D. depid;

 

Depid	Depname	Empid	Empname
1	Finacle	1	Wbq
1	Finacle	3	CHH
2	Marketing	4	Wal
2	Marketing	2	Czh
3	HR	5	Ddd
4	It

 

 

-- Test the right Outer Join of 1 select D. depid, D. depname, E. empid, E. empname from orders ments D right Outer Join employees e on D. depid = E. depid order by D. depid;

 

Depid	Depname	Empid	Empname
1	Finacle	3	CHH
1	Finacle	1	Wbq
2	Marketing	4	Wal
2	Marketing	2	Czh
3	HR	5	Ddd
		6	Ddd
		7	Ddd

 

 

-- The right Outer Join of test 1 is equivalent to the following SQL statement (note that the left Outer Join is shown below, and the left Outer Join and the right Outer Join can be converted) Select D. depid, D. depname, E. empid, E. empname from employees e left Outer Join orders ments D on D. depid = E. depid order by D. depid;

Depid	Depname	Empid	Empname
1	Finacle	3	CHH
1	Finacle	1	Wbq
2	Marketing	4	Wal
2	Marketing	2	Czh
3	HR	5	Ddd
		6	Ddd
		7	Ddd

 

-- If (+) is used, then (+) is the connected table, and its data is optional (if any, it is displayed, and if not, it is null, the condition is that each row of a table without (+) serves as a -- Query condition)

 

 

-- Full outer join: Select D. depid, D. depname, E. empid, E. empname from orders ments D full outer join employees e on D. depid = E. depid order by D. depid;

 

Depid	Depname	Empid	Empname
1	Finacle	3	CHH
1	Finacle	1	Wbq
2	Marketing	2	Czh
2	Marketing	4	Wal
3	HR	5	Ddd
4	It
		6	Ddd
		7	Ddd

 

You can see the result set clearly.