create table tablename(id varchar(10),price int)
insert into tablename select 'a',1
insert into tablename select 'a',2
1insert into tablename select 'a',3
1insert into tablename select 'b',4go
select *
from tablename
where exists (select 1
from tablename where id='a')
--
查询记录是:a 1
a 2
a 3
b 4这样为什么不能得出指定ID的记录,而得出的是全部呢,select 1
from tablename where id='a')这句使用错误了吗
from tablename where id='a'当前这个条件为true 啊.所以是全部记录.
--这样.
select *
from tablename where id='a'
select *
from tablename where id='a'
就可以哦!
from tablename
where id in (select 1
from tablename where id='a')
where exists(select 1 from tablename where id='a')的意思是:只要tablename中有id='a'的记录 则选出tablename中的所有记录
select *
from tablename
where exists (select 1
from tablename where id='a')
--等同于
go
select *
from tablename
where exists (select 1
from tablename where 1=1)
select *
from tablename a
where exists (select 1
from tablename where id=a.id and id='a')
from tablename
where exists (select 1
from tablename where id='a')与
select *
from tablename
where exists (select *
from tablename where id='a')性能一样的,因为执行过程它并没有返回Exists()里select的部分,只是判断是否为true 或 false
from tablename
where exists (select 1
from dual where id='a');其实和 select *
from tablename
where id='a' 一样
from tablename a
where exists (select 1
from tablename where id =a.id and id='a')--结果id price
--------------
a 1
a 2
a 3
true 执行,false不执行
2.select 1 from tablename where id='a'
返回有值所以执行,显示所有记录.
3.条件
create table tablename(id varchar(10),price int)
insert into tablename select 'a',1
insert into tablename select 'a',2
insert into tablename select 'a',3
insert into tablename select 'b',4select 1
from tablename where id='a'select *
from tablename a
where exists (select 1
from tablename where id='a' and id = a.id)