Basic query statement for Oracle 10 Gb database-bottom-connection & amp; subquery

Tutorial 18: Table connection Query

The purpose of this experiment is to master basic joint queries.

Table join

To obtain information from multiple tables, we can join the tables for query under certain conditions.

Related reading:

Basic query statement for Oracle 10 Gb database-above
Basic query statement for Oracle 10 Gb database-Medium-Function
Basic query statement for Oracle 10 Gb database-bottom-connection & subquery

Cartesian (flute) Connection

When multiple tables are queried together, the correct join condition is not given. The result is that all rows in the first table are multiplied by all rows in the second table, obtain the result set of n * m rows.

In general, the flute connection is not the result we need.

However, if a table has a row, the result may be correct.

SQL> select ename, dname from emp, dept;

ENAME DNAME

------------------------

SMITH ACCOUNTING

ALLEN ACCOUNTING

........

SMITH RESEARCH

ALLEN RESEARCH

........

SMITH SALES

ALLEN SALES

........

SMITH OPERATIONS

ALLEN OPERATIONS

........

56 rows selected

SQL>

The result is that each employee has a class in each department, 4*14 = 56, which is not what we want.

A correct connection condition must be specified to avoid the flute connection.

Equijoin

Specify an equal connection condition in the connection.

SQL> select ename, dname from emp, dept where emp. deptno = dept. deptno;

When the column names are repeated in two tables, you must add the table prefix to distinguish them to avoid ambiguous definitions.

Table alias

1. Easy to write

2. Differentiate tables with the same name

3. Once an alias is defined, the table name is invalid.

4. Valid only in this statement

5. It is defined as the table name followed by the alias, and each interval is empty.

SQL> select ename, dname from emp e, dept d where e. deptno = d. deptno;

SQL99 Writing Method

Select ename, dname from emp e join dept d on (e. deptno = d. deptno );

The efficiency is the same, and SQL99 is the National Standard

The column alias. to distinguish the names of the same columns, this is the essence of the alias.

SQL> select ename, dname, e. deptno, d. deptno

From emp e, dept d

Where e. deptno = d. deptno;

The preceding two columns are named deptno, which cannot be distinguished.

SQL> select ename, dname, e. deptno "Employee table", d. deptno "department table"

From emp e, dept d

Where e. deptno = d. deptno;

1 2 3 4 5 Next Page