(1)内连接查询
(2)外连接查询
(3)复合条件连接查询
（免费学习推荐：mysql视频教程）
(1)、内连接查询(inner join)内连接使用比较运算符进行表间某(某些)列数据的比较操作，并列出这些表中与连接条件向匹配的数据行，组成新纪录，也就是说，在内连接查询中，只有满足条件的记录才能出现在结果关系中。下面创建数据表suppliers进行演示：
mysql> 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) -> );query ok, 0 rows affected (0.17 sec)mysql> 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','taiyuan','030000','22222'), -> (106,'just eat ours','beijing','010','45678'), -> (107,'dk inc','zhengzhou','450000','33332');query ok, 7 rows affected (0.07 sec)records: 7 duplicates: 0 warnings: 0
【例1】在fruits表和suppliers表之间使用内连接查询。
mysql> desc fruits;+---------+--------------+------+-----+---------+-------+| field | type | null | key | default | extra |+---------+--------------+------+-----+---------+-------+| f_id | char(10) | no | pri | null | || s_id | int(11) | no | | null | || f_name | char(255) | no | | null | || f_price | decimal(8,2) | no | | null | |+---------+--------------+------+-----+---------+-------+4 rows in set (0.06 sec)mysql> desc suppliers;+--------+----------+------+-----+---------+----------------+| field | type | null | key | default | extra |+--------+----------+------+-----+---------+----------------+| s_id | int(11) | no | pri | null | auto_increment || s_name | char(50) | no | | null | || s_city | char(50) | yes | | null | || s_zip | char(10) | yes | | null | || s_call | char(50) | no | | null | |+--------+----------+------+-----+---------+----------------+5 rows in set (0.00 sec)
可以看到fruits和suppliers表中都有相同数据类型的字段s_id,两个表通过s_id字段建立联系。
接下来从fruits表中查询f_name、f_price字段，从suppliers表中查询s_id、s_name，sql语句如下：
mysql> select suppliers.s_id,s_name,f_name,f_price -> from fruits,suppliers -> where fruits.s_id=suppliers.s_id;+------+----------------+------------+---------+| s_id | s_name | f_name | f_price |+------+----------------+------------+---------+| 104 | fnk inc. | lemon | 6.40 || 101 | fastfruit inc. | apple | 5.20 || 103 | acme | apricot | 2.20 || 101 | fastfruit inc. | blackberry | 10.20 || 104 | fnk inc. | berry | 7.60 || 107 | dk inc | xxxx | 3.60 || 102 | lt supplies | orange | 11.20 || 105 | good set | melon | 8.20 || 101 | fastfruit inc. | cherry | 3.20 || 106 | just eat ours | mango | 15.70 || 105 | good set | xbabay | 2.60 || 105 | good set | xxtt | 11.60 || 103 | acme | coconut | 9.20 || 102 | lt supplies | banana | 10.30 || 102 | lt supplies | grape | 5.30 || 107 | dk inc | xbabay | 3.60 |+------+----------------+------------+---------+16 rows in set (0.00 sec)
注意：因为fruits表和suppliers表中有相同的字段s_id，因此在比较的时候，需要完全限定表名，格式为表名.列名，如果只给出s_id，mysql将不知道指的是哪一个，并返回错误信息。【例2】在fruits表和suppliers表之间，使用inner join语法进行内连接查询，sql语句如下：
mysql> select suppliers.s_id,s_name,f_name,f_price -> from fruits inner join suppliers -> on fruits.s_id = suppliers.s_id;+------+----------------+------------+---------+| s_id | s_name | f_name | f_price |+------+----------------+------------+---------+| 104 | fnk inc. | lemon | 6.40 || 101 | fastfruit inc. | apple | 5.20 || 103 | acme | apricot | 2.20 || 101 | fastfruit inc. | blackberry | 10.20 || 104 | fnk inc. | berry | 7.60 || 107 | dk inc | xxxx | 3.60 || 102 | lt supplies | orange | 11.20 || 105 | good set | melon | 8.20 || 101 | fastfruit inc. | cherry | 3.20 || 106 | just eat ours | mango | 15.70 || 105 | good set | xbabay | 2.60 || 105 | good set | xxtt | 11.60 || 103 | acme | coconut | 9.20 || 102 | lt supplies | banana | 10.30 || 102 | lt supplies | grape | 5.30 || 107 | dk inc | xbabay | 3.60 |+------+----------------+------------+---------+16 rows in set (0.00 sec)
使用where子句定义连接条件比较简单明了，而inner join语法是ansi sql 的标准规范，使用inner join连接语法能够确保不会忘记连接条件，而且，where子句在某些时候回影响查询的性能。如果在一个连接查询中涉及的两个表都是同一个表，name这种查询称为自连接查询。自连接是一种特殊的内连接，是指相互连接的表在物理上为同一张表，但可以在逻辑上分为两张表。
【例3】查询供应f_id='a1'的水果供应商提供的水果种类，sql语句如下：
mysql> 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';+------+------------+| f_id | f_name |+------+------------+| a1 | apple || b1 | blackberry || c0 | cherry |+------+------------+3 rows in set (0.00 sec)
(2)、外连接查询外连接查询将查询多个表中相关联的行。内连接时，返回查询结果集合中的仅是符合查询条件和连接条件的行。但有时候需要包含没有关联的行中数据，即返回查询结果集合中的不仅包含符合连接条件的行，而且还包括左表(左外连接或左连接)、右表(右外连接或右连接)或两个边接表(全外连接)中的所有数据行。外连接分为左外连接或左连接和右外连接或右连接：①左连接：返回包括左表中的所有记录和右表中连接字段相等的记录。
②右连接：返回包括右表中的记录和左表中连接字段相等的记录。
1.左连接left join
首先创建表orders，sql语句如下;
mysql> create table orders -> ( -> o_num int not null auto_increment, -> o_date datetime not null, -> c_id int not null, -> primary key (o_num) -> );query ok, 0 rows affected (0.11 sec)mysql> 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);query ok, 5 rows affected (0.06 sec)records: 5 duplicates: 0 warnings: 0
【例】在customers表和orders表中，查询所有客户，包括没有订单的客户，sql语句如下：
mysql> select * from customers;+-------+----------+---------------------+---------+--------+-----------+-------------------+| c_id | c_name | c_address | c_city | c_zip | c_contact | c_email |+-------+----------+---------------------+---------+--------+-----------+-------------------+| 10001 | redhool | 200 street | tianjin | 300000 | liming | lming@163.com || 10002 | stars | 333 fromage lane | dalian | 116000 | zhangbo | jerry@hotmail.com || 10003 | netbhood | 1 sunny place | qingdao | 266000 | luocong | null || 10004 | joto | 829 riverside drive | haikou | 570000 | yangshan | sam@hotmail.com |+-------+----------+---------------------+---------+--------+-----------+-------------------+4 rows in set (0.00 sec)mysql> select * from orders;+-------+---------------------+-------+| o_num | o_date | c_id |+-------+---------------------+-------+| 30001 | 2008-09-01 00:00:00 | 10001 || 30002 | 2008-09-12 00:00:00 | 10003 || 30003 | 2008-09-30 00:00:00 | 10004 || 30004 | 2008-10-03 00:00:00 | 10005 || 30005 | 2008-10-08 00:00:00 | 10001 |+-------+---------------------+-------+5 rows in set (0.00 sec)mysql> select customers.c_id , orders.o_num -> from customers left outer join orders -> on customers.c_id = orders.c_id;+-------+-------+| c_id | o_num |+-------+-------+| 10001 | 30001 || 10003 | 30002 || 10004 | 30003 || 10001 | 30005 || 10002 | null |+-------+-------+5 rows in set (0.00 sec)
2.右连接right join
右连接是左连接的反向连接，将返回右表的所有行。如果右表的某行在左表中没有匹配行，左表将返回空值。【例】在customers表和orders表中，查询所有订单，包括没有客户的订单，sql语句如下：
mysql> select customers.c_id, orders.o_num -> from customers right outer join orders -> on customers.c_id = orders.c_id;+-------+-------+| c_id | o_num |+-------+-------+| 10001 | 30001 || 10003 | 30002 || 10004 | 30003 || null | 30004 || 10001 | 30005 |+-------+-------+5 rows in set (0.00 sec)
(3)、复合条件连接查询复合条件连接查询是在连接查询的过程中，通过添加过滤条件，限制查询结果，使查询的结果更加准确。【例1】在customers表和orders表中，使用inner join语法查询customers表汇总id为10001的客户的订单信息，sql语句如下：
mysql> select customers.c_id,orders.o_num -> from customers inner join orders -> on customers.c_id = orders.c_id and customers.c_id = 10001;+-------+-------+| c_id | o_num |+-------+-------+| 10001 | 30001 || 10001 | 30005 |+-------+-------+2 rows in set (0.00 sec)
【例2】在fruits表和suppliers表之间，使用inner join语法进行内连接查询，并对查询结果排序，sql语句如下：
mysql> 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;+------+----------------+------------+---------+| s_id | s_name | f_name | f_price |+------+----------------+------------+---------+| 101 | fastfruit inc. | apple | 5.20 || 101 | fastfruit inc. | blackberry | 10.20 || 101 | fastfruit inc. | cherry | 3.20 || 102 | lt supplies | orange | 11.20 || 102 | lt supplies | banana | 10.30 || 102 | lt supplies | grape | 5.30 || 103 | acme | apricot | 2.20 || 103 | acme | coconut | 9.20 || 104 | fnk inc. | lemon | 6.40 || 104 | fnk inc. | berry | 7.60 || 105 | good set | melon | 8.20 || 105 | good set | xbabay | 2.60 || 105 | good set | xxtt | 11.60 || 106 | just eat ours | mango | 15.70 || 107 | dk inc | xxxx | 3.60 || 107 | dk inc | xbabay | 3.60 |+------+----------------+------------+---------+16 rows in set (0.00 sec)
更多相关免费学习推荐：mysql教程(视频)
以上就是mysql数据查询之连接查询的详细内容。