Deepen understanding of Oracle outer connections (left/right/full), inner connections (oracleinner), and

Deepen understanding of Oracle outer connections (left/right/full), inner connections (oracleinner), and
Oracle's left join, right join, and (+) Outer Join types are always confused. The experiment is used to deepen the understanding of the connection type syntax. There are three types of external connections: 1. left outer Join, corresponding SQL Keyword: LEFT (OUTER) JOIN2. right outer Join, corresponding SQL Keyword: RIGHT (OUTER) JOIN3. all OUTER Join, corresponding SQL Keyword: FULL (OUTER) the outer and outer JOIN operations are based on one table. The matching records in the other table are added to all the records in the base table. If the data in the base table is not recorded in the other table, the associated result set row is displayed as a null value. To be exact, reference MOS: For left join, the table (or "table on the left") mentioned for the first time in the join condition is returned ). For the right join, the table (or "table on the right") mentioned in the join condition is returned ).
Experiment: 1. Preparation: 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 join SQL> select * from left_tbl l left join right_tbl r on l. id = r. id;
ID
--------------------
2 2
3 3
1. From the sorting perspective, the matching records of the left and right tables are listed at the top and in ascending order. You can also write this statement: 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
--------------------
2 2
3 3
1. Use (+) to put the right table to indicate all records in the left table, and add the records in the right table.
SQL> select * from left_tbl l, right_tbl r where r. id (+) = l. id;
ID
--------------------
2 2
3 3
1. Swap the left and right tables in the where condition, and the result is left join. Because (+) is on the left, the right table record is added.
SQL> select * from left_tbl left join right_tbl using (id );
ID
----------
2
3
1 using is used here. Only records in the left table are displayed.
3. right join SQL> select * from left_tbl l right join right_tbl r on l. id = r. id;
ID
--------------------
2 2
3 3
6 is the same as the left and right connections. The matching records in the left and right tables are listed at the top in ascending order. You can also write this statement: 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
--------------------
2 2
3 3
6. Use (+) to put the left table to indicate all records in the right table, and add the records in the left table.
SQL> select * from left_tbl l, right_tbl r where r. id = l. id (+ );

ID
--------------------
2 2
3 3
6. Swap the left and right tables in the where condition, and the result is still right join. Because (+) is on the right, the left table record is added.
4. SQL> select * from left_tbl l full join right_tbl r on l. id = r. id; or select * from left_tbl l full outer join right_tbl r on l. id = r. id;
ID
--------------------
2 2
3 3
1
6
The records of the left and right tables are displayed. NULL is used for non-matching.
5. The inner join refers to the Outer join. Let's look at the innser join or join. SQL> select * from left_tbl l inner join right_tbl r on l. id = r. id;
ID
--------------------
2 2
3 3
Use inner join to only display records matching both the left and right tables.
SQL> select * from left_tbl l join right_tbl r on l. id = r. id;
ID
--------------------
2 2
3. directly use join and inner join.
SQL> select * from left_tbl l, right_tbl r where l. id = r. id;
ID
--------------------
2 2
3. Replacing on with = is another method of inner connection.
Summary:
1. Next to the table in which (+) is located, it indicates that the base table is another table, and the result set also needs to add (+) data that does not match the table.
2. The inner keyword of the inner connection can be saved, and the outer keyword of the outer connection can be saved.
3. You can use the (+) method for both on and where =.
What is the difference between Chinese and foreign oracle database connections and internal connections?

Inner join: two fully qualified records are returned. The fields in each table in the result set come from their respective tables;
Outer Join: two records that fully meet the conditions are returned, plus two records in each table. When only one table has a record in the result set field, fields in the other table are null.
For example, table:
ID user partmentID
1 aaa 3
2 bbb 3
3 ccc (null)
Table B:
ParmentID PartmentName
3 pm001
4 pm002
Internal join result: select A. user B. PartmentName from A inner join B on A. partmentID = B. partmentID
User PartmentName
Aaa pm001
Bbb pm001
Outer join result: select A. user B. PartmentName from A outer join B on A. partmentID = B. partmentID
User PartmentName
Aaa pm001
Bbb pm001
Ccc (null) is a null value for the PartmentName field
(Null) The user field is null when pm002 is more than the record with internal connections.

Intranet connections are mostly used in application environments. Only a few external connections are used. For example, if you compare similar data, you can use external connections to compare the price list of the two people, to compare the consistency between the two data sets, you can use external connections.
 
What is the difference between an internal connection and a full external connection in Oracle?

Inner join (inner join): SELECT * FROM TABLE1 T1 inner join TABLE2 T2 ON T1.ID = T2.ID
This record can be queried only when the values that meet the IDS exist in both tables.
Full outer join (full outer join): SELECT * FROM TABLE1 T1 full outer join TABLE2 T2 ON T1.ID = T2.ID
When querying, the records of TABLE1 and TABLE2 meeting the internal connection will be unified into one record for query.
The ID value can be found in TABLE1, but the corresponding value cannot be found in TABLE2. In this case, the value of this record in TABLE1 remains unchanged. The values of other TABLE2 fields fill in NULL and combine them into one record for query.
On the contrary, the ID value can be found in TABLE2 but the corresponding value cannot be found in TABLE1. In this case, the value of this record in TABLE2 remains unchanged, and the values of other TABLE1 fields fill in NULL, merged into one record for query.