Detailed description of Oracle table connection (internal/external/self-connection)

There are three types of connections between Oracle tables:

1. inner connection (natural connection)

2. External Connection
(1) left Outer Join (the table on the left is not restricted)
(2) Right Outer Join (the table on the right is not restricted)
(3) Full outer join (no restrictions are imposed on both the left and right tables)

3. Self-join (connections in the same table)
Standard SQL Syntax:
Select table1.column, table2.column
From table1 [inner | left | right | full] join table2 on table1.column1 = table2.column2;
Inner join indicates the inner join;
Left join indicates the left Outer join;
Right join indicates the right outer join;
Full join indicates a full outer join;
The on clause is used to specify the connection conditions.
Note:
If you use the from clause to specify the internal and external connections, you must use the on clause to specify the connection conditions;
If you use the (+) operator to specify the outer join, you must use the where clause to specify the join condition.

1. Inner Join/Join)
1.1 Inner Join
The Inner join logical operator returns each row that matches the first (top) input and the second (bottom) Input join. This is the same as querying multiple tables using select statements, so there are very few internal connections.
Another note is that Join is an inner join by default. Therefore, when writing an inner connection, we can omit the keyword inner.
1.2 The following is an example to describe the internal connection:
1.2.1 create two test tables and insert data first:
SQL> select * from dave;
ID NAME
--------------------
1 dave
2 bl
1 bl
2 dave
SQL> select * from bl;
ID NAME
--------------------
1 dave
2 bl
1.2.3 use internal links for query:
SQL> Select a. id, a. name, B. name from dave a inner join bl B on a. id = B. id; -- standard syntax
ID NAME
------------------------------
1 dave
2 bl
1 bl dave
2 dave bl
SQL> Select a. id, a. name, B. name from dave a join bl B on a. id = B. id; -- the inner keyword is omitted here.
ID NAME
------------------------------
1 dave
2 bl
1 bl dave
2 dave bl
SQL> Select a. id, a. name, B. name from dave a, bl B where a. id = B. id; -- select multi-Table query
ID NAME
------------------------------
1 dave
2 bl
1 bl dave
2 dave bl
From the results of these three SQL statements, we can also see that their functions are the same.
1.3 Natural join)
A natural join is to search for fields with the same data type and column name in the two tables, then automatically connect them, and return all results that meet the condition.
Let's take a look at the examples of natural connections:
SQL> Select id, name from dave a natural join bl B;
ID NAME
--------------------
1 dave
2 bl
Here we do not specify the connection conditions. In fact, oracle makes our own claim to connect the id and name fields in the dave table with the id and name fields in the bl table. That is, it is actually equivalent
SQL> Select dave. id, bl. name From dave join bl on dave. id = bl. id and dave. name = bl. name;
ID NAME
--------------------
1 dave
2 bl
Therefore, we can also regard natural connections as an internal connection.
Notes for natural connection:
(1) If multiple fields in the two tables that are naturally connected meet the same name type, they will be used as conditions for natural connection.
(2) If the two tables that are naturally connected have the same field name but different data types, an error is returned.

1 2 3 Next Page