SQL Server中的內連接(inner join)

一、建庫和建表

create database scortuse scortcreate table emp(  empno int primary key,  ename nvarchar(10),  sal int,  deptno int)insert into emp values (7369,'smith',3000,20);insert into emp values (7499,'allen',1500,10);insert into emp values (7521,'ward',2850,30);insert into emp values (7566,'jones',2000,30);insert into emp values (7654,'martin',5000,10);insert into emp values (7698,'blake',1800,30); create table dept(  deptno int primary key,  dname nvarchar(10),  loc nvarchar(10))insert into dept values (10,'accounting','new york');insert into dept values (20,'research','dallas');insert into dept values (30,'sales','chicago');insert into dept values (40,'operations','boston'); create table salgrade(  grade int primary key,  lostl int,  hisal int)insert into salgrade values (1,700,1200);insert into salgrade values (2,1201,1400);insert into salgrade values (3,1401,2000);insert into salgrade values (4,2001,3000);insert into salgrade values (5,3001,6000);
二、內連接的用法(inner join)
解釋：將兩個表或兩個以上的表以一定的連接條件連接起來，從中檢索出滿足條件的數據。
注意：inner join 可以簡寫為 join
1.select . from A,B 的用法
1.產生的結果：行數是A和B的乘積，列數是A和B之和。或者說把A表的每一條記錄和B表的每一條記錄組合在一起，形成的是個笛卡爾積。
2.select . from A,B和select . from B,A的輸出查詢結果本質上是一樣的，只是看起來A，B表的前後順序不一樣而已。
3.實例演示：
2.select . from A,B  where .的用法(sql92標準)
1.select . from A,B  where .和select . fromB,A  where .查詢結果本質上一樣
select * from emp,dept where sal>2000
2.如果倆個表或多個表有相同列名的時候，where後面必須加表名。
select * from emp,dept where deptno=10 
select * from emp,dept where emp.deptno=10
select * from emp,dept where dept.deptno=10
3.三張表連接在一起
select "E".ename,"D".dname,"S".grade  from emp "E",dept "D",salgrade "S"  where "E".deptno="D".deptno and      ("E".sal>"S".lostl and "E".sal<"S".hisal) and      "E".sal>2000
3.select . from A join B on .的用法(sql99標準)
1.select . from A join B on .和select . from B join A on .查詢結果本質上一樣
2.倆張表連接在一起
select * from emp  join dept  on 1=1
select * from emp  join dept  on emp.deptno=dept.deptno
3.三張表連接在一起
select "E".ename,"D".dname,"S".grade  from emp "E"  join dept "D"  on "E".deptno="D".deptno  join salgrade "S"  on "E".sal>"S".lostl and "E".sal<"S".hisal  where "E".sal>2000