1.使用关键字"IN"
SELECT *
FROM bms_dsl_PortConsumerBind a
WHERE (ConsumerID IN
(SELECT ConsumerID
FROM bms_dsl_ConsumerInfo b
WHERE (b.Name = '空') OR
(b.Name = '坏')))
2.使用关键字"ANY"
SELECT *
FROM bms_dsl_PortConsumerBind a
WHERE (ConsumerID = ANY
(SELECT ConsumerID
FROM bms_dsl_ConsumerInfo b
WHERE (b.Name = '空') OR
(b.Name = '坏')))
使用关键字"in"和"ANY",区别在于ANY前面需要加"=",子查询语句是完全一致
3.使用关键字"EXISTS" 使用"EXISTS"关键在于两表关联语句,如:下列例子 a.ConsumerID = b.ConsumerID
SELECT *
FROM bms_dsl_PortConsumerBind a
WHERE EXISTS
(SELECT ConsumerID
FROM bms_dsl_ConsumerInfo b
WHERE a.ConsumerID = b.ConsumerID AND ((b.Name = '空') OR
(b.Name = '坏')))
使用关键字"in"和"ANY" 与使用"EXISTS"的区别,结构上的差别:EXISTS前面是不需要有列名的,并且在子查询
语句的条件中的必须增加两表关联语句
4.不使用任何关键字(经过测试ODBC中是不可用的),所以可以使用添加关键字"ANY"取代
SELECT *
FROM bms_dsl_PortConsumerBind a
WHERE (ConsumerID =
(SELECT ConsumerID
FROM bms_dsl_ConsumerInfo b
WHERE (b.Name = '空') OR
(b.Name = '坏')))
SELECT *
FROM bms_dsl_PortConsumerBind a
WHERE (ConsumerID IN
(SELECT ConsumerID
FROM bms_dsl_ConsumerInfo b
WHERE (b.Name = '空') OR
(b.Name = '坏')))
2.使用关键字"ANY"
SELECT *
FROM bms_dsl_PortConsumerBind a
WHERE (ConsumerID = ANY
(SELECT ConsumerID
FROM bms_dsl_ConsumerInfo b
WHERE (b.Name = '空') OR
(b.Name = '坏')))
使用关键字"in"和"ANY",区别在于ANY前面需要加"=",子查询语句是完全一致
3.使用关键字"EXISTS" 使用"EXISTS"关键在于两表关联语句,如:下列例子 a.ConsumerID = b.ConsumerID
SELECT *
FROM bms_dsl_PortConsumerBind a
WHERE EXISTS
(SELECT ConsumerID
FROM bms_dsl_ConsumerInfo b
WHERE a.ConsumerID = b.ConsumerID AND ((b.Name = '空') OR
(b.Name = '坏')))
使用关键字"in"和"ANY" 与使用"EXISTS"的区别,结构上的差别:EXISTS前面是不需要有列名的,并且在子查询
语句的条件中的必须增加两表关联语句
4.不使用任何关键字(经过测试ODBC中是不可用的),所以可以使用添加关键字"ANY"取代
SELECT *
FROM bms_dsl_PortConsumerBind a
WHERE (ConsumerID =
(SELECT ConsumerID
FROM bms_dsl_ConsumerInfo b
WHERE (b.Name = '空') OR
(b.Name = '坏')))