create table products_tbl
(
prod_id varchar(10) not null primary key,
prod_desc varchar(10) not null,
cost decimal(6,2) not null
);
insert into products_tbl values (‘11235′,’WITCHES COSTUME’,'29.99′) ;
insert into products_tbl values (‘222′,’PLASTIC PUMPKIN 18 INCH’,'7.75′) ;
insert into products_tbl values (‘13′,’FALSE PARAFFIN TEETH’,'1.10′) ;
insert into products_tbl values (‘90′,’LIGHTED LANTERNS’,'14.50′) ;
insert into products_tbl values (‘15′,’ASSORTED COSTUMES’,'10.00′) ;
insert into products_tbl values (‘9′,’CANDY CORN’,'1.35′) ;
insert into products_tbl values (‘6′,’PUMPKIN CANDY’,'1.45′) ;
insert into products_tbl values (‘87′,’PLASTIC SPIDERS’,'1.05′) ;
insert into products_tbl values (‘119′,’ASSORTED MASKS’,'4.95′) ;
做了简单的子查询,本来是查询价格超过10元的,结果全查出来了(不要问我为什么后面的子句也能用,但是还要用exist子查询,这是联系书上的例子!)select cost from products_tbl where exists(select cost from products_tbl where cost>10);
+-------+
| cost |
+-------+
| 29.99 |
| 7.75 |
| 1.10 |
| 14.50 |
| 10.00 |
| 1.35 |
| 1.45 |
| 1.05 |
| 4.95 |
+-------+
9 rows in set (0.00 sec)
(
prod_id varchar(10) not null primary key,
prod_desc varchar(10) not null,
cost decimal(6,2) not null
);
insert into products_tbl values (‘11235′,’WITCHES COSTUME’,'29.99′) ;
insert into products_tbl values (‘222′,’PLASTIC PUMPKIN 18 INCH’,'7.75′) ;
insert into products_tbl values (‘13′,’FALSE PARAFFIN TEETH’,'1.10′) ;
insert into products_tbl values (‘90′,’LIGHTED LANTERNS’,'14.50′) ;
insert into products_tbl values (‘15′,’ASSORTED COSTUMES’,'10.00′) ;
insert into products_tbl values (‘9′,’CANDY CORN’,'1.35′) ;
insert into products_tbl values (‘6′,’PUMPKIN CANDY’,'1.45′) ;
insert into products_tbl values (‘87′,’PLASTIC SPIDERS’,'1.05′) ;
insert into products_tbl values (‘119′,’ASSORTED MASKS’,'4.95′) ;
做了简单的子查询,本来是查询价格超过10元的,结果全查出来了(不要问我为什么后面的子句也能用,但是还要用exist子查询,这是联系书上的例子!)select cost from products_tbl where exists(select cost from products_tbl where cost>10);
+-------+
| cost |
+-------+
| 29.99 |
| 7.75 |
| 1.10 |
| 14.50 |
| 10.00 |
| 1.35 |
| 1.45 |
| 1.05 |
| 4.95 |
+-------+
9 rows in set (0.00 sec)
cost>10的记录有两条,条件始终满足,故结果为全部记录
所以exists(select cost from products_tbl where cost>10)肯定恒为TRUE
因此 select cost from products_tbl where exists(select cost from products_tbl where cost>10); 对所有记录都符合这个条件。
SELECT cost FROM products_tbl A WHERE prod_id in(select prod_id from products_tbl WHERE cost>10)