Women in Technology

Hear us Roar



Article:
  The Outer Limits of SQL JOINs
Subject:   full outer join
Date:   2007-08-27 17:36:03
From:   landonmkelsey@hotmail.com
why doesn't the following work?


mysql> select * from girls full outer join boys where girls.city = boys.city;


where :


drop table girls;
drop table boys;
create table girls (name varchar(12), city varchar(12));
create table boys (name varchar(12), city varchar(12));
insert into girls values('Mary', 'Boston');
insert into girls values('Nancy', null);
insert into girls values('Susan', 'Chicago');
insert into girls values('Betty', 'Chicago');
insert into girls values('Anne', 'Denver');
insert into boys values('John', 'Boston');
insert into boys values('Henry', 'Boston');
insert into boys values('George', null);
insert into boys values('Sam', 'Chicago');
insert into boys values('James', 'Dallas');


If needed, I can go into insert and create for you!


BTW left and right work OK!


BTW what do P(T1,T2), P1(T1,T2) and R( mean in the following


SELECT * T1 LEFT JOIN T2 ON P1(T1,T2)
WHERE P(T1,T2) AND R(T2)


my academic theory book doesn't cover this!