MySQL 子查询谓词

 

 

MySQL 子查询分类

  可以查看item_subselect.h中的定义：

  enum subs_type {UNKNOWN_SUBS, SINGLEROW_SUBS,

 EXISTS_SUBS, IN_SUBS, ALL_SUBS, ANY_SUBS};

 

子查询类型详解：

  UNKNOWN_SUBS: 

 

  SINGLEROW_SUBS :

       a) SELECT * FROM t1 WHERE t1.a = (<single row subquery>) 

       b) SELECT a, (<single row subquery) FROM t1

   

  EXISTS_SUBS:

       EXISTS 子查询的条件返回true or false

       a) SELECT c.CustomerId, CompanyName FROM Customers c  WHERE EXISTS( SELECT OrderID FROM Orders o  WHERE o.CustomerID = cu.CustomerID)  

       b) SELECT c.CustomerId, CompanyName FROM Customers c  WHERE not EXISTS( SELECT OrderID FROM Orders o  WHERE o.CustomerID = cu.CustomerID)  

 

   IN_SUBS:

      a) SELECT * FROM article WHERE uid IN(SELECT uid FROM user WHERE status=0)

      b) SELECT * FROM article WHERE uid not IN(SELECT uid FROM user WHERE status=0)

 

   ALL_SUBS:

       s1 必须大于所有s2的元素

       a) SELECT s1 FROM table1 WHERE s1 > ALL(SELECT s2 FROM table2)

 

  ANY_SUBS:

       s1 大于任意s2的元素即可

       a) SELECT s1 FROM table1 WHERE s1 > ANY(SELECT s2 FROM table2)