mysql要按VS嘛_在vs上使用MySQL？

匿名用户

当我发现on比using更有用时，我想我会在这里加入。在查询中引入outer联接时就是这样。

上的好处是允许限制查询外部联接的表的结果集，同时维护外部联接。试图通过指定where子句来限制结果集将有效地将outer联接更改为inner联接。

诚然，这可能是一个相对的角落案例。不过，还是值得一试的。。。。

例如:CREATE TABLE country (

countryId int(10) unsigned NOT NULL PRIMARY KEY AUTO_INCREMENT,

country varchar(50) not null,

UNIQUE KEY countryUIdx1 (country)

) ENGINE=InnoDB;

insert into country(country) values ("France");

insert into country(country) values ("China");

insert into country(country) values ("USA");

insert into country(country) values ("Italy");

insert into country(country) values ("UK");

insert into country(country) values ("Monaco");

CREATE TABLE city (

cityId int(10) unsigned NOT NULL PRIMARY KEY AUTO_INCREMENT,

countryId int(10) unsigned not null,

city varchar(50) not null,

hasAirport boolean not null default true,

UNIQUE KEY cityUIdx1 (countryId,city),

CONSTRAINT city_country_fk1 FOREIGN KEY (countryId) REFERENCES country (countryId)

) ENGINE=InnoDB;

insert into city (countryId,city,hasAirport) values (1,"Paris",true);

insert into city (countryId,city,hasAirport) values (2,"Bejing",true);

insert into city (countryId,city,hasAirport) values (3,"New York",true);

insert into city (countryId,city,hasAirport) values (4,"Napoli",true);

insert into city (countryId,city,hasAirport) values (5,"Manchester",true);

insert into city (countryId,city,hasAirport) values (5,"Birmingham",false);

insert into city (countryId,city,hasAirport) values (3,"Cincinatti",false);

insert into city (countryId,city,hasAirport) values (6,"Monaco",false);

-- Gah. Left outer join is now effectively an inner join

-- because of the where predicate

select *

from country left join city using (countryId)

where hasAirport

;

-- Hooray! I can see Monaco again thanks to

-- moving my predicate into the ON

select *

from country co left join city ci on (co.countryId=ci.countryId and ci.hasAirport)

;