sql数据库多表查询之 where和INNER JOIN

最新推荐文章于 2024-04-29 11:45:00 发布

lele_44839345

最新推荐文章于 2024-04-29 11:45:00 发布

阅读量7.8k

点赞数 4

分类专栏： sql基础文章标签： sql

sql基础专栏收录该内容

20 篇文章 1 订阅

订阅专栏

在多表查询中，一些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会建议在你的环境中使用它。

INNER JOIN（内连接,或等值连接）：获取两个表中字段匹配关系的记录。
LEFT JOIN（左连接）：获取左表所有记录，即使右表没有对应匹配的记录。
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

id	size
1	10
2	20
3	30

表2：tab2

size	name
10	AAA
20	BBB
30	CCC

两条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);

lele_44839345

关注

4
点赞
踩
38

收藏

觉得还不错? 一键收藏
1
评论
sql数据库多表查询之 where和INNER JOIN

在多表查询中，一些SQL开发人员更喜欢使用WHERE来做join，比如：SELECT a.ID, b.Name, b.Date FROM Customers a, Sales b WHERE a.ID = b.ID;1缺点：在上面语句中，实际上是创建了两张表的笛卡尔积，所有可能的组合都会被创建出来。在笛卡尔连接中，在上面的例子中，如果有1000顾客和1000条销售记录，这个查询会先产生100...
复制链接

扫一扫