Oracle connection type

Oracle connection type
If you connect multiple tables by using the select clause and the from clause, the query result is a table generated by the Cartesian product.
The table generated by the so-called flute product is the table generated when each row in a basic table is connected to each row in another basic table, the number of rows in the query result is the product of the number of rows in two basic tables.
Connect to the query using the emp and dept tables in oracle as follows:

SQL> select * from emp, dept; empno ename job mgr hiredate sal comm deptno dname loc ------ ----------- --------- ---------- ------ ------------ ----------------- 7369 smith clerk 7902 17-12 month-80 800 20 10 accounting new Employee allen salesman 7698 20-2 month-81 1600 300 30 10 accounting new YORK7521 ward salesman 7698 22-2 month-81 1250 500 30 10 accounting new YORK7566 jones manager 7839 02-month-81 2975 20 10 accounting new YORK7654 martin salesman 7698 28-9 month-81 1250 1400 30 10 accounting new YORK7698 blake manager 7839 01-5 month-81 2850 30 10 accounting new YORK7782 clark manager 7839 09-6 month-81 2450 10 10 10 accounting new YORK7788 scott analyst 7566 24-1 month-87 3000 20 10 accounting new YORK7839 king president 17-11 month-81 5000 10 10 accounting new YORK7844 turner salesman 7698 08-9 month-81 1500 0 30 10 accounting new YORK7876 adams clerk 7788 02-4 month-87 1100 20 10 accounting new YORK7900 james clerk 7698 03-12 month-81 1800 30 10 accounting new YORK7902 ford analyst 7566 03-12 month-81 3000 20 10 accounting new YORK7934 miller clerk 7782 23-1 month -82 1300 10 10 accounting new YORK7935 xiaoxue manager 7839 01-3 month-98 5000 500 20 10 accounting new YORK7369 smith clerk 7902 17-12 month-80 800 20 20 RESEARCH DALLAS7499 allen salesman 7698 20-2 month- 81 1600 300 30 20 RESEARCH DALLAS7521 ward salesman 7698 22-2 month-81 1250 500 30 20 RESEARCH DALLAS7566 jones manager 7839 02-month-81 2975 20 20 20 RESEARCH DALLAS7654 martin salesman 7698 28-9 month-81 1250 1400 30 20 RESEARCH DALLAS7698 blke MANAGER 7839 01-5 months-81 2850 30 20 RESEARCH DALLAS7782 clark manager 7839 09-6 months-81 2450 10 20 RESEARCH DALLAS7788 scott analyst 7566 24-1 month-87 3000 20 RESEARCH DALLAS7839 king president 17-11 month-81 5000 10 20 RESEARCH DALLAS7844 turner salesman 7698 08-9 month-81 1500 0 30 20 RESEARCH DALLAS7876 adams clerk 7788 month-87 1100 20 20 RESEARCH DALLAS7900 james clerk 7698 03-12 months-81 1800 30 20 RESEARCH DALLAS7902 ford analyst 7566 03-12 months-81 3000 20 20 RESEARCH DALLAS7934 miller clerk 7782 23-1 months-82 1300 10 20 RESEARCH DALLAS7935 xiaoxue manager 7839 01-3 months-98 5000 500 20 20 RESEARCH DALLAS7369 smith clerk 7902 17-12 months-80 800 20 30 SALES chicag7499 allen salesman 7698 20-81 1600 300 30 30 SALES chicag7521 ward salesman 7698 22-2 month-81 1250 500 30 30 SALES chicag7566 jones manager 7839 month-81 2975 20 30 SALES chicag7654 martin salesman 7698 month-81 1250 1400 30 30 30 SALES chicag7698 blke MANAGER 7839 month-81 month-81 2850 30 30 SALES chicago 7782 clark manager 7839-81 2450 10 30 SALES Chicago 7788 scott analyst 7566 24-1 month-87 3000 20 30 SALES Chicago 7839 king president 17-11 month-81 5000 10 30 SALES Chicago 7844 turner salesman 7698 08-9 month-81 1500 0 30 30 SALES Chicago 7876 adams clerk 7788 month-87 1100 20 30 SALES Chicago 7900 james clerk 7698 month-81 1800 30 30 SALES Chicago 7902 ford analyst 7566 month-81 3000 20 30 SALES chicag7934 miller clerk 7782 23-1 month-82 1300 10 30 SALES chicag7935 xiaoxue manager 7839 01-3 month-98 5000 500 20 30 SALES chicag7369 smith clerk 7902 17-12 month-80 800 20 40 OPERATIONS BOSTON7499 allen salesman 7698 20-2 month-81 1600 300 30 40 OPERATIONS BOSTON7521 ward salesman 7698 22-2 month-81 1250 500 30 40 OPERATIONS BOSTON7566 jones manager 7839 02-month-81 2975 20 40 OPERATIONS BOSTON7654 martin salesman 7698 28-9 month-81 1250 1400 30 40 OPERATIONS BOSTON7698 blke MANAGER 7839 01-5 month-81 2850 30 40 OPERATIONS BOSTON7782 clark manager 7839 09-6 month-81 2450 10 40 OPERATIONS BOSTON7788 scott analyst 7566 24-1 month-87 3000 20 40 OPERATIONS BOSTON7839 king president 17-11 month-81 5000 10 40 OPERATIONS BOSTON7844 turner salesman 7698 08-9 month-81 1500 0 30 40 OPERATIONS BOSTON7876 adams clerk 7788 02-04 month-87 1100 20 40 OPERATIONS BOSTON7900 james clerk 7698 03-12 months-81 1800 30 40 OPERATIONS BOSTON7902 ford analyst 7566 03-12 months-81 3000 20 40 OPERATIONS BOSTON7934 miller clerk 7782 23-1 month-82 1300 10 40 OPERATIONS BOSTON7935 xiaoxue manager 7839 01-3 month-98 5000 500 20 40 OPERATIONS BOSTON

