Basic Principle of Cartesian sets, equijoin, non-equijoin, outer join, and Self join

1. Basic principles of Cartesian sets two tables if the Cartesian set operation (1) the number of rows is the result of multiplying the number of rows of two tables (2) the column is a collection of two tables case: Incorrect query method: selectcount (e. EMPNO) fromempe, deptd; correct query method: selectcount (e. EMPNO) fromempe, deptdwheree. deptnod. deptno; 2. and so on

1. Cartesian set basic principle two tables if the Cartesian set operation (1) the number of rows is the result of multiplying the number of rows of two tables (2) the column is a collection of two tables case: Incorrect query method: select count (e. EMPNO) from emp e, dept d; correct query method: select count (e. EMPNO) from emp e, dept d where e. deptno = d. deptno; 2. and so on

1. Basic principles of Cartesian Sets

If two tables have Cartesian sets

(1) the number of rows is the result of multiplying the number of rows in two tables.

(2) The column is a collection of two tables.

Case:

Error query method:

Select count (e. EMPNO)

From emp e, dept d;

Correct query method:

Select count (e. EMPNO)

From emp e, dept d

Where e. deptno = d. deptno;

2. equijoin

Eg: Query employee information, employee ID, name, monthly salary, Department name

Select e. empno, e. ename, e. sal, d. dname

From emp e, dept d

Where e. deptno = d. deptno;

3. Unequal connections

Eg: Query employee information, employee ID, name, monthly salary, salary level

Select e. empno, e. ename, e. sal, s. grade

From emp e, salgrade s

Where e. sal> = s. losal and e. sal <= s. hisal; (can be replaced by between and)

4. External Connection

Eg: Number of employees by Department: department no., Department name, department count

Ideas:

A: count the number of people in each department. Divide the data of each department ...., Group .....

B: Number of employees in each department, which must be associated with the employee table (because the number of employees is determined by the employee table, the employee table must be retrieved)

Therefore, multi-table queries are required.

Symptom 1 (a department is missing)

Select d. deptno, d. dname, count (e. empno)

From dept d, emp e

Where d. deptno = e. deptno

Group by d. deptno, d. dname;

Analysis:

Our expectation is to count Department 40...

The reason why Department 40 was not counted: There were no employees in Department 40 in the employee table... d. deptno = e. deptno (d.40 = e.40)

Essence: the equivalence condition does not meet...

Reason for External Connection Technology

+ Left Outer Join: + is located on the right of... =, and left Outer Join ....

+ Right outer join: + signs are on the left of... =, and left Outer Join ....

To display all the columns in the department table, you need to find the Department table)

Left Outer Join:

Select d. deptno, d. dname, count (e. empno)

From dept d, emp e

Where d. deptno = e. deptno (+)

Group by d. deptno, d. dname;

5. Self-connection

-- Query employee information and boss Information

Display: *** the boss is ****

--- Thought 1: employee information query employee table

Check the boss information and employee table...

--- Idea 2 the boss of the employee table is the employee of the boss table

Selecte. ename, B. ename

Fromemp e, emp B

Wheree. mgr = B. empno

Self-Connection case:

Select e. ename | 'The Boss is '| nvl (B. ename, 'his own ')

From emp e, emp B

Where e. mgr = B. empno (+ );