The connection type syntax is always understood by experimentation by experimenting with confusion over the left, right, and (+) connection types of Oracle. The outer joins are divided into three types:1. Left outer connection, corresponding to SQL keyword: OUTER JOIN2. Right outer connection, corresponding to SQL keyword: OUTER JOIN3. All-out connection, corresponding to SQL keyword: full (OUTER) joinboth the left and right outer joins are based on a table, displaying all records of the base table, plus matching records in another table. If the data for the base table is not recorded in another table, the associated result set row is displayed as a null value. to be precise, quote MOS:for a left connection, the table (or "left" table) that is mentioned in the join join condition is returned for the first time. for the right connection, the table (or "right" table) mentioned in the join join condition is returned for the second time.
Experiment: 1. Prepare :sql> CREATE TABLE Left_tbl (ID number);
Table created.
Sql> CREATE TABLE Right_tbl (ID number);
Table created.
INSERT INTO LEFT_TBL values (1);INSERT INTO LEFT_TBL values (2);INSERT into LEFT_TBL values (3);
INSERT INTO RIGHT_TBL values (2);INSERT into RIGHT_TBL values (3);INSERT INTO RIGHT_TBL values (6);
sql> select * from Left_tbl;
id
----------
1
2
3
Sql> select * from Right_tbl;
id
----------
2
3
6
2. Left connectionsql> SELECT * from Left_tbl L left joins right_tbl r on l.id = R.id;
ID ID
---------- ----------
2 2
3 3
1 from the sort view, the left and right table matching records are in front and ascending . You can also write this:select * from Left_tbl L LEFT join right_tbl r on l.id = r.id (+);
Sql> select * from Left_tbl L, right_tbl r where L.id = R.id (+);
ID ID
---------- ----------
2 2
3 3
1 using (+), the right table represents all the records in the left table, plus the records for the right table .
Sql> select * from Left_tbl L, Right_tbl R where r.id (+) = l.id;
ID ID
---------- ----------
2 2
3 3
1Swaps the left and right tables in the Where condition, and the result is left-connected, because (+) is on the left and represents the record with the right table .
Sql> SELECT * from left_tbl LEFT join RIGHT_TBL using (ID);
Id
----------
2
3
1 use is used here to show only the records of the left table .
3. Right connectionSql> select * from Left_tbl L right joins right_tbl r on l.id = R.id;
ID ID
---------- ----------
2 2
3 3
6The same as the left and right connections, the records that match the right and left tables are in the front and ascending . can also be written like this: select * from left_tbl L RIGHT join RIGHT_TBL R on l.id (+) = R . ID;
Sql> select * from Left_tbl L, Right_tbl R where l.id (+) = r.id;
ID ID
---------- ----------
2 2
3 3
6 using (+), the left table represents all the records in the right table, plus the records of the left table .
sql> SELECT * from Left_tbl L, right_tbl r where R.id = L.id (+);
ID ID
---------- ----------
2 2
3 3
6 swaps the left and right tables in the Where condition, and the result is still connected, because (+) is on the right, indicating a record with a leave table .
4. Full-Outer connectionsql> SELECT * from left_tbl L full join right_tbl r on l.id = r.id;select * from left_tbl L FULL outer join right_tbl r on l.id = r.id;
ID ID
---------- ----------
2 2
3 3
1
6
the records for the left and right tables are displayed, not matching null.
5. Internal Connectionsay the outer connection, and then look at the inner connection, innser join or join. sql> SELECT * from left_tbl l inner joins right_tbl r on l.id = R.id;
ID ID
---------- ----------
2 2
3 3
use the inner join to display only records that match the left and right tables .
Sql> select * from Left_tbl L joins right_tbl r on l.id = R.id;
ID ID
---------- ----------
2 2
3 3 directly with join and inner join .
Sql> select * from Left_tbl L, right_tbl r where l.id = R.id;
ID ID
---------- ----------
2 2
3 3 use = instead of On is an inside connection another use .
Summary:
1. (+) next to which table, it means that the base table is another table, and the result set also needs to add data that does not match in the (+) table.
2. Internal connection inner keywords can be saved,The outer keyword for external connections can be saved.
3. You can use the (+) mode with on and where =.