Select 60 rows.
The above 60 rows of data are obtained, but there are a lot of redundant data in the above data. We can use the where clause to remove some redundant data:

SQL> select * from emp, dept where emp. deptno = dept. deptno; empno ename job mgr hiredate sal comm deptno dname loc ------ ----------- --------- ---------- ------ ------------ ----------------- 7369 smith clerk 7902-12 months-80 800 20 RESEARCH lead allen salesman 7698 20-2 months -81 1600 300 30 30 SALES chicag7521 ward salesman 7698 22-2 month-81 1250 500 30 30 SALES chicag7566 jones manager 7839 02-month-81 2975 20 20 RESEARCH DALLAS7654 martin salesman 7698 28-9 month-81 1250 1400 30 30 SALES chicag7698 blke MANAGER 7839 01-5 months-81 2850 30 30 SALES chicag7782 clark manager 7839 09-6 months-81 2450 10 10 accounting new YORK7788 scott analyst 7566 24-1 month-87 3000 20 20 RESEARCH DALLAS7839 king president 17-11 month-81 5000 10 10 accounting new YORK7844 turner salesman 7698 08-9 month-81 1500 0 30 30 SALES chicag7876 adams clerk 7788 02-4month-87 1100 20 20 RESEARCH DALLAS7900 james clerk 7698 03-12 months-81 1800 30 30 SALES Chicago 7902 ford analyst 7566 03-12 months-81 3000 20 20 RESEARCH DALLAS7934 miller clerk 7782 23-1 month-82 1300 10 10 accounting new YORK7935 xiaoxue manager 7839 5000-98 500 20 20 RESEARCH DALLAS

15 rows have been selected.

SQL>
The result is much simpler than the preceding one.

In the from clause of the connection query, multiple tables can be separated by commas. In addition to this form, SQL supports the use of the keyword join for join.
In the from clause, the syntax for using join is as follows:
From join_table1 join_type join_table2 [on (join_condition)]
[Join_type... on join_condition,...]
Join_table1 and join_table2 indicate the names of tables involved in the join operation.
Join_type indicates the connection type. The connection types include inner join (inner join), outer join (outer join), and cross join (cross join)
Join_condition join condition, which consists of columns, comparison operators, and logical operators in the connected table. You can also use multiple groups of join_type... on join_condition... clauses to connect multiple tables.


Internal Connection
The most common connection query method for internal connections, which is specified using the inner join keyword. If the join keyword is used for knowledge, internal join is represented by default.
The inner join uses the comparison operator to compare some columns in the connected table and list the data rows in the table that match the connection conditions.
According to the comparison method, internal connections are classified into equivalent connections, natural connections, and unequal connections.
1. equijoin
The so-called equijoin refers to the use of the equal sign operator in the connection condition to compare the connected values, that is, the query connected by equal column values.
For example, use inner join to connect two different tables, emp and dept. on is used to set the connection conditions and the where clause is used to limit the query range. Retrieve the employee information of the accounting department as follows:

SQL> select empno,ename,sal,d.deptno,dname from emp e inner join dept d on e.deptno=d.deptno where dname='ACCOUNTING';EMPNO ENAME SAL DEPTNO DNAME---------- ---------- ---------- ---------- --------------7782 CLARK 2450 10 ACCOUNTING7839 KING 5000 10 ACCOUNTING7934 MILLER 1300 10 ACCOUNTING

We can see from the above that the database only uses three records whose dname is accounding.
Similarly, we can select a record with a department number greater than 20.

