Oracle Table Connection mode (1)---

Tag:ural    no     operator     use     specify     Caveats     Limitations     type    csharp   

The connections between Oracle tables are divided into three types: 1. Internal connection (natural connection) 2. Outer JOIN (1) LEFT outer join (No limit on left table) (2) Right outer join (no restriction on right table) (3) Full outer connection (no restriction on both tables) 3. Self-connect (connection within the same table) standard syntax for sql: SELECT Table1.column,table2.columnfrom table1 [Inner | left | right | full] join table2 on table1.c Olumn1 = Table2.column2;inner Join represents an inner join; the LEFT join represents an outer join, the right join represents an outer join, and the full join represents a complete outer join; the ON clause is used to specify the join condition. Note: If you specify an inner or outer join using the FROM clause, you must specify the join condition using the ON clause, and if you specify an outer join using the (+) operator, you must specify the join condition by using the WHERE clause. A The INNER join (Inner join/join) 1.1 Inner join Inner Join logical operator returns each row that satisfies the first (top) input and the second (bottom) input join. This is the same effect as querying multiple tables with SELECT, so there are few internal connections. Another point to note is that join is inner join by default.  So we can omit the Inner keyword when we write the inner connection.   1.2 The following example illustrates the:sql> Select A.id,a.name,b.name from the Dave a inner join BL B on a.id=b.id;  --standard notation sql> Select A.id,a.name,b.name from Dave A joins BL B on a.id=b.id;  --this omits the inner keyword sql> Select a.id,a.name,b.name from Dave A,bl b where a.id=b.id; --Select multiple table queries from the results of these three SQL we can also see that their role is the same. 1.3 Natural Connection (Natural join) The natural connection is to look for those fields with the same data type and column names in both tables, then automatically connect them and return all results that match the criteria. Sql> Select Id,name from DAVe a natural join BL B; Here we do not specify the conditions for the connection, in fact Oracle has made a move for us, and the ID and name fields in the Dave table are connected to the ID and name fields in the BL table.  That is actually equivalent to sql> Select Dave.id,bl.name from the Dave join bl on dave.id = Bl.id and dave.name=bl.name; therefore, we can also understand the natural connection as one of the inner joins. Some notes about natural connections: (1). If there are multiple fields of the two tables that are naturally connected to the same name type, then they will be treated as a natural connection condition. (2). If the two tables that are naturally connected are only the same as the field names, but the data types are different, an error is returned. Two Self-connected self-join is a frequently used connection in SQL statements, and using self-joins can treat one mirror of its own table as another, allowing for some special data to be obtained. Example: A table in the Oracle's Scott schema is EMP. Each of the employees in the EMP has their own MGR (manager), and each manager himself is a company employee and has his own manager. Below we need to find out each employee's name and manager's name. What do we do at this time? If we have two of these tables teaching workers and MGR, then we can write SQL statements very well. Select worker.name,mgr.namefrom Worker,mgrwhere worker.id = mgr.id; But now we have only one EMP table. So we can use self-connection. The intention of self-connection is to treat a table as more than one table to make a connection. We can write SQL statements like this: Select Work.ename worker,mgr.ename Manager from scott.emp work, scott.emp mgr where work.mgr = Mgr.empno or Der by Work.ename;

　　

Oracle Table Connection mode (1)---