One of Oracle learning notes: SQL internal link, external link, and self-Link

One of Oracle learning notes: SQL internal link, external link, and self-Link

It is not easy for the landlord to learn Oracle recently. After six months of failure, he has to start over again.
SQL internal links, external links, and self-links. I have not interviewed such questions. I will repeat them here.
First, create two test tables and insert data: www.2cto.com [SQL] -- create a table.
Create table table1 (id int, sname varchar (50 ));
Create table table2 (id int, score int );
-- Insert data
Insert into table1 values (1, 'issac ');
Insert into table1 values (2, 'yang ');
Insert into table1 values (3, 'wen ');
Insert into table1 values (4, 'yuanyuan ');
Insert into table2 values (1, 90 );
Insert into table2 values (2,100 );
Insert into table2 values (3,70 );
Insert into table2 values (5, 80 );
Commit; when performing multi-table queries, if the link condition is not specified, the result is returned as a Cartesian Product. For example, the following SQL statement results in 4*4 rows, obviously redundant, obviously it is not the result we want. Therefore, there are various connection conditions. [SQL] -- returns Cartesian Product 4*4 rows if no join adjustment is specified
Select * from table1, table2;
Internal Link
The internal link is a link that compares the values to be linked using comparison operators, including equal links and unequal links. It is a link condition that limits the two tables.
Equal to the link. As the name implies, two tables have equal associations of specified columns, also known as natural links. [SQL] select * from table1, table2 where table1.id = table2.id;

Unequal links, use unequal operator links, including>, <,! =,. [SQL] select * from table1, table2 where table1.id> table2.id;
External link
External links include all external links, left outer links, and right outer links. Sometimes, in this case, the specified columns in two tables are not exactly the same, but we need to display the data in one of the tables or both tables, in this case, external links are used.
The full join operation of all external links naturally displays all the data (not repeated) in the two tables ). If the field in the left table does not exist in the right table, the right table is empty. [SQL] select * from table1 full join table2 on table1.id = table2.id; -- all Outer join

The left outer link is left join. The first table in the two tables is the left table. All records in the left table are displayed. [SQL] -- use table1 (left table) as the primary table for left Outer Join
Select * from table1 left join table2 on table1.id = table2.id;
-- Another method of writing left outer link
Select * from table1, table2 where table1.id = table2.id (+ );

Right join on the outer right link. The second table in the two tables, that is, the right table, is the primary table. All records in the right table are displayed. [SQL] -- right Outer Join of Table 2 as the main table
Select * from table1 right join table2 on table1.id = table2.id;
-- Another method of writing the right outer link
Select * from table1, table2 where table1.id (+) = table2.id;
Self-linked
A self-link is a link to the table itself, giving the table an alias, tableCPY. [SQL] select * from table1, table1 tableCPY where table1.id = tableCPY. id;