INで書いたサブクエリーをLEFT JOINにて書き換える


CREATE TABLE `client` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`Name` char(35) NOT NULL DEFAULT '',
PRIMARY KEY (`ID`)
) ENGINE=MyISAM AUTO_INCREMENT=4080 DEFAULT CHARSET=UTF8;


CREATE TABLE `project` (
`PID` int(11) NOT NULL,
`ID` int(11) NOT NULL AUTO_INCREMENT,
`NAME` char(35) NOT NULL DEFAULT '',
`START` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`END` timestamp NULL,
PRIMARY KEY (`ID`)
) ENGINE=MyISAM AUTO_INCREMENT=4080 DEFAULT CHARSET=utf8;

sub_a


mysql> insert into client(Name) values('company-a');
Query OK, 1 row affected (0.00 sec)
mysql> insert into client(Name) values('company-b');
Query OK, 1 row affected (0.00 sec)
mysql> insert into client(Name) values('company-c');
Query OK, 1 row affected (0.00 sec)
mysql> insert into project(pid,name,end) values(10000,'Project1','2009-12-15');
Query OK, 1 row affected (0.00 sec)
mysql> insert into project(pid,name,end) values(10001,'Project2','2009-12-15');
Query OK, 1 row affected (0.00 sec)
mysql> insert into project(pid,name,end) values(10002,'Project3','2009-12-16');
Query OK, 1 row affected (0.00 sec)
mysql> insert into project(pid,name,end) values(10003,'Project4','2009-12-19');
Query OK, 1 row affected (0.00 sec)
mysql> insert into project(pid,name,end) values(10004,'Project5','2009-12-30');
Query OK, 1 row affected (0.00 sec)

table_select

    Clientがいるプロジェクトのみ抽出


mysql> select * from project where project.ID IN
-> (select client.ID from client);

client

    上記QueryをLEFT JOINで書き直してみる


select B.PID,B.ID,B.NAME,B.START,B.END from client A
LEFT JOIN project B using(ID) /* or: ON A.ID = B.ID */
WHERE B.NAME IS NOT NULL;


select B.PID,B.ID,B.NAME,B.START,B.END from client A
LEFT JOIN project B ON A.ID = B.ID /* or: using(ID) */
WHERE B.NAME IS NOT NULL;

left_join

Comments are closed.

Post Navigation