select * from table1 t1,table2 t2
where t1.field1=t2.field1(+)
and t2.field1 is null
and t2.field3 ='12345'
and t1.field2 like 'a%'上边写错了一处,不好意思。
where t1.field1=t2.field1(+)
and t2.field1 is null
and t2.field3 ='12345'
and t1.field2 like 'a%'上边写错了一处,不好意思。
*
from
table1 t1
where
t1.field1 not in (select field1 from table2 t2 where t2.field3='12345')
and
t1.fields2 like 'a%'也不等价于:select
*
from
table1 t1
where
t1.field1 in (select field1 from table2 t2 where t2.field3 <> '12345')
and
t1.fields2 like 'a%'
*
from
table1 t1
where
t1.field1 not in (select field1 from table2 t2 where t2.field3='12345')
and
t1.fields2 like 'a%'等价于select
*
from
table1 t1
where
not exists(select 1 from table2 where field3='12345' and field1=t1.field1)
and
t1.fields2 like 'a%'
t1.field1 not in (select field1 from table2 t2 where t2.field3='12345')
and t1.fields2 like 'a%'等价于select * from table1 t1 where
t1.field1 not exists (select field1 from table2 t2 where t2.field3='12345')
and t1.fields2 like 'a%'
(强烈推荐用not exists比not in 要快,这是相关资料写的)
in 和=在查询数据时是有区别的。IN是在一个范围内,=是指定基本一个值。
null值是永遠不會想等的,所以用t1.field1=t2.field1是查不出資料的