SQL基础学习4-子查询

SQL基础学习4-子查询

子查询 Sub Queries

子查询是嵌套查询,即一个SELECT句中包含另一个或多个SELECT句.

SELECT * FROM t1 WHERE column1 = (SELECT column1 FROM t2);

子查询分文关联子联查询和非关联子查询.

  • 关联子查询

    子查询需要执行多次,即采用循环的方式,先从外部查询开始,每次都传入子查询,然后将结果反馈给外部.

    SELECT * FROM t1 AS t
      WHERE 2 = (SELECT COUNT( * ) FROM t1 WHERE t1.id = t.id);
    

  • 非关联子查询

    子查询从数据表中查询了数据结果,这个结果只执行了一次,并作为主查询的条件进行执行.

    SELECT * FROM t1 WHERE column1 = (SELECT MAX(column1) FROM t1);
    

EXISTS,NOT EXISTS子查询

EXISTS子查询用来判断条件是否满足,满足:True,不满足:False.
最少在一个城市存在的店铺是:

SELECT DISTINCT store_type FROM stores
  WHERE EXISTS (SELECT * FROM cities_stores
                WHERE cities_stores.store_type = stores.store_type);

NOT EXISTS就是不存在.
那个城市都没有的店铺是:

SELECT DISTINCT store_type FROM stores
  WHERE NOT EXISTS (SELECT * FROM cities_stores
                    WHERE cities_stores.store_type = stores.store_type);

https://dev.mysql.com/doc/refman/5.6/ja/exists-and-not-exists-subqueries.html

集合比较子查询

  • IN:判断是否在集合中.
  • ANY:需与比较操作符一起使用,与子查询返回的任何值做比较.
    SELECT s1 FROM t1 WHERE s1 = ANY (SELECT s1 FROM t2);
    
  • ALL:需与比较操作符一起使用,与子查询返回的任何值做比较.
    SELECT s1 FROM t1 WHERE s1 > ALL (SELECT s1 FROM t2);
    

    https://dev.mysql.com/doc/refman/5.6/ja/all-subqueries.html

  • SOME:是ANY的别名

IN和EXISTS的用法
下面的两条语句,在cc列有索引的情况下,如果A表大于B表,用IN.反之用EXISTS.
我的理解为:因为IN和EXISTS的实现都是用的循环,CPU有个动态分支预测功能,在循环时,会预测下一条指令的取指地址,大小表的位置不一样,所预测的错误次数也是不一样的.大表在内,小表在外时时,预测的错误数小.所以小表驱动大表时的效率会更高.

SELECT * FROM A WHERE cc IN (SELECT cc FROM B);
SELECT * FROM A WHERE EXIST (SELECT cc FROM B WHERE B.cc=A.cc);

将子查询作为计算字段

mysql > SELECT team_name, (SELECT count( * ) FROM player WHERE player.team_id = team.team_id) AS player_num FROM team;

https://dev.mysql.com/doc/refman/5.6/ja/any-in-some-subqueries.html

发表评论

邮箱地址不会被公开。 必填项已用*标注