Oracle connection Query

Background:

The emp table has 14 records:

Four records in the dept table:

The salgrade table has five records:

I. CROSS JOIN)

Cross join: there are two types: explicit and implicit, without the ON clause. The returned result is the product of the two tables, also called Cartesian product.

For example, the following statements 1 and 2 have the same results (56 records ).

Statement 1: Implicit cross join, without cross join.

select empno,ename,sal,dname,loc from emp,dept

Statement 2: explicit cross join, using CROSS JOIN

select empno,ename,sal,dname,loc from emp CROSS JOIN dept

2. Internal Connection

An internal connection is used to query connections based on specified connection conditions. Only data that meets the connection conditions will appear in the result set.

When performing join queries between two tables, first find the first record in the first table, and then scan the second table from the beginning to find records that meet the conditions one by one, after finding it, splice it with the first record in the first table to form the first record in the result set. After the second table is scanned, the second record is queried from the first table, and the second record is scanned from the first table to find the matching records one by one, after finding it, splice it with the second record in the first table to form a record in the result set. Repeat the operation until all records in the first table are processed.

1. Equal connection

Two columns with the same meaning can be used to create equal join conditions. Only rows with the same value in the two tables of the connected column will appear in the query results.

For example, query the employee number, employee name, salary, Department number, and department name of the employee in department 10:

SELECT empno,ename,sal,emp.deptno FROM EMPjoin DEPT on EMP.DEPTNO = DEPT.DEPTNO and EMP.DEPTNO=10

The result is as follows:

2. Unequal connections

If the operator in the join condition is not an equal sign but an operator of other relations, this becomes an unequal connection.

For example, to query the salaries of employees in department 10:

select empno,ename,sal,grade from emp join salgrade on sal>losal and sal
The result is as follows:


3. Self-connection
A self-join is a connection between two or more tables in the same table or view.
For example, query the employee ID of all employees, employee name, employee name of the employee's leadership, and employee ID:
select work.empno,work.ename,manager.empno,manager.ename from emp work join emp manager on work.mgr=manager.empno
The result is as follows:

 
Iii. External Connection
External join refers to adding records that do not meet the connection conditions in a connection table to the result set based on the internal join.
When connecting left Outer Join and right Outer Join, a table is used as the base table, and the table content is displayed in full, and the matching content of the two tables is added. If the data in the base table is not recorded in the other table. The column in the row of the associated result set is displayed as NULL ).
1. left Outer Join
The left Outer Join refers to adding records that do not meet the join conditions in the left table of the join operator to the result set based on the inner join. The column on the right of the join operator is filled with NULL.
For example, query the Department name, employee number, employee name, and name of all other departments of Department 10:
select dname,empno,ename from dept left join emp on dept.deptno=emp.deptno and dept.deptno=10;
The result is as follows:

2. Right Outer Join
The outer right join refers to adding records that do not meet the join conditions in the table on the right of the join operator to the result set based on the inner join. The column on the left of the corresponding join operator is filled with NULL.
For example, query the Department name and employee number of Department 20, employee name and employee number of all other departments, and employee name:
select empno ,ename,dname from dept right join emp on dept.deptno=emp.deptno and dept.deptno=20;
The result is as follows:

3. Full connection
Full outer join refers to adding non-conforming records in the tables on both sides of the join operator to the result set based on internal and external connections.
For example, query all department names and employee names:
select dname,ename from emp full join dept on emp.deptno=dept.deptno
The result is as follows: