Oracle internal connection and external connection

The inner connection is used to return records that meet the connection conditions, while the outer connection is an extension of the inner connection. It will not only return all records that meet the connection conditions, in addition, records that do not meet the connection conditions will be returned! From Oracle9i, you can specify the connection syntax in The From clause. Syntax: SELECTtable. column, table. column2FROMtable1 [

The inner connection is used to return records that meet the connection conditions, while the outer connection is an extension of the inner connection. It will not only return all records that meet the connection conditions, in addition, records that do not meet the connection conditions will be returned! From Oracle9i, you can specify the connection syntax in The From clause. Syntax: SELECT table. column, table. column2 FROM table1 [

The inner connection is used to return records that meet the connection conditions, while the outer connection is an extension of the inner connection. It will not only return all records that meet the connection conditions, in addition, records that do not meet the connection conditions will be returned! From Oracle9i, you can specify the connection syntax in The From clause. Syntax:

 SELECT table.column,table.column2 FROM table1 [INNER|LEFT|RIGHT|FULL] JOIN table2 ON table.column1=table.column2;

Inner join indicates inner join, left join indicates LEFT Outer JOIN, right join indicates RIGHT outer JOIN, and full join indicates FULL outer JOIN. ON clause is used to specify the connection condition!

Let's take a look at these so-called connections!

1. Internal Connection

The inner connection is used to return all records that meet the connection conditions. By default, if no join operator is specified when a connection query is executed, these join queries are inner joins. The following example shows how to use the internal connection by displaying the Department name and employee name of Department 10. Example:

SQL> select a.dname ,b.ename from dept a ,emp b where a.deptno=b.deptno and a.deptno=10; DNAME          ENAME-------------- ----------ACCOUNTING     CLARKACCOUNTING     KINGACCOUNTING     MILLER

There are a lot of queries using this connection in my work. In fact, I have never known this is an internal connection. In my impression, the INNER JOIN connection syntax is the internal connection. It is ridiculous to think about it!

Therefore, when executing a connection query, you can specify the inner join option in the FROM clause, or you can specify the inner join. This is a way to display the inner join. Example:

SQL> select a.dname,b.ename from dept a inner join emp b on a.deptno=b.deptno and a.deptno=10; DNAME          ENAME-------------- ----------ACCOUNTING     CLARKACCOUNTING     KINGACCOUNTING     MILLER

There is another method of internal join, but I have not used it yet. This is executed since Oracle 9i. If the primary key column of the master table is the same as the external key column name of the slave table, you can also use the natural join keyword to automatically execute the internal JOIN operation, for example:

SQL> select dname ,ename from dept natural join emp; DNAME          ENAME-------------- ----------RESEARCH       SMITHSALES          ALLENSALES          WARDRESEARCH       JONESSALES          MARTINSALES          BLAKEACCOUNTING     CLARKRESEARCH       SCOTTACCOUNTING     KINGSALES          TURNERRESEARCH       ADAMSSALES          JAMESRESEARCH       FORDACCOUNTING     MILLER 14 rows selected

2. left Outer Join

The left outer join is implemented by specifying the LEFT [OUTER] JOIN option. when left Outer Join is used, not only all records meeting the join conditions are returned, but other rows in the left table of the join operators that do not meet the join conditions are also returned. The following example shows the Department name, employee name, and other department names of department 10:

SQL> select a.dname,b.ename from dept a left join emp b on a.deptno=b.deptno and a.deptno=10; DNAME          ENAME-------------- ----------SALES          ACCOUNTING     CLARKACCOUNTING     KINGACCOUNTING     MILLERRESEARCH       SALES          OPERATIONS  

The right outer connection, full outer connection, and left outer connection are similar, so I won't say much!