MySQL连接查询_mysql 连接查询

连接是关系数据库模型的主要特点。连接查询是关系数据库中最主要的查询，主要包括内连 接、外连接等。通过连接运算符可以实现多个表查询。在关系数据库管理系统中，表建立时各数据 之间的关系不必确定，常把一个实体的所有信息存放在一个表中。当查询数据时，通过连接操作查 询出存放在多个表中的不同实体的信息。当两个或多个表中存在相同意义的字段时，便可以通过这 些字段对不同的表进行连接查询。本节将介绍多表之间的内连接查询、外连接查询以及复合条件连 接查询。

1. 内连接查询

内连接（INNER JOIN）使用比较运算符进行表间某（些）列数据的比较操作，并列出这些表 中与连接条件相匹配的数据行，组合成新的记录，也就是说，在内连接查询中，只有满足条件的记 录才能出现在结果关系中。 为了演示的需要，首先创建数据表 suppliers，SQL 语句如下：

CREATE TABLE suppliers
(
 s_id int NOT NULL AUTO_INCREMENT,
 s_name char(50) NOT NULL,
 s_city char(50) NULL,
 s_zip char(10) NULL,
 s_call CHAR(50) NOT NULL,
 PRIMARY KEY (s_id)
) ; 

插入需要演示的数据，SQL 语句如下：

INSERT INTO suppliers(s_id, s_name,s_city, s_zip, s_call)
VALUES(101,'FastFruit Inc.','Tianjin','300000','48075'),
(102,'LT Supplies','Chongqing','400000','44333'),
(103,'ACME','Shanghai','200000','90046'),
(104,'FNK Inc.','Zhongshan','528437','11111'),
(105,'Good Set','Taiyuang','030000', '22222'),
(106,'Just Eat Ours','Beijing','010', '45678'),
(107,'DK Inc.','Zhengzhou','450000', '33332'); 

在 fruits 表和 suppliers 表之间使用内连接查询。 查询之前，查看两个表的结构：

 DESC suppliers;

由结果可以看到，fruits 表和 suppliers 表中都有相同数据类型的字段 s_id，两个表通过 s_id 字 段建立联系。接下来从 fruits 表中查询 f_name、f_price 字段，从 suppliers 表中查询 s_id、s_name， SQL 语句如下：

SELECT suppliers.s_id, s_name,f_name, f_price FROM fruits ,suppliers WHERE fruits.s_id = suppliers.s_id;

在这里，SELECT 语句与前面所介绍的一个最大的差别是：SELECT 后面指定的列分别属于两 个不同的表，（f_name，f_price）在表 fruits 中，而另外两个字段在表 suppliers 中；同时 FROM 子 句列出了两个表 fruits 和 suppliers。WHERE 子句在这里作为过滤条件，指明只有两个表中的 s_id 字段值相等的时候才符合连接查询的条件。从返回的结果可以看到，显示的记录是由两个表中的不 同列值组成的新记录。

提 示 ：因为 fruits 表和 suppliers 表中有相同的字段 s_id，因此在比较的时候需要完全限定表名（格 式为“表名.列名”），如果只给出 s_id，MySQL 将不知道指的是哪一个，并返回错误信息。

下面的内连接查询语句返回与前面完全相同的结果。 在 fruits 表和 suppliers 表之间，使用 INNER JOIN 语法进行内连接查询，SQL 语句 如下：

SELECT suppliers.s_id, s_name,f_name, f_price FROM fruits INNER JOIN suppliers ON fruits.s_id = suppliers.s_id;

在这里的查询语句中，两个表之间的关系通过 INNER JOIN 指定。使用这种语法的时候，连 接的条件使用 ON 子句而不是 WHERE，ON 和 WHERE 后面指定的条件相同。

提 示 ：使用 WHERE 子句定义连接条件比较简单明了，而 INNER JOIN 语法是 ANSI SQL 的标 准规范，使用 INNER JOIN 连接语法能够确保不会忘记连接条件，而且 WHERE 子句在 某些时候会影响查询的性能。

如果在一个连接查询中，涉及的两个表都是同一个表，这种查询称为自连接查询。自连接是 一种特殊的内连接，它是指相互连接的表在物理上为同一张表，但可以在逻辑上分为两张表。 查询 f_id= 'a1'的水果供应商提供的水果种类，SQL 语句如下：

 SELECT f1.f_id, f1.f_name FROM fruits AS f1, fruits AS f2 WHERE f1.s_id = f2.s_id AND f2.f_id = 'a1';

