SQL多表查询之 where和INNER JOIN【基础查询和效率分析】

转载Michaelwubo 最后发布于2018-07-05 10:40:47 阅读数 18057  收藏

https://blog.csdn.net/u013372487/article/details/52622491?locationNum=1

https://blog.csdn.net/qingtanlang/article/details/2133816

http://www.cnblogs.com/drake-guo/p/6101531.html

https://blog.csdn.net/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会建议在你的环境中使用它。

1.  

   INNER JOIN（内连接,或等值连接）：获取两个表中字段匹配关系的记录。

2.  

   LEFT JOIN（左连接）：获取左表所有记录，即使右表没有对应匹配的记录。

3.  

   RIGHT JOIN（右连接）： 与 LEFT JOIN 相反，用于获取右表所有记录，即使左表没有对应匹配的记录。

注意： 
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

数据库在通过连接两张或多张表来返回记录时，都会生成一张中间的临时表，然后再将这张临时表返回给用户。

在使用left jion时，on和where条件的区别如下：

1、 on条件是在生成临时表时使用的条件，它不管on中的条件是否为真，都会返回左边表中的记录。

2、where条件是在临时表生成好后，再对临时表进行过滤的条件。这时已经没有left join的含义（必须返回左边表的记录）了，条件不为真的就全部过滤掉。

假设有两张表：

表1：tab2

表2：tab2

两条SQL:
1、select * form tab1 left join tab2 on (tab1.size = tab2.size) where tab2.name=’AAA’
2、select * form tab1 left join tab2 on (tab1.size = tab2.size and tab2.name=’AAA’)

其实以上结果的关键原因就是left join,right join,full join的特殊性，不管on上的条件是否为真都会返回left或right表中的记录，full则具有left和right的特性的并集。 而inner jion没这个特殊性，则条件放在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);

最后

以上就是彩色蜻蜓为你收集整理的SQL 多表查询之 where和INNER JOINSQL多表查询之 where和INNER JOIN【基础查询和效率分析】的全部内容，希望文章能够帮你解决SQL 多表查询之 where和INNER JOINSQL多表查询之 where和INNER JOIN【基础查询和效率分析】所遇到的程序开发问题。

如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。