SQL connection query for Oracle databases

SQL connection query for Oracle databases

Preface
In reality, there is such a requirement that sometimes the data we need to query is in multiple tables, so how to query data from multiple tables
What about it? In this case, you need to use table connections to perform queries.
Cartesian Product
Before defining a connection, we need to simply understand cartesian products. I will not tangle with them, but just look at their actual results.
In fact, Cartesian product is a connection without a connection condition or the connection condition is invalid. For example, [SQL] select * from emp, dept; -- the result tends to be a huge number of records, it has no practical significance. We can see that cartesian products produce a huge set of meaningless records. We can use

Effective connection to avoid this situation and make it practical.
Connection Definition
The join condition is used based on the Cartesian Product. For example, the join condition is performed based on the same columns of two tables. If n tables are to be created
Add n-1 connection conditions. For example: [SQL] select * from emp, dept where emp. deptno = dept. deptno;
Connection Type
There are two types of connections: equijoin and non-equijoin.
There are also some other Connection Methods: Multi-connection, self-connection, and fixed operators.
Equijoin
The above has been used, that is, the where condition for table connection is that the column in one table is equal to the column in another table, usually the primary key and
Foreign key judgment and other connections. [SQL] select * from emp, dept where emp. deptno = dept. deptno; Tips: Because deptno exists in both tables, you must use the table. field Format. Otherwise, oracle considers the field as ambiguous.

You can also add constraints to the equijoin: [SQL] select * from emp, dept where emp. deptno = dept. deptno and emp. ename = 'clark ';
-- Only one record can be used to define the alias of the object. However, after the alias is defined, the field must be accessed through the alias.

Indicates that [SQL] select * from emp e, dept d where e. deptno = d. deptno and e. ename = 'clark' is accessed ';
Non-equivalent join
By observing the emp and salgrade tables under the scott user, we can know that there is no direct correspondence between them,
The values of the sal column of emp are not equijoin between LOSAL and hisal in the salgrade table.
[SQL] select * from emp e, salgrade s where e. sal between s. losal and s. hisal;
External Connection
When an external connection is used, the record with a NULL side on both sides of the connection is returned. The outer join operator is (+)
The outer join operator can be either on the left or on the right, but not on both the left and right sides,
The value of one side with (+) indicates that the value of this side is either equal to the other side or NULL.
Example: [SQL] <span style = "color: #000000;"> select * from emp e, dept d where e. deptno (+) = d. deptno;
-- Indicates that e. deptno can be NULL.
</Span>
Similarly, the situation on the right is the same, so we will not demonstrate it here.

Outer right connection
Alignment is aligned based on the table on the right of the join. if the content is insufficient, the null value is null. [SQL] select * from emp e right outer join dept d on (e. deptno = d. deptno); the result of this method is the same as the following [SQL] select * from emp e, dept d where e. deptno (+) = d. deptno;
Left Outer Join
Alignment is aligned based on the table on the left of the join. if the content is insufficient, the null value is supplemented. [SQL] select * from emp e left outer join dept d on (e. deptno = d. deptno );
Select * from emp e, dept d where e. deptno = d. deptno (+); you can see which side is done, and (+) where it is!

All external connections
In layman's terms: NULL can appear on the left and right, but it cannot appear at the same time)
Although (+) cannot be added to both sides, after 9i, you can use the following method to perform [SQL] select * from emp e full outer join dept d on (e. deptno = d. deptno); in this way, more records are found than the previous ones.

Self-connection
Sometimes you need to perform a self-Join Operation on the table. In fact, self-join is only a special case of equijoin.
Different aliases are differentiated into different tables.
 
Example: query the relationship between employees. The upper-level relationship [SQL] select e. ename | 'Works for '| NVL (m. ename, 'himself')
Relations from emp e, emp m where e. mgr = m. empno (+); based on the above learning, you can also write the following form
[SQL] select e. ename | 'Works for '| NVL (m. ename, 'himself')
Relations from emp e left outer join emp m on (e. mgr = m. empno); check the query results:
 
It's time to query data from multiple tables and connect! Over!