CREATE TABLE `test_a` (
`id` int(11) NOT NULL,
`matName` varchar(128) default NULL,
`matCode` varchar(64) default NULL,
`keyWords` varchar(128) default NULL,
PRIMARY KEY (`id`)
) CREATE TABLE `test_b` (
`id` int(11) NOT NULL,
`aId` int(11) default NULL,
`status` int(11) default NULL,
PRIMARY KEY (`id`)
) 不用连接实现下述sql语句查询到的结果!
select * from test_a
left join test_b on test_a.id = test_b.aId
where (status=3 or status =4 )select * from test_a
left join test_b on test_a.id = test_b.aId
where (status=5 or status is null)
INSERT INTO `test_a`( `id`, `matName`) VALUE (1,'a');
INSERT INTO `test_a`( `id`, `matName`) VALUE (2,'b');
INSERT INTO `test_a`( `id`, `matName`) VALUE (3,'c');
INSERT INTO `test_a`( `id`, `matName`) VALUE (4,'d');
INSERT INTO `test_a`( `id`, `matName`) VALUE (5,'e');INSERT INTO `test_b`( `id`, `aId`,`status`) VALUE (1,1,3);
INSERT INTO `test_b`( `id`, `aId`,`status`) VALUE (2,1,2);
INSERT INTO `test_b`( `id`, `aId`,`status`) VALUE (3,1,2);
INSERT INTO `test_b`( `id`, `aId`,`status`) VALUE (4,1,5);INSERT INTO `test_b`( `id`, `aId`,`status`) VALUE (5,2,4);
INSERT INTO `test_b`( `id`, `aId`,`status`) VALUE (6,2,5);
INSERT INTO `test_b`( `id`, `aId`,`status`) VALUE (7,2,2);
INSERT INTO `test_b`( `id`, `aId`,`status`) VALUE (8,2,5);INSERT INTO `test_b`( `id`, `aId`,`status`) VALUE (9,3,3);要求的结果:
不用连接实现下述sql语句查询到的结果!select * from test_a
left join test_b on test_a.id = test_b.aId
where (status=3 or status =4 )
select * from test_a
left join test_b on test_a.id = test_b.aId
where (status=5 or status is null)
-> from test_a
-> where exists (select 1 from test_b where aId=test_a.id and (status=3 or s
tatus =4));
+----+---------+---------+----------+
| id | matName | matCode | keyWords |
+----+---------+---------+----------+
| 1 | a | NULL | NULL |
| 2 | b | NULL | NULL |
| 3 | c | NULL | NULL |
+----+---------+---------+----------+
3 rows in set (0.00 sec)mysql>mysql> select *
-> from test_a
-> where exists (select 1 from test_b where aId=test_a.id and status=5)
-> or not exists (select 1 from test_b where aId=test_a.id);
+----+---------+---------+----------+
| id | matName | matCode | keyWords |
+----+---------+---------+----------+
| 1 | a | NULL | NULL |
| 2 | b | NULL | NULL |
| 4 | d | NULL | NULL |
| 5 | e | NULL | NULL |
+----+---------+---------+----------+
4 rows in set (0.00 sec)mysql>