Intra-Connection in SQL Server

Internal connection is the focus of the query, but also the focus of database learning.

The three tables in the Scott database are to be queried:

EMP Table:

Dept Table:

Salgrade table:

1, select .... usage of from A, b

--emp is 14 rows and 8 columns, Dept is 5 rows 3 columns select * from Emp,dept;   --Output is 70 rows and 11 columns

The resulting result is a Cartesian product:

2. Select .... from A, B where ... Usage of

SELECT * from EMP, dept where empno = 7369;   --The where filter for the generated Cartesian product

3, select .... usage of from a join B on

Select *from emp "E" Join dept "D"    --join is connected on  1 = 1;       --on is a connection condition on cannot save a join must have on

Because the connection condition is 1 = 1 is always true, the result of the output is 70 rows and 11 columns, followed by:

SELECT * from Emp,dept;

Outputs the same result.

So:

SELECT * from emp "E" Join dept "D"                        --sql99 standard on "E". Deptno = "D". Deptno;

The output of the result is:

Its principle is as follows (important):

In fact, this follows:

SELECT * from EMP, dept                       where emp.deptno = Dept.deptno;            --SQL92 Standard

The output is the same, it is recommended to use the SQL99 standard.

Example:

1. Export the name of the employee with a salary greater than 2000, the names of the department, and the salary level.

--SQL99 Standard Select "E". ename "Employee Name", "E". Sal "Payroll", "D". Dname "department name", "S". GRADE "wage level" from EMP "E" Join dept "D" to "e". Deptno = "D". Deptnojoin Salgrade "s" on "E". Sal >= "S". Losal and "E". Sal < ; = "S". Hisalwhere "E". Sal > 2000;

--SQL92 Standard Select "E". ename "Employee Name", "E". Sal "Payroll", "D". Dname "department name", "S". GRADE "wage level" from emp "E", dept "D", Salgrade "s" Where ("E". Deptno = "D". Deptno) and ("E". Sal >= "s". losal) and ("E". Sal & Lt;= "S". Hisal and "E". Sal > 2000);

2. The name, salary, wage level and department name of each employee who does not contain a in the output name of all employees who are in the top three of the salary.

Select Top 3 "E". ename, "E". Sal, "S". grade, "D". Dnamefrom emp "E" Join dept "D" on "e". Deptno = "D". Deptnojoin salgrade "S" O N "E". Sal between "S". Losal and "S". Hisalwhere "E". ename not as '%a% ' order by ' e '. Sal desc;

3. Find the number of each department grade of average wage for all employees in the department

Select "T". Deptno, "T". Avg_sal "average wage", "S".  GRADE "wage level" from Salgrade "S" Join (select Deptno, avg (SAL) as "Avg_sal" from Empgroup by Deptno             -temporary table, find out department number and average employee's salary) "T" on                         "T". " Avg_sal "between" S ". Losal and "S". Hisal;

Intra-Connection in SQL Server