mysql中from exists union where

#exists的效率一般比in高 小表用exists 大表用exists 如果两张表差不多大的时候效率基本上一样 但是not exists比not in 效率高SELECT * from category where cat_id in (SELECT cat_id FROM goods);SELECT * from category WHERE EXISTS (SELECT cat_id from goods where goods.cat_id=category.cat_id );#FROM子句中使用子查询一般都是返回多行多列，可以将其当作一张数据表 致于效率我也不太懂SELECT *,(SELECT cat_name from category where category.cat_id=goods.cat_id) from goods where goods_id=19;SELECT * from goods g LEFT JOIN category c on g.cat_id=c.cat_id where goods_id=19; #union查询 一般不让union进行合并 因为是耗时的 我们用union all 可以避免合并 #使用union 注意表的字段数量必须一样 字段的名字无所谓一样 只有等到两张表的数据完全相同是才进行合并#你想想为什么不用order by呢 因为子句写没有意义 到结果集写才有意义呢SELECT id,sum(num) from a UNION ALL SELECT * FROM b;# where查询内层sql的返回值在where后作为条件表达式的一部分 查询出最新一行商品(以商品编号最大为最新,用子查询实现)SELECT * from goods where goods_id=(SELECT MAX(goods_id) from goods);表结构create table goods (  goods_id mediumint(8) unsigned primary key auto_increment,  goods_name varchar(120) not null default '',  cat_id smallint(5) unsigned not null default '0',  brand_id smallint(5) unsigned not null default '0',  goods_sn char(15) not null default '',  goods_number smallint(5) unsigned not null default '0',  shop_price decimal(10,2) unsigned not null default '0.00',  market_price decimal(10,2) unsigned not null default '0.00',  click_count int(10) unsigned not null default '0') engine=myisam default charset=utf8;insert into `goods` values (1,'kd876',4,8,'ecs000000',1,1388.00,1665.60,9),(4,'诺基亚n85原装充电器',8,1,'ecs000004',17,58.00,69.60,0),(3,'诺基亚原装5800耳机',8,1,'ecs000002',24,68.00,81.60,3),(5,'索爱原装m2卡读卡器',11,7,'ecs000005',8,20.00,24.00,3),(6,'胜创kingmax内存卡',11,0,'ecs000006',15,42.00,50.40,0),(7,'诺基亚n85原装立体声耳机hs-82',8,1,'ecs000007',20,100.00,120.00,0),(8,'飞利浦9@9v',3,4,'ecs000008',1,399.00,478.79,10),(9,'诺基亚e66',3,1,'ecs000009',4,2298.00,2757.60,20),(10,'索爱c702c',3,7,'ecs000010',7,1328.00,1593.60,11),(11,'索爱c702c',3,7,'ecs000011',1,1300.00,0.00,0),(12,'摩托罗拉a810',3,2,'ecs000012',8,983.00,1179.60,13),(13,'诺基亚5320 xpressmusic',3,1,'ecs000013',8,1311.00,1573.20,13),(14,'诺基亚5800xm',4,1,'ecs000014',1,2625.00,3150.00,6),(15,'摩托罗拉a810',3,2,'ecs000015',3,788.00,945.60,8),(16,'恒基伟业g101',2,11,'ecs000016',0,823.33,988.00,3),(17,'夏新n7',3,5,'ecs000017',1,2300.00,2760.00,2),(18,'夏新t5',4,5,'ecs000018',1,2878.00,3453.60,0),(19,'三星sgh-f258',3,6,'ecs000019',12,858.00,1029.60,7),(20,'三星bc01',3,6,'ecs000020',12,280.00,336.00,14),(21,'金立 a30',3,10,'ecs000021',40,2000.00,2400.00,4),(22,'多普达touch hd',3,3,'ecs000022',1,5999.00,7198.80,16),(23,'诺基亚n96',5,1,'ecs000023',8,3700.00,4440.00,17),(24,'p806',3,9,'ecs000024',100,2000.00,2400.00,35),(25,'小灵通/固话50元充值卡',13,0,'ecs000025',2,48.00,57.59,0),(26,'小灵通/固话20元充值卡',13,0,'ecs000026',2,19.00,22.80,0),(27,'联通100元充值卡',15,0,'ecs000027',2,95.00,100.00,0),(28,'联通50元充值卡',15,0,'ecs000028',0,45.00,50.00,0),(29,'移动100元充值卡',14,0,'ecs000029',0,90.00,0.00,0),(30,'移动20元充值卡',14,0,'ecs000030',9,18.00,21.00,1),(31,'摩托罗拉e8 ',3,2,'ecs000031',1,1337.00,1604.39,5),(32,'诺基亚n85',3,1,'ecs000032',4,3010.00,3612.00,9); create table category ( cat_id smallint unsigned auto_increment primary key, cat_name varchar(90) not null default '', parent_id smallint unsigned )engine myisam charset utf8;INSERT INTO `category` VALUES (1,'手机类型',0),(2,'CDMA手机',1),(3,'GSM手机',1),(4,'3G手机',1),(5,'双模手机',1),(6,'手机配件',0),(7,'充电器',6),(8,'耳机',6),(9,'电池',6),(11,'读卡器和内存卡',6),(12,'充值卡',0),(13,'小灵通/固话充值卡',12),(14,'移动手机充值卡',12),(15,'联通手机充值卡',12);create table a (id char(1),num int)engine myisam charset utf8;insert into a values ('a',5),('b',10),('c',15),('d',10);create table b (id char(1),num int)engine myisam charset utf8;insert into b values ('b',5),('c',15),('d',20),('e',99);