标题: 关于left join right join where 等等的问题，难道以前的理解是错误的？ [打印本页] 作者: zyrf2001 时间: 2007-11-03 21:06 标题: 关于left join right join where 等等的问题，难道以前的理解是错误的？ 版本是4.1.7-nt 下面是准备数据
CREATE TABLE data_dictionary
(
dict_id decimal(6) PRIMARY KEY not null,
dict_name char(32) not null,
parent_id decimal(6)
);
CREATE TABLE kindergarten
(
k_id int PRIMARY KEY not null,
county_id int not null,
k_name char(64) not null,
k_abbr char(16) not null,
is_active tinyint not null
);
CREATE TABLE report
(
report_id int PRIMARY KEY not null,
k_id int not null,
year smallint not null,
can_update tinyint not null
);
ALTER TABLE report
ADD CONSTRAINT FK_Relationship_19
FOREIGN KEY (k_id)
REFERENCES kindergarten(k_id) ON DELETE NO ACTION ON UPDATE NO ACTION;
CREATE INDEX Relationship_19_FK ON report(k_id);
CREATE TABLE device_stat_info
(
device_stat_id int PRIMARY KEY not null,
report_id int not null,
dict_id decimal(6) not null,
value1 smallint,
value2 decimal(10,4)
);
ALTER TABLE device_stat_info
ADD CONSTRAINT FK_Relationship_11
FOREIGN KEY (dict_id)
REFERENCES data_dictionary(dict_id) ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE device_stat_info
ADD CONSTRAINT FK_Relationship_10
FOREIGN KEY (report_id)
REFERENCES report(report_id) ON DELETE NO ACTION ON UPDATE NO ACTION;
CREATE INDEX Relationship_11_FK ON device_stat_info(dict_id);
CREATE INDEX Relationship_10_FK ON device_stat_info(report_id);
INSERT INTO data_dictionary (dict_id,dict_name,parent_id) VALUES (130001,'活动室',130);
INSERT INTO data_dictionary (dict_id,dict_name,parent_id) VALUES (130002,'寝室',130);
INSERT INTO data_dictionary (dict_id,dict_name,parent_id) VALUES (130003,'儿童厕所',130);
INSERT INTO data_dictionary (dict_id,dict_name,parent_id) VALUES (130004,'盥洗室',130);
INSERT INTO kindergarten (k_id,county_id,k_name,k_abbr,is_active) VALUES (1,6,'北京市海淀区人民政府机关幼儿园','yey',1);
INSERT INTO kindergarten (k_id,county_id,k_name,k_abbr,is_active) VALUES (2,5,'中国气象局幼儿园','yey',1);
INSERT INTO kindergarten (k_id,county_id,k_name,k_abbr,is_active) VALUES (3,6,'中国人民解放军后勤指挥学院幼儿园','yey',1);
INSERT INTO kindergarten (k_id,county_id,k_name,k_abbr,is_active) VALUES (4,6,'海淀区西北旺镇西玉河村幼儿园','yey',1);
INSERT INTO report (report_id,k_id,year,can_update) VALUES (1,1,2007,1);
INSERT INTO report (report_id,k_id,year,can_update) VALUES (2,2,2007,1);
INSERT INTO device_stat_info (device_stat_id,report_id,dict_id,value1,value2) VALUES (1,1,130001,1,null);
INSERT INTO device_stat_info (device_stat_id,report_id,dict_id,value1,value2) VALUES (2,2,130001,2,null);
第一种查询
SELECT
kindergarten.k_name,
device_stat.dict_name,
device_stat_info.value1,
device_stat_info.value2
FROM
kindergarten LEFT OUTER JOIN report ON (report.k_id=kindergarten.k_id)
LEFT OUTER JOIN device_stat_info ON (device_stat_info.report_id=report.report_id)
RIGHT OUTER JOIN data_dictionary device_stat ON (device_stat.dict_id=device_stat_info.dict_id)
WHERE
( device_stat.parent_id=130 )
AND (report.year = 2007)
第二种查询
SELECT
kindergarten.k_name,
device_stat.dict_name,
device_stat_info.value1,
device_stat_info.value2
FROM
kindergarten ,report ,device_stat_info
RIGHT OUTER JOIN data_dictionary device_stat ON (device_stat.dict_id=device_stat_info.dict_id)
WHERE (report.k_id=kindergarten.k_id) and
(device_stat_info.report_id=report.report_id) and
( device_stat.parent_id=130 )
AND (report.year = 2007)
第三种查询
SELECT
kindergarten.k_name,
device_stat.dict_name,
device_stat_info.value1,
device_stat_info.value2
FROM
kindergarten inner JOIN report ON (report.k_id=kindergarten.k_id)
inner JOIN device_stat_info ON (device_stat_info.report_id=report.report_id)
RIGHT OUTER JOIN data_dictionary device_stat ON (device_stat.dict_id=device_stat_info.dict_id)
WHERE
( device_stat.parent_id=130 )
AND (report.year = 2007)