Oracle connection Query

Background: There are 14 records in the emp table: 4 records in the dept table: 5 records in the salgrade table: 1. CROSSJOIN: there are two types, explicit and implicit, without the ON clause, return the product of two tables, also called Cartesian product. For example, the following statements 1 and 2 have the same results (both 56

Background: There are 14 records in the emp table: 4 records in the dept table: 5 records in the salgrade table: 1. cross join ): there are two types: explicit and implicit. Without the ON clause, the product of the two tables is returned, also called Cartesian product. For example, the following statements 1 and 2 have the same results (both 56

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;
  
In this example, the first thing to ensure is: I want to find out the department name of Department 10, even if there is no employee in department 10, the names of all departments of Department 10 must also be displayed, and the department names are in the dept table. Therefore, the dept table is placed on the left. The first three rows with matching data are the data found when Join is used, that is, the data that meets the On condition.

  
The result is as follows:
  


  
Note: My personal understanding about left join
  
Assume that you have two tables, capital settlement documents (JSDJXX) and bit information (DWXX)
A table is used to store the information of the fund settlement documents (the fund settlement documents must have units, but the unit information in the fund settlement documents must be numbers ), another table is used to store the unit information (for example, the data in the unit table includes the unit number, unit name, and internal unit code). Now I want to check the information of the fund settlement document, at the same time, I want the organization information in the fund settlement document to display the organization name instead of the number. At this time, I need to use left join to associate the organization information table, because the data I need is the information in the fund settlement document, even if the organization name corresponding to a unit number cannot be found in the organization information table, I still want to display this data, therefore, use left join instead of join.
  
SELECT JSDJXX_DWBH,DWXX_DWMC  from  JSDJXXleft JOIN DWXX ON JSDJXX_DWBH = DWXX_DWBH 
  
By executing the preceding SQL statement and getting ds, I can bind the unit name when binding data to Gridcontrol.
  
For example:
  
SELECT JSDJXX_DWBH,DWXX_DWMC as DWMCfrom  JSDJXXleft JOIN DWXX ON JSDJXX_DWBH = DWXX_DWBH 
You can use DWMC to bind a column name to Dev Gridcontrol.
  
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:
  

Note:
  
1. Summary of left and right outer connections:
  
      Left Outer Join(Left outer join) tells the DBMS to generate a result table, including not only matching rows, but also non-matching rows in the LEFT table of the JOIN keyword (in the from clause. The left Outer Join can actually be expressed:
Left Outer Join = Inner join + mismatched tuples in the left table
The value of the missing attribute in the right table is NULL.
      Outer right connection(Right outer join) tells the DBMS to generate a result table, including not only matching rows, but also non-matching rows in the RIGHT table of the JOIN keyword (in the from clause. The outer right connection can actually be expressed:
Outer right join = Inner join + mismatched tuples in the right table
The value of the missing left table is NULL.
      All external connections(Full outer join) tells the DBMS to generate a result table. In addition to matching rows, the table on the left and the table on the right contain the JOIN keyword (in the from clause. All external connections can be expressed:
Total Outer Join = Inner join + mismatched tuples in the left table + mismatched tuples in the right table.
The missing attribute values in the Left or Right tables are expressed as NULL.

  
2. Recommended for similar articles: Click to open the link
  
3. Summary
  
The JOIN types that you can use and their differences are listed below.
  
 

   
  
JOIN: If the table has at least one match, the row is returned. Example: Click the open link
   
  
Left join: returns all rows from the LEFT table even if there is no match in the right table. Example: Click the open link
   
  
Right join: returns all rows from the RIGHT table even if the left table does not match. Example: Click the open link
   
  
Full join: if one of the tables matches, the row is returned. Example: Click the open link