SQL多表查询之 where和INNER JOIN【基础查询和效率分析】
转载Michaelwubo最后发布于-07-05 10:40:47阅读数 18057收藏/u013372487/article/details/52622491?locationNum=1
/qingtanlang/article/details/2133816
/drake-guo/p/6101531.html
/huyr_123/article/details/61616547
在多表查询中，一些SQL开发人员更喜欢使用WHERE来做join，比如：
SELECT a.ID, b.Name, b.Date FROM Customers a, Sales b WHERE a.ID = b.ID;
缺点：在上面语句中，实际上是创建了两张表的笛卡尔积，所有可能的组合都会被创建出来。在笛卡尔连接中，在上面的例子中，如果有1000顾客和1000条销售记录，这个查询会先产生1000000个结果，然后通过正确的 ID过滤出1000条记录。 这是一种低效利用数据库资源，数据库多做100倍的工作。 在大型数据库中，笛卡尔连接是一个大问题，对两个大表的笛卡尔积会创建数10亿或万亿的记录。
为了避免创建笛卡尔积，应该使用INNER JOIN ：
SELECT a.ID, b.Name, b.Date FROM Customers a INNER JOIN Sales b ON a.ID = b.ID;
优点：如上面语句，使用inner join 这样数据库就只产生等于ID 的1000条目标结果。增加了查询效率。
有些数据库系统会识别出 WHERE连接并自动转换为 INNER JOIN。在这些数据库系统中，WHERE 连接与INNER JOIN 就没有性能差异。但是， INNER JOIN 是所有数据库都能识别的，因此DBA会建议在你的环境中使用它。
INNERJOIN(内连接,或等值连接)：获取两个表中字段匹配关系的记录。 LEFTJOIN(左连接)：获取左表所有记录，即使右表没有对应匹配的记录。 RIGHTJOIN(右连接)： 与LEFTJOIN 相反，用于获取右表所有记录，即使左表没有对应匹配的记录。
注意：
1、INNER JOIN 等同于 JOIN；
2、示意图：
我在练习MySQL操作语句时，使用一条完全没有错误的语句：
update students set name='drake' where name='chuan';
却报了如下错误：
Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column To disable safe mode, toggle the option in Preferences -> SQL Queries and reconnect.
简单翻译一下：你正在使用 安全更新模式(safe upate mode)并且你在尝试 update 一个表时 没有用带有键的列 作为where条件,在首选项中切换选项。
初学者在修改一个值时可能会用以下语句：
update t set col='new_value'
而这正是很多人常犯的错误。因为他没有限定条件，会将表中所有的记录都修改一遍。
为了防止这种错误出现，我们可以开启安全更新模式(safe update mode)：
set [global] SQL_SAFE_UPDATES = 1;
在update操作中：当where条件中列(column)没有索引可用且无limit限制时会拒绝更新。where条件为常量且无limit限制时会拒绝更新。
在delete操作中：当①where条件为常量，②或where条件为空，③或where条件中 列(column)没有索引可用且无limit限制时拒绝删除。
需要注意的是：
update操作中，where可以为常量 ，where条件中列(column)可以没有索引。但是需要有limit限制。
然而delete要严格一些：where不能为常量，且where条件中列(column)不能没有索引！
1 .WHERE子句中使用的连接语句，在数据库语言中，被称为隐性连接。INNER JOIN……ON子句产生的连接称为显性连接。(其他JOIN参数也是显性连接)WHERE 和INNER JOIN产生的连接关系，没有本质区别，结果也一样。但是！隐性连接随着数据库语言的规范和发展，已经逐渐被淘汰，比较新的数据库语言基本上已经抛弃了隐性连接，全部采用显性连接了。
2 .无论怎么连接，都可以用join子句，但是连接同一个表的时候，注意要定义别名，否则产生错误！
a> left join：理解为“有效连接”，两张表中都有的数据才会显示left join：理解为“有左显示”，比如on a.field=b.field，则显示a表中存在的全部数据及a//b中都有的数据，A中有、B没有的数据以null显示
b> right join：理解为“有右显示”，比如on a.field=b.field，则显示B表中存在的全部数据及a//b中都有的数据，B中有、A没有的数据以null显示
c> full join：理解为“全连接”，两张表中所有数据都显示，实际就是inner +(left-inner)+(right-inner)
3 .join可以分主次表 外联接有三种类型：完全外联，左联，右联．
完全外联包含两张表的所有记录．
左联是以左边的表为主，右边的为辅，右联则相反
语法格式：
其实 INNER JOIN ……ON的语法格式可以概括为：
FROM (((表1 INNER JOIN 表2 ON 表1.字段号=表2.字段号) INNER JOIN 表3 ON 表1.字段号=表3.字段号) INNER JOIN 表4 ON Member.字段号=表4.字段号) INNER JOIN 表X ON Member.字段号=表X.字段号
您只要套用该格式就可以了。
现成格式范例：
虽然我说得已经比较明白了，但为照顾初学者，我还是以本会员注册系统为例，提供一些现成的语法格式范例，大家只要修改其中的数据表名称和字段名称即可。
连接两个数据表的用法：
FROM Member INNER JOIN MemberSort ON Member.MemberSort=MemberSort.MemberSort
语法格式可以概括为：
FROM 表1 INNER JOIN 表2 ON 表1.字段号=表2.字段号
连接三个数据表的用法：
FROM (Member INNER JOIN MemberSort ON Member.MemberSort=MemberSort.MemberSort) INNER JOIN MemberLevel ON Member.MemberLevel=MemberLevel.MemberLevel
语法格式可以概括为：
FROM (表1 INNER JOIN 表2 ON 表1.字段号=表2.字段号) INNER JOIN 表3 ON 表1.字段号=表3.字段号
连接四个数据表的用法：
FROM ((Member INNER JOIN MemberSort ON Member.MemberSort=MemberSort.MemberSort) INNER JOIN MemberLevel ON Member.MemberLevel=MemberLevel.MemberLevel) INNER JOIN MemberIdentity ON Member.MemberIdentity=MemberIdentity.MemberIdentity
语法格式可以概括为：
FROM ((表1 INNER JOIN 表2 ON 表1.字段号=表2.字段号) INNER JOIN 表3 ON 表1.字段号=表3.字段号) INNER JOIN 表4 ON Member.字段号=表4.字段号
连接五个数据表的用法：
FROM (((Member INNER JOIN MemberSort ON Member.MemberSort=MemberSort.MemberSort) INNER JOIN MemberLevel ON Member.MemberLevel=MemberLevel.MemberLevel) INNER JOIN MemberIdentity ON Member.MemberIdentity=MemberIdentity.MemberIdentity) INNER JOIN Wedlock ON Member.Wedlock=Wedlock.Wedlock
语法格式可以概括为：
FROM (((表1 INNER JOIN 表2 ON 表1.字段号=表2.字段号) INNER JOIN 表3 ON 表1.字段号=表3.字段号) INNER JOIN 表4 ON Member.字段号=表4.字段号) INNER JOIN 表5 ON Member.字段号=表5.字段号
sql(join on 和where的执行顺序)
left join :左连接，返回左表中所有的记录以及右表中连接字段相等的记录。
right join :右连接，返回右表中所有的记录以及左表中连接字段相等的记录。
inner join: 内连接，又叫等值连接，只返回两个表中连接字段相等的行。
full join:外连接，返回两个表中的行：left join + right join。
cross join:结果是笛卡尔积，就是第一个表的行数乘以第二个表的行数。
关键字:on
数据库在通过连接两张或多张表来返回记录时，都会生成一张中间的临时表，然后再将这张临时表返回给用户。
在使用leftjion时，on和where条件的区别如下：
1、on条件是在生成临时表时使用的条件，它不管on中的条件是否为真，都会返回左边表中的记录。
2、where条件是在临时表生成好后，再对临时表进行过滤的条件。这时已经没有leftjoin的含义(必须返回左边表的记录)了，条件不为真的就全部过滤掉。
假设有两张表：
表1：tab2
表2：tab2
两条SQL:
1、select*formtab1leftjointab2on(tab1.size=tab2.size)wheretab2.name=’AAA’
2、select*formtab1leftjointab2on(tab1.size=tab2.sizeandtab2.name=’AAA’)
where条件：
tab2.name=’AAA’
其实以上结果的关键原因就是leftjoin,rightjoin,fulljoin的特殊性，不管on上的条件是否为真都会返回left或right表中的记录，full则具有left和right的特性的并集。而innerjion没这个特殊性，则条件放在on中和where中，返回的结果集是相同的。
连接查询是将两个或多个的表按某个条件连接起来，从中选取需要的数据，连接查询是同时查询两个或两个以上的表的使用的。当不同的表中存在相同意义的字段时，可以通过该字段来连接这几个表。
1.内连接查询
内连接查询是一种最常用的连接查询。内查询可以查询两个或两个以上的表。举例说明两个表的连接查询。当该字段的值相等时，就查询出该记录。
举个栗子~
SELECT num,name,employee.d_id,sex,d_name,function
FROM employee ，department
WHERE employee.d_id=department.d_id;
2.外连接查询
基本语法：
SELECT 属性名列表
FROM 表名1 LEFT | RIGHT JOIN 表名2
ON 表名1.属性1=表名2.属性2；
左连接查询
进行左连接查询时，可以查出表1的表中所有记录，而表2所指的表中，只能查询出匹配的记录
右连接查询
进行右连接查询时，可以查出表2的表中所有记录，而表1所指的表中，只能查询出匹配的记录
3.复合·条件查询
在连接查询时，也可以增加其他的限制条件，使查询结果更加准确。
子查询
子查询是将一个查询语句嵌套在另外一个查询语句中，内层查询语句的查询结果，可以为外层查询语句提供查询条件。
举个栗子~IN
SELECT * FROM employee
WHERE d_id IN
(SELECT d_id FROM department);
还举个栗子~比较运算符
SELECT id ,name, score FROM computer_stu
WHERE score>=
(SELECT score FROM scholarship
WHERE level=1)；
还举个栗子~EXISTS(表示存在，使用EXISTS关键字时，内层查询语句不反悔查询的记录，而是返回一个真假值)
SELECT * FROM employee
WHERE EXISTS
( SELECT d_name FROM department
WHERE d_id =1003)；
还举个栗子~ANY(表示满足其中任一条件)
如查询获得任意一个奖学金的最低分，即只要获得奖学金都满足条件
SELECT * FROM computer_stu
WHERE score >=ANY
( SELECT score FROM scholarship);
最后一个栗子~ALL(表示满足所有条件)
如查询获得最高奖学金的记录
SELECT * FROM computer_stu
WHERE score >=ALL
( SELECT score FROM scholarship);
