SQL connection query for Oracle databases

In reality, there is such a requirement. Sometimes the data we need to query is in multiple tables. How can we query data from multiple tables? In this case, you need to use table connections for queries.

In reality, there is such a requirement. Sometimes the data we need to query is in multiple tables. How can we query data from multiple tables? In this case, you need to use table connections for queries.

Preface

In reality, there is such a requirement. Sometimes the data we need to query is in multiple tables. How can we query data from multiple tables? 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:

Select * from emp, dept; -- the result tends to be a huge number of records, no practical significance

We can see from the above that Cartesian product is a huge meaningless set of records. we can avoid this situation by using valid connections in the where clause to 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 connected, n-1 join conditions are added. For example:

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, and usually the primary key and foreign key judgment and other connections.

Select * from emp, dept where emp. deptno = dept. deptno;

Tips: Because deptno exists in both tables, you must use the form of table. Field. Otherwise, Oracle considers it ambiguous.

You can also add constraints to the equijoin.

Select * from emp, dept where emp. deptno = dept. deptno and emp. ename = 'clark ';

-- Only one record is returned.

You can also define the alias of the target object. However, after defining the alias, you must use the alias to access the field.

Select * from emp e, dept d where e. deptno = d. deptno and e. ename = 'clark ';

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.

Select * from emp e, salgrade s where e. sal between s. losal and s. hisal;