此处查询的两个表是相同的表，为了防止产生二义性，对表使用了别名，fruits 表第 1 次出现 的别名为 f1，第 2 次出现的别名为 f2，使用 SELECT 语句返回列时明确指出返回以 f1 为前缀的列 的全名，WHERE 连接两个表，并按照第 2 个表的 f_id 对数据进行过滤，返回所需数据。

2. 外连接查询

外连接查询将查询多个表中相关联的行，内连接时，返回查询结果集合中仅是符合查询条件 和连接条件的行。有时候需要包含没有关联的行中数据，即返回查询结果集合中不仅包含符合连接 条件的行，还包括左表（左外连接或左连接）、右表（右外连接或右连接）或两个边接表（全外连 接）中的所有数据行。外连接分为左外连接或左连接和右外连接或右连接： LEFT JOIN（左连接）：返回包括左表中的所有记录和右表中连接字段相等的记录。 RIGHT JOIN（右连接）：返回包括右表中的所有记录和左表中连接字段相等的记录。 1. LEFT JOIN 左连接 左连接的结果包括 LEFT OUTER 子句中指定的左表的所有行，而不仅仅是连接列所匹配的行。 如果左表的某行在右表中没有匹配行，则在相关联的结果行中，右表的所有选择列表列均为空值。 首先创建表 orders，SQL 语句如下：

CREATE TABLE orders
(
 o_num int NOT NULL AUTO_INCREMENT,
 o_date datetime NOT NULL,
 c_id int NOT NULL,
 PRIMARY KEY (o_num)
) ; 

插入需要演示的数据，SQL 语句如下：

INSERT INTO orders(o_num, o_date, c_id)
VALUES(30001, '2008-09-01', 10001),
(30002, '2008-09-12', 10003),
(30003, '2008-09-30', 10004),
(30004, '2008-10-03', 10005),
(30005, '2008-10-08', 10001);

在 customers 表和 orders 表中，查询所有客户，包括没有订单的客户，SQL 语句如 下：

SELECT customers.c_id, orders.o_num FROM customers LEFT OUTER JOIN orders ON customers.c_id = orders.c_id;

结果显示了 5 条记录，ID 等于 10002 的客户目前并没有下订单，所以对应的 orders 表中并没 有该客户的订单信息，所以该条记录只取出了 customers 表中相应的值，而从 orders 表中取出的值 为空值 NULL

2. RIGHT JOIN 右连接 右连接是左连接的反向连接，将返回右表的所有行。如果右表的某行在左表中没有匹配行， 左表将返回空值。 在 customers 表和 orders 表中，查询所有订单，包括没有客户的订单，SQL 语句如 下：

 SELECT customers.c_id, orders.o_num FROM customers RIGHT OUTER JOIN orders ON customers.c_id = orders.c_id;

结果显示了 5 条记录，订单号等于 30004 的订单的客户可能由于某种原因取消了该订单，对 应的 customers 表中并没有该客户的信息，所以该条记录只取出了 orders 表中相应的值，而从 customers 表中取出的值为空值 NULL。

3. 复合条件连接查询

复合条件连接查询是在连接查询的过程中，通过添加过滤条件限制查询的结果，使查询的结 果更加准确。 在 customers 表和 orders 表中，使用 INNER JOIN 语法查询 customers 表中 ID 为 10001 的客户的订单信息，SQL 语句如下：

SELECT customers.c_id, orders.o_num FROM customers INNER JOIN orders ON customers.c_id = orders.c_id AND customers.c_id = 10001;

结果显示，在连接查询时指定查询客户 ID 为 10001 的订单信息，添加了过滤条件之后返回的 结果将会变少，因此返回结果只有两条记录。 使用连接查询，并对查询的结果进行排序。 在 fruits 表和 suppliers 表之间，使用 INNER JOIN 语法进行内连接查询，并对查询 结果排序，SQL 语句如下：

SELECT suppliers.s_id, s_name,f_name, f_price FROM fruits INNER JOIN suppliers ON fruits.s_id = suppliers.s_id ORDER BY fruits.s_id;

由结果可以看到，内连接查询的结果按照 suppliers.s_id 字段进行了升序排序。