在join中，on和where的区别

1、on条件是在生成临时表时使用的条件，它不管on中的条件是否为真，都会返回左边表中的记录。

2、where条件是在临时表生成好后，再对临时表进行过滤的条件。这时已经没有left  join的含义（必须返回左边表的记录）了，条件不为真的就全部过滤掉

建议使用时使用where过滤掉不符合的条件。

SELECT sc.DEGREE,st.SNAME,c.cname from SCORE sc join COURSE c on sc.cno = c.cno 
LEFT join TEACHER t on c.tno = t.tno 
left join STUDENT st on st.sno = sc.sno 
and t.DEPART = '计算机系'

SELECT sc.DEGREE,st.SNAME,c.cname from SCORE sc join COURSE c on sc.cno = c.cno 
LEFT join TEACHER t on c.tno = t.tno 
left join STUDENT st on st.sno = sc.sno 
where t.DEPART = '计算机系'

建表语句：

CREATE TABLE STUDENT
(
 SNO       VARCHAR(3) NOT NULL, 
 SNAME     VARCHAR(4) NOT NULL,
 SSEX      VARCHAR(2) NOT NULL, 
 SBIRTHDAY DATETIME,
 CLASS     VARCHAR(5)
 ) default charset=utf8;
 -- VARCHAR(M) 每个值占用的字节长度=该值字节数+1，M只是其最大值--
 -- MySQL retrieves and displays DATETIME values in 'YYYY-MM-DD HH:MM:SS' format--

CREATE TABLE COURSE
(
 CNO   VARCHAR(5)  NOT NULL, 
 CNAME VARCHAR(10) NOT NULL, 
 TNO   VARCHAR(3) NOT NULL
 )default charset=utf8;

 CREATE TABLE SCORE 
(
 SNO    VARCHAR(3)     NOT NULL, 
 CNO    VARCHAR(5)     NOT NULL, 
 DEGREE NUMERIC(10, 1) NOT NULL
 ) default charset=utf8;
 -- NUMERIC(5,2)的赋值范围是[-999.99,999.99]--
 -- NUMERIC(M)等价于NUMERIC(M,0), NUMERIC等价于NUMERIC(10)--

 CREATE TABLE TEACHER 
(
 TNO       VARCHAR(3)  NOT NULL, 
 TNAME     VARCHAR(4)  NOT NULL, 
 TSEX      VARCHAR(2)  NOT NULL, 
 TBIRTHDAY DATETIME    NOT NULL, 
 PROF      VARCHAR(6), 
 DEPART    VARCHAR(10) NOT NULL
 )default charset=utf8;

 ALTER TABLE STUDENT ADD PRIMARY KEY (SNO);
 ALTER TABLE SCORE   ADD PRIMARY KEY (SNO,CNO);
 ALTER TABLE COURSE  ADD PRIMARY KEY (CNO);
 ALTER TABLE TEACHER ADD PRIMARY KEY (TNO);

-- 先添加外键，再添加值--
-- 外键在两张table中的数据类型须一致--
 ALTER TABLE SCORE   ADD CONSTRAINT FK_SCORE_STUDENT  FOREIGN KEY (SNO) REFERENCES STUDENT(SNO);
 ALTER TABLE SCORE   ADD CONSTRAINT FK_SCORE_COURSE   FOREIGN KEY (CNO) REFERENCES COURSE(CNO);
 ALTER TABLE COURSE  ADD CONSTRAINT FK_COURSE_TEACHER FOREIGN KEY (TNO) REFERENCES TEACHER(TNO);

 -- 先对REFERENCES中所指的表添加值--
 INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) 
 VALUES (108 ,'曾华' ,'男' ,'1977-09-01',95033);
 INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) 
 VALUES (105 ,'匡明' ,'男' ,'1975-10-02',95031);
 INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) 
 VALUES (107 ,'王丽' ,'女' ,'1976-01-23',95033);
 INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) 
 VALUES (101 ,'李军' ,'男' ,'1976-02-20',95033);
 INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) 
 VALUES (109 ,'王芳' ,'女' ,'1975-02-10',95031);
 INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) 
 VALUES (103 ,'陆君' ,'男' ,'1974-06-03',95031);		 

INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) 

 VALUES (110 ,'wkb' ,'男' ,'1974-06-03',95031);





 INSERT INTO TEACHER(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) 
 VALUES (804,'李诚','男','1958-12-02','副教授','计算机系');
 INSERT INTO TEACHER(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) 
 VALUES (856,'张旭','男','1969-03-12','讲师','电子工程系');
 INSERT INTO TEACHER(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART)
 VALUES (825,'王萍','女','1972-05-05','助教','计算机系');
 INSERT INTO TEACHER(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) 
 VALUES (831,'刘冰','女','1977-08-14','助教','电子工程系');

 INSERT INTO COURSE(CNO,CNAME,TNO)VALUES ('3-105' ,'计算机导论',825);
 INSERT INTO COURSE(CNO,CNAME,TNO)VALUES ('3-245' ,'操作系统' ,804);
 INSERT INTO COURSE(CNO,CNAME,TNO)VALUES ('6-166' ,'数据电路' ,856);
 INSERT INTO COURSE(CNO,CNAME,TNO)VALUES ('9-888' ,'高等数学' ,831);

 INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (103,'3-245',86);
 INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (105,'3-245',75);
 INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (109,'3-245',68);
 INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (103,'3-105',92);
 INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (105,'3-105',88);
 INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (109,'3-105',76);
 INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (101,'3-105',64);
 INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (107,'3-105',91);
 INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (108,'3-105',78);
 INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (101,'6-166',85);
 INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (107,'6-166',79);
 INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (108,'6-166',81);