Oracle has several connection methods

Oracle has three connection methods, namely: 1. Inner connection, the logical operator returns each row that satisfies the connection between the first input and the second input; 2. Outer connection, left outer connection, right outer connection Connection, full outer connection; 3. Self-join is a connection method often used in SQL statements. Using self-join can treat a mirror of its own table as another table, so as to obtain some special data.

The operating environment of this tutorial: Windows 10 system, Dell G3 computer.

There are three types of connections between Oracle tables:

1. Inner join (natural join)

2. Outer join

(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 ( There are no restrictions on the left and right tables)

3. Self-join (join within the same table)

SQL standard syntax:

selecttable1.column,table2.column

fromtable1[inner|left|right|full]jointable2ontable1.column1=table2.column2;

innerjoin represents inner join;

leftjoin represents left outer join;

rightjoin represents a right outer join;

fulljoin represents a complete outer join;

on clause is used to specify the connection conditions.

Note:

If you use the from clause to specify inner and outer connections, you must use the on clause to specify the connection conditions;

If you use the () operator to specify the outer connection To connect, you must use the where clause to specify the connection conditions.

one. InnerJoin/Join

The Innerjoin logical operator returns each row that satisfies the connection between the first (top) input and the second (bottom) input. This has the same effect as using select to query multiple tables, so inner joins are rarely used.

two. OuterJoin

outerjoin will return every row that satisfies the connection between the first (top) input and the second (bottom) input. It also returns any rows in the first input that do not have a matching row in the second input. There are three types of outer joins: left outer join, right outer join, and full outer join. Corresponding to SQL: LEFT/RIGHT/FULLOUTERJOIN. Usually we omit the outer keyword. Written as: LEFT/RIGHT/FULLJOIN.

In left outer join and right outer join, one table will be used as the base table, and all the contents of the table will be displayed, and then the matching contents of the two tables will be added. If the data in the base table is not recorded in another table. Then the column appears as NULL in the associated result set row.

three. Self-join

Self-join (selfjoin) is a connection method often used in SQL statements. Using self-join, you can treat a mirror of your own table as another table, so that you can obtain some special data.

The above is the detailed content of Oracle has several connection methods. For more information, please follow other related articles on the PHP Chinese website!