Common Oracle connection query methods and functions

I. Common connection query methods in Oracle (exercises with built-in oracle tables)

1. left outer join (left outer join/left join)

Left join is based on records in the left table. All records in the left table are displayed. Only records meeting the query conditions are displayed in the right table. Fill the right table with null.

Example: select * from emp t1 left join dept t2 on t1.deptno = t2.deptno;

The left Outer Join can be implemented using (+). The '(+)' behind the table indicates that the table is a matching table. The '(+)' keyword is used as the where clause.

Example: select * from emp t1 left join dept t2 on t1.deptno = t2.deptno;

2. right outer join (right outer join/right join)

Right join is based on records in the right table. All records in the right table are displayed. Only records meeting the query conditions are displayed in the left table, and null is used to fill the left table with insufficient records.

Example: select * from emp t1 left join dept t2 on t1.deptno = t2.deptno;

The outer right join can be implemented using '(+)' and '(+)' with the where keyword.

The difference between the left and right Outer Join modes is: Use '(+)' to perform full table scan on the left/right table and index scan on the right/left table. In another way, both tables are full-Table scans.

3. full outer join (full outer join/full join)

No restrictions are imposed on the left and right tables. All records are displayed. If the two tables are insufficient, use null to fill them. All external connections do not support (+.

Example: select * from dave a full join bl B on a. id = B. id;

4. Self-connection

Self-join is a commonly used connection method in SQL statements. Using Self-join, you can treat an image of your table as another table, so that you can get some special data.

For example, query the name of the employee in the emp table and the name of the employee's manager.

Select t1.ename "employee", ', t2.ename "manager" from emp t1, emp t2 where t1.mgr = t2.empno;

5. natural connection

The natural connection is to search for fields with the same class name and column name in the two tables, then automatically connect them, and return all matching results.

Example: select deptno from emp natural join dept;

The connection conditions are not specified here. In fact, oracle only connects the deptno field in the emp table to the deptno field in the dept table. That is equivalent:

Select deptno from emp t1 natural join dept t2;

6. inner join/join)

Inner join returns each row that matches the first (top) input and the second (bottom) Input join. This is the same as querying multiple tables using the select statement. Therefore, few internal links are used.

Select t1.ename from emp t1 inner join dept t2 on t1.deptno = t2.deptno;

1 2 Next Page