sql多表查询和In EXISIT
来源:互联网 发布:js中数组的indexof 编辑:程序博客网 时间:2024/06/16 22:51
本文主要列举两张和三张表来讲述多表连接查询。
新建两张表:
表1:student 截图如下:
表2:course 截图如下:
(此时这样建表只是为了演示连接SQL语句,当然实际开发中我们不会这样建表,实际开发中这两个表会有自己不同的主键。)
一、外连接
外连接可分为:左连接、右连接、完全外连接。
1、左连接 left join 或 left outer join
SQL语句:select * from student left join course on student.ID=course.ID
执行结果:
左外连接包含left join左表所有行,如果左表中某行在右表没有匹配,则结果中对应行右表的部分全部为空(NULL).
注:此时我们不能说结果的行数等于左表数据的行数。当然此处查询结果的行数等于左表数据的行数,因为左右两表此时为一对一关系。
2、右连接 right join 或 right outer join
SQL语句:select * from student right join course on student.ID=course.ID
执行结果:
右外连接包含right join右表所有行,如果左表中某行在右表没有匹配,则结果中对应左表的部分全部为空(NULL)。
注:同样此时我们不能说结果的行数等于右表的行数。当然此处查询结果的行数等于左表数据的行数,因为左右两表此时为一对一关系。
3、完全外连接 full join 或 full outer join
SQL语句:select * from student full join course on student.ID=course.ID
执行结果:
完全外连接包含full join左右两表中所有的行,如果右表中某行在左表中没有匹配,则结果中对应行右表的部分全部为空(NULL),如果左表中某行在右表中没有匹配,则结果中对应行左表的部分全部为空(NULL)。
二、内连接 join 或 inner join
SQL语句:select * from student inner join course on student.ID=course.ID
执行结果:
inner join 是比较运算符,只返回符合条件的行。
此时相当于:select * from student,course where student.ID=course.ID
三、交叉连接 cross join
1.概念:没有 WHERE 子句的交叉联接将产生连接所涉及的表的笛卡尔积。第一个表的行数乘以第二个表的行数等于笛卡尔积结果集的大小。
SQL语句:select * from student cross join course
执行结果:
如果我们在此时给这条SQL加上WHERE子句的时候比如SQL:select * from student cross join course where student.ID=course.ID
此时将返回符合条件的结果集,结果和inner join所示执行结果一样。
四、两表关系为一对多,多对一或多对多时的连接语句
当然上面两表为一对一关系,那么如果表A和表B为一对多、多对一或多对多的时候,我们又该如何写连接SQL语句呢?
其实两表一对多的SQL语句和一对一的SQL语句的写法都差不多,只是查询的结果不一样,当然两表也要略有改动。
比如表1的列可以改为:
Sno Name Cno
表2的列可以改为:
Cno CName
这样两表就可以写一对多和多对一的SQL语句了,写法和上面的一对一SQL语句一样。
下面介绍一下当两表为多对多的时候我们该如何建表以及些SQL语句。
新建三表:
表A: student 截图如下:
表B: course 截图如下:
表C: student_course 截图如下:
一个学生可以选择多门课程,一门课程可以被多个学生选择,因此学生表student和课程表course之间是多对多的关系。
当两表为多对多关系的时候,我们需要建立一个中间表student_course,中间表至少要有两表的主键,当然还可以有别的内容。
SQL语句:select s.Name,C.Cname from student_course as sc left join student as s on s.Sno=sc.Sno left join course as c on c.Cno=sc.Cno
执行结果:
此条SQL执行的结果是学生选课的情况。
1.exist,not exist一般都是与子查询一起使用. In可以与子查询一起使用,也可以直接in (a,b.....)
2.exist会针对子查询的表使用索引. not exist会对主子查询都会使用索引. in与子查询一起使用的时候,只能针对主查询使用索引. not in则不会使用任何索引. 注意,一直以来认为exists比in效率高的说法是不准确的。
in 是把外表和内表作hash 连接,而exists是对外表作loop循环,每次loop循环再对内表进行查询。
如果查询的两个表大小相当,那么用in和exists差别不大。
如果两个表中一个较小,一个是大表,则子查询表大的用exists,子查询表小的用in:
例如:表A(小表),表B(大表)1:select * from A where cc in (select cc from B)
效率低,用到了A表上cc列的索引;select * from A where exists(select cc from B where cc=A.cc)
效率高,用到了B表上cc列的索引。
相反的2:select * from B where cc in (select cc from A)
效率高,用到了B表上cc列的索引;select * from B where exists(select cc from A where cc=B.cc)
效率低,用到了A表上cc列的索引。
not in 和not exists如果查询语句使用了not in 那么内外表都进行全表扫描,没有用到索引;而not extsts 的子查询依然能用到表上的索引。所以无论那个表大,用not exists都比not in要快。
3.exist与in都可以实现一个目的.二者都可以用来过滤数据.
示例:
select count(1) from t1;--160W
select count(1) from t2; --90W
SELECT count(1)
FROM t1 a
WHERE EXISTS (SELECT accountid
FROM t2 b
WHERE a.keyid = b.keyid AND a.ideaid = b.ideaid);--主大子小,不适合使用exist,因为exist只会利用子表t2的复合索引keyid+ideaid,而子表内容要小与主表,主表由于无法使用索引,查询效率低下.
select count(1) from t1 a where accountid in (SELECT accountid
FROM t2 b
WHERE a.keyid = b.keyid AND a.ideaid = b.ideaid);--主大子小,适合用in,因为in只会使用主表t1里面的复合主键keyid-ideaid,在主表大于子表的情况下,会很好的利用主表的索引.
--后二条sql的执行结果都是一样的.说明exist与in在用法上可以达到一个目的,不同的地方是
--1.性能的考虑此时就按子表大主表小用exist,子表小主表大用in的原则就可以.
--2.写法的不同, exist的where条件是: "...... where exist (..... where a.id=b.id)"
--in的where条件是: " ...... where id in ( select id .... where a.id=b.id)"
4. exist的原理:
exists做为where 条件时,是先对where 前的主查询询进行查询,然后用主查询的结果一个一个的代入exists的查询进行判断,如果为真则输出当前这一条主查询的结果,否则不输出
比如
如下:
表A
ID NAME
1 A1
2 A2
3 A3
表B
ID AID NAME
1 1 B1
2 2 B2
3 2 B3
表A和表B是一对多的关系 A.ID --> B.AID
SELECT ID , NAME FROM A WHERE EXISTS (SELECT * FROM B WHERE A.ID = B.AID)
执行结果为
1 A1
2 A2
原因可以按照如下分析
SELECT ID , NAME FROM A WHERE EXISTS (SELECT * FROM B WHERE B.AID = 1)
-->SELECT * FROM B WHERE B.AID = 1有值返回真所以有数据
SELECT ID , NAME FROM A WHERE EXISTS (SELECT * FROM B WHERE B.AID = 2)
-->SELECT * FROM B WHERE B.AID = 2有值返回真所以有数据
SELECT ID , NAME FROM A WHERE EXISTS (SELECT * FROM B WHERE B.AID = 3)
-->SELECT * FROM B WHERE B.AID = 3无值返回真所以没有数据
NOT EXISTS 就是反过来
SELECT ID , NAME FROM A WHERE NOT EXIST (SELECT * FROM B WHERE A.ID = B.AID)
执行结果为
3 A3
5. in 与 =的区别
select name from student where name in ('zhang','wang','li','zhao');
与
select name from student where name='zhang' or name='li' or name='wang' or name='zhao'
的结果是相同的。
in的字段也可以与其它字段建复合索引.
比如
T1包含下面key, accountd,groupid.
SELECT *
FROM T1 a
WHERE a.groupid = 2001
AND a.accountid = 1001
AND a.key IN ('abc', 'def', 'ala');
--上面的sql可以将accountid,key建成复合索引.
- sql多表查询和In EXISIT
- sql多表查询之三:子查询IN
- T-SQL查询:慎用 IN 和 NOT IN
- T-SQL查询:慎用 IN 和 NOT IN
- Sql多条件查询,子查询,in及分页查询
- sql in 查询匹配多个字段
- mybatis sql in 查询
- mybatis sql in 查询
- mybatis sql in 查询
- mybatis sql in 查询
- mybatis sql in 查询
- mybatis sql in 查询
- sql查询where/in
- mybatis sql in 查询
- Mybatis SQL IN查询
- sql进阶-多表查询和复合查询
- SQL的多表查询和子查询
- sql: not in和联接(left join)查询的比较
- android AIDL技术的简单演示
- Fibonacci数列
- coreData中integer16,integer32,integer64的区别
- RabbitMQ的原理与操作示例
- 结构的使用
- sql多表查询和In EXISIT
- 双端链表实现hash(哈希)
- poj 2488A Knight's Journey(搜索 记录路径)
- java(7)--TCP传输
- HDU Courses 二分图最大匹配
- Spanny字符串样式处理使用心得。
- Maven Error injecting org/sonatype/aether/version/VersionConstrain
- 产品经理需要会写代码吗?
- rtmp协议消息体介绍