SQL> select empno,ename,sal,d.deptno,dname from emp e inner join dept d on e.deptno=d.deptno where d.deptno>20;EMPNO ENAME SAL DEPTNO DNAME---------- ---------- ---------- ---------- --------------7499 ALLEN 1600 30 SALES7521 WARD 1250 30 SALES7654 MARTIN 1250 30 SALES7698 BLAKE 2850 30 SALES7844 TURNER 1500 30 SALES7900 JAMES 950 30 SALES

You have selected 6 rows.

2. Unequal connections
The unequal connection refers to the use of comparison operators other than equal signs in the connection conditions to form a non-equivalent connection query. Possible comparison operators include >,<,>=, <=, <>( not equal ),! =, Like, in and.
For example, you can use the emp table and salgrade table to query the employee's salary level. Use the between operator to establish unequal connections,
Salgrade is the wage level. The data in this table is as follows. The wage is divided into 5 levels. Each data entry contains a minimum wage and a maximum wage:

SQL> select * from salgrade;GRADE LOSAL HISAL---------- ---------- ----------1 700 12002 1201 14003 1401 20004 2001 30005 3001 9999SQL> select empno,ename,sal,grade from emp e inner join salgrade s on e.sal between s.losal and s.hisal;EMPNO ENAME SAL GRADE------ --------- ------ ----------7369 SMITH 800 17876 ADAMS 1100 17521 WARD 1250 27654 MARTIN 1250 27934 MILLER 1300 27499 ALLEN 1600 37844 TURNER 1500 37900 JAMES 1800 37566 JONES 2975 47698 BLAKE 2850 47782 CLARK 2450 47788 SCOTT 3000 47902 FORD 3000 47839 KING 5000 57935 XIAOXUE 5000 5

15 rows have been selected.



3. natural connection
The natural connection is to find the fields with the same column name and data type in the two tables, connect the two tables with the same fields, and return all results that meet the conditions.
To use a natural connection, you need to specify the natural join connection keyword, but do not need to specify the connection conditions.
Note the following when using a natural connection:
(1) If multiple fields in the two tables that are naturally connected meet the same name and data type, they will be used as conditions for natural connection.
(2) If the two tables that are naturally connected only have the same field name and the Data Type of the field is different, an error will be returned when this field is used for connection.
(3) because oracle supports natural connections, we should use the same name and data type for de fields with the same meaning in different tables during table design, if you always use the same name for the primary key and the foreign key, what can we do with the natural connection.

SQL> select e.ename,e.sal,deptno,d.dname from emp e natural join dept d;ENAME SAL DEPTNO DNAME--------- ------ ------ ------------SMITH 800 20 RESEARCHALLEN 1600 30 SALESWARD 1250 30 SALESJONES 2975 20 RESEARCHMARTIN 1250 30 SALESBLAKE 2850 30 SALESCLARK 2450 10 ACCOUNTINGSCOTT 3000 20 RESEARCHKING 5000 10 ACCOUNTINGTURNER 1500 30 SALESADAMS 1100 20 RESEARCHJAMES 1800 30 SALESFORD 3000 20 RESEARCHMILLER 1300 10 ACCOUNTINGXIAOXUE 5000 20 RESEARCH

The deptno fields in the emp and dept tables are the same.

SQL> select * from emp e natural join dept d; deptno empno ename job mgr hiredate sal comm dname loc ------ ----------- --------- ---------- ----- ------------ ------- 20 7369 smith clerk 7902 17-12 month-80 800 RESEARCH DALLAS30 7499 allen salesman 7698-81 1600 300 SALES chicag30 7521 ward salesman 7698 22-2 month-81 1250 500 SALES chicag20 7566 jones manager 7839 02-month-81 2975 RESEARCH DALLAS30 7654 martin salesman 7698 28-9 month-81 1250 SALES chicag30 1400 blake manager 7839 01-5 month-81 2850 SALES Chicago 10 7782 clark manager 7839 09-6 month-81 2450 accounting new YORK20 7788 scott analyst 7566 24-1 month-87 3000 RESEARCH DALLAS10 7839 king president 17-11 month-81 5000 accounting new YORK30 7844 turner salesman 7698 month-81 1500 0 SALES chicag20 7876 adams clerk 7788 month-87 1100 RESEARCH DALLAS30 7900 james clerk 7698 month-81 1800 SALES Chicago 20 7902 FORD ANALYST 7566 03-12 months-81 3000 RESEARCH DALLAS10 7934 miller clerk 7782 23-1 month-82 1300 accounting new YORK20 7935 xiaoxue manager 7839 01-3 months-98 5000 500 RESEARCH DALLAS

15 rows have been selected.