`

left join, right join, full join, join 测试

MySQL 
阅读更多

一、准备测试数据：

 

  • 创建数据
  • Create database test;

 

  • 应用数据库
  • use test;

 

  • 创建表a

 

create table a(
ID int not null,
Name varchar(32)
);

 

  • 创建表b
create table b(
DD int not null,
DM varchar(32)
);

  

insert a(ID, Name) values(1,'paul');

insert a(ID, Name) values(2,'jude');

insert a(ID, Name) values(3,'Tom');

  

mysql> select * from a;
+----+------+
| ID | Name |
+----+------+
|  1 | paul  |
|  2 | jude  |
|  3 | Tom   |
+----+------+
3 rows in set (0.00 sec)

 

insert b(DD,DM) values(1,'Retail');

insert b(DD,DM) values(2,'IVS');

insert b(DD,DM) values(3,'IT');

insert b(DD,DM) values(4,'HRD');

  

mysql> select * from b;
+----+--------+
| DD | DM     |
+----+--------+
|  1 | Retail |
|  2 | IVS    |
|  4 | IT     |
|  5 | HRD    |
+----+--------+
4 rows in set (0.00 sec)

 

二、测试数据

 

  • inner join

mysql> select a.id,a.name,b.dd,b.dm from a inner join b on a.id=b.dd;
+----+------+----+--------+
| id | name | dd | dm     |
+----+------+----+--------+
|  1 | paul |  1 | Retail |
|  2 | jude |  2 | IVS    |
+----+------+----+--------+
2 rows in set (0.00 sec)

 

 

 

 

mysql> select a.id,a.name,b.dd,b.dm from a,b where a.id=b.dd;
+----+------+----+--------+
| id | name | dd | dm     |
+----+------+----+--------+
|  1 | paul |  1 | Retail |
|  2 | jude |  2 | IVS    |
+----+------+----+--------+
2 rows in set (0.00 sec)

 

mysql> select a.id,a.name,b.dd,b.dm from a inner join b on a.id>b.dd;
+----+------+----+--------+
| id | name | dd | dm     |
+----+------+----+--------+
|  2 | jude |  1 | Retail |
|  3 | Tom  |  1 | Retail |
|  3 | Tom  |  2 | IVS    |
+----+------+----+--------+
3 rows in set (0.00 sec)

 

mysql> select a.id,a.name,b.dd,b.dm from a,b where a.id>b.dd;
+----+------+----+--------+
| id | name | dd | dm     |
+----+------+----+--------+
|  2 | jude |  1 | Retail |
|  3 | Tom  |  1 | Retail |
|  3 | Tom  |  2 | IVS    |
+----+------+----+--------+
3 rows in set (0.00 sec)

 

mysql> select a.id,a.name,b.dd,b.dm from a inner join b on a.id>=b.dd;
+----+------+----+--------+
| id | name | dd | dm     |
+----+------+----+--------+
|  1 | paul |  1 | Retail |
|  2 | jude |  1 | Retail |
|  3 | Tom  |  1 | Retail |
|  2 | jude |  2 | IVS    |
|  3 | Tom  |  2 | IVS    |
+----+------+----+--------+
5 rows in set (0.00 sec)

 

mysql> select a.id,a.name,b.dd,b.dm from a,b where a.id>=b.dd;
+----+------+----+--------+
| id | name | dd | dm     |
+----+------+----+--------+
|  1 | paul |  1 | Retail |
|  2 | jude |  1 | Retail |
|  3 | Tom  |  1 | Retail |
|  2 | jude |  2 | IVS    |
|  3 | Tom  |  2 | IVS    |
+----+------+----+--------+
5 rows in set (0.00 sec)

 

mysql> select a.id,a.name,b.dd,b.dm from a inner join b on a.id<b.dd;
+----+------+----+------+
| id | name | dd | dm   |
+----+------+----+------+
|  1 | paul |  2 | IVS  |
|  1 | paul |  4 | IT   |
|  2 | jude |  4 | IT   |
|  3 | Tom  |  4 | IT   |
|  1 | paul |  5 | HRD  |
|  2 | jude |  5 | HRD  |
|  3 | Tom  |  5 | HRD  |
+----+------+----+------+
7 rows in set (0.00 sec)

 

mysql> select a.id,a.name,b.dd,b.dm from a,b where a.id<b.dd;
+----+------+----+------+
| id | name | dd | dm   |
+----+------+----+------+
|  1 | paul |  2 | IVS  |
|  1 | paul |  4 | IT   |
|  2 | jude |  4 | IT   |
|  3 | Tom  |  4 | IT   |
|  1 | paul |  5 | HRD  |
|  2 | jude |  5 | HRD  |
|  3 | Tom  |  5 | HRD  |
+----+------+----+------+
7 rows in set (0.00 sec)

 

mysql> select a.id,a.name,b.dd,b.dm from a inner join b on a.id<=b.dd;
+----+------+----+--------+
| id | name | dd | dm     |
+----+------+----+--------+
|  1 | paul |  1 | Retail |
|  1 | paul |  2 | IVS    |
|  2 | jude |  2 | IVS    |
|  1 | paul |  4 | IT     |
|  2 | jude |  4 | IT     |
|  3 | Tom  |  4 | IT     |
|  1 | paul |  5 | HRD    |
|  2 | jude |  5 | HRD    |
|  3 | Tom  |  5 | HRD    |
+----+------+----+--------+
9 rows in set (0.00 sec)

 

mysql> select a.id,a.name,b.dd,b.dm from a,b where a.id<=b.dd;
+----+------+----+--------+
| id | name | dd | dm     |
+----+------+----+--------+
|  1 | paul |  1 | Retail |
|  1 | paul |  2 | IVS    |
|  2 | jude |  2 | IVS    |
|  1 | paul |  4 | IT     |
|  2 | jude |  4 | IT     |
|  3 | Tom  |  4 | IT     |
|  1 | paul |  5 | HRD    |
|  2 | jude |  5 | HRD    |
|  3 | Tom  |  5 | HRD    |
+----+------+----+--------+
9 rows in set (0.00 sec)

 

mysql> select a.id,a.name,b.dd,b.dm from a inner join b on a.id<>b.dd;
+----+------+----+--------+
| id | name | dd | dm     |
+----+------+----+--------+
|  2 | jude |  1 | Retail |
|  3 | Tom  |  1 | Retail |
|  1 | paul |  2 | IVS    |
|  3 | Tom  |  2 | IVS    |
|  1 | paul |  4 | IT     |
|  2 | jude |  4 | IT     |
|  3 | Tom  |  4 | IT     |
|  1 | paul |  5 | HRD    |
|  2 | jude |  5 | HRD    |
|  3 | Tom  |  5 | HRD    |
+----+------+----+--------+
10 rows in set (0.00 sec)

 

mysql> select a.id,a.name,b.dd,b.dm from a,b where a.id<>b.dd;
+----+------+----+--------+
| id | name | dd | dm     |
+----+------+----+--------+
|  2 | jude |  1 | Retail |
|  3 | Tom  |  1 | Retail |
|  1 | paul |  2 | IVS    |
|  3 | Tom  |  2 | IVS    |
|  1 | paul |  4 | IT     |
|  2 | jude |  4 | IT     |
|  3 | Tom  |  4 | IT     |
|  1 | paul |  5 | HRD    |
|  2 | jude |  5 | HRD    |
|  3 | Tom  |  5 | HRD    |
+----+------+----+--------+
10 rows in set (0.00 sec)

 

  • left join

 

mysql> select a.id,a.name,b.dd,b.dm from a left join b on a.id=b.dd;
+----+------+------+--------+
| id | name | dd   | dm     |
+----+------+------+--------+
|  1 | paul |    1 | Retail |
|  2 | jude |    2 | IVS    |
|  3 | Tom  | NULL | NULL   |
+----+------+------+--------+
3 rows in set (0.00 sec)

 

mysql> select a.id,a.name,b.dd,b.dm from a left join b on a.id>b.dd;
+----+------+------+--------+
| id | name | dd   | dm     |
+----+------+------+--------+
|  1 | paul | NULL | NULL   |
|  2 | jude |    1 | Retail |
|  3 | Tom  |    1 | Retail |
|  3 | Tom  |    2 | IVS    |
+----+------+------+--------+
4 rows in set (0.00 sec)

 

mysql> select a.id,a.name,b.dd,b.dm from a left join b on a.id>=b.dd;
+----+------+------+--------+
| id | name | dd   | dm     |
+----+------+------+--------+
|  1 | paul |    1 | Retail |
|  2 | jude |    1 | Retail |
|  2 | jude |    2 | IVS    |
|  3 | Tom  |    1 | Retail |
|  3 | Tom  |    2 | IVS    |
+----+------+------+--------+
5 rows in set (0.00 sec)

  

 

mysql> select a.id,a.name,b.dd,b.dm from a left join b on a.id<b.dd;
+----+------+------+------+
| id | name | dd   | dm   |
+----+------+------+------+
|  1 | paul |    2 | IVS  |
|  1 | paul |    4 | IT   |
|  1 | paul |    5 | HRD  |
|  2 | jude |    4 | IT   |
|  2 | jude |    5 | HRD  |
|  3 | Tom  |    4 | IT   |
|  3 | Tom  |    5 | HRD  |
+----+------+------+------+
7 rows in set (0.00 sec)

  

mysql> select a.id,a.name,b.dd,b.dm from a left join b on a.id<=b.dd;
+----+------+------+--------+
| id | name | dd   | dm     |
+----+------+------+--------+
|  1 | paul |    1 | Retail |
|  1 | paul |    2 | IVS    |
|  1 | paul |    4 | IT     |
|  1 | paul |    5 | HRD    |
|  2 | jude |    2 | IVS    |
|  2 | jude |    4 | IT     |
|  2 | jude |    5 | HRD    |
|  3 | Tom  |    4 | IT     |
|  3 | Tom  |    5 | HRD    |
+----+------+------+--------+
9 rows in set (0.00 sec)

 

mysql> select a.id,a.name,b.dd,b.dm from a left join b on a.id<>b.dd;
+----+------+------+--------+
| id | name | dd   | dm     |
+----+------+------+--------+
|  1 | paul |    2 | IVS    |
|  1 | paul |    4 | IT     |
|  1 | paul |    5 | HRD    |
|  2 | jude |    1 | Retail |
|  2 | jude |    4 | IT     |
|  2 | jude |    5 | HRD    |
|  3 | Tom  |    1 | Retail |
|  3 | Tom  |    2 | IVS    |
|  3 | Tom  |    4 | IT     |
|  3 | Tom  |    5 | HRD    |
+----+------+------+--------+
10 rows in set (0.00 sec)

 

  • right join
mysql> select a.id,a.name,b.dd,b.dm from a right join b on a.id=b.dd;
+------+------+----+--------+
| id   | name | dd | dm     |
+------+------+----+--------+
|    1 | paul |  1 | Retail |
|    2 | jude |  2 | IVS    |
| NULL | NULL |  4 | IT     |
| NULL | NULL |  5 | HRD    |
+------+------+----+--------+
4 rows in set (0.00 sec)

 

 

mysql> select a.id,a.name,b.dd,b.dm from a right join b on a.id>b.dd;
+------+------+----+--------+
| id   | name | dd | dm     |
+------+------+----+--------+
|    2 | jude |  1 | Retail |
|    3 | Tom  |  1 | Retail |
|    3 | Tom  |  2 | IVS    |
| NULL | NULL |  4 | IT     |
| NULL | NULL |  5 | HRD    |
+------+------+----+--------+
5 rows in set (0.00 sec)

 

 

mysql> select a.id,a.name,b.dd,b.dm from a right join b on a.id>=b.dd;
+------+------+----+--------+
| id   | name | dd | dm     |
+------+------+----+--------+
|    1 | paul |  1 | Retail |
|    2 | jude |  1 | Retail |
|    3 | Tom  |  1 | Retail |
|    2 | jude |  2 | IVS    |
|    3 | Tom  |  2 | IVS    |
| NULL | NULL |  4 | IT     |
| NULL | NULL |  5 | HRD    |
+------+------+----+--------+
7 rows in set (0.00 sec)

 

 

mysql> select a.id,a.name,b.dd,b.dm from a right join b on a.id<b.dd;
+------+------+----+--------+
| id   | name | dd | dm     |
+------+------+----+--------+
| NULL | NULL |  1 | Retail |
|    1 | paul |  2 | IVS    |
|    1 | paul |  4 | IT     |
|    2 | jude |  4 | IT     |
|    3 | Tom  |  4 | IT     |
|    1 | paul |  5 | HRD    |
|    2 | jude |  5 | HRD    |
|    3 | Tom  |  5 | HRD    |
+------+------+----+--------+
8 rows in set (0.00 sec)

 

mysql> select a.id,a.name,b.dd,b.dm from a right join b on a.id<=b.dd;
+------+------+----+--------+
| id   | name | dd | dm     |
+------+------+----+--------+
|    1 | paul |  1 | Retail |
|    1 | paul |  2 | IVS    |
|    2 | jude |  2 | IVS    |
|    1 | paul |  4 | IT     |
|    2 | jude |  4 | IT     |
|    3 | Tom  |  4 | IT     |
|    1 | paul |  5 | HRD    |
|    2 | jude |  5 | HRD    |
|    3 | Tom  |  5 | HRD    |
+------+------+----+--------+
9 rows in set (0.00 sec)

 

mysql> select a.id,a.name,b.dd,b.dm from a right join b on a.id<>b.dd;
+------+------+----+--------+
| id   | name | dd | dm     |
+------+------+----+--------+
|    2 | jude |  1 | Retail |
|    3 | Tom  |  1 | Retail |
|    1 | paul |  2 | IVS    |
|    3 | Tom  |  2 | IVS    |
|    1 | paul |  4 | IT     |
|    2 | jude |  4 | IT     |
|    3 | Tom  |  4 | IT     |
|    1 | paul |  5 | HRD    |
|    2 | jude |  5 | HRD    |
|    3 | Tom  |  5 | HRD    |
+------+------+----+--------+
10 rows in set (0.00 sec)

 

  •  full join

错误执行代码：

mysql> select a.name,b.dm from a full join b on a.id=b.dd;
ERROR 1054 (42S22): Unknown column 'a.name' in 'field list'

 

 

mysql> select id,name,dd,dm from a full join b on id=dd;
+----+------+----+--------+
| id | name | dd | dm     |
+----+------+----+--------+
|  1 | paul |  1 | Retail |
|  2 | jude |  2 | IVS    |
+----+------+----+--------+
2 rows in set (0.00 sec)

 

mysql> select id,name,dd,dm from a full join b on id<dd;
+----+------+----+------+
| id | name | dd | dm   |
+----+------+----+------+
|  1 | paul |  2 | IVS  |
|  1 | paul |  4 | IT   |
|  2 | jude |  4 | IT   |
|  3 | Tom  |  4 | IT   |
|  1 | paul |  5 | HRD  |
|  2 | jude |  5 | HRD  |
|  3 | Tom  |  5 | HRD  |
+----+------+----+------+
7 rows in set (0.00 sec)

 

mysql> select id,name,dd,dm from a full join b on id<=dd;
+----+------+----+--------+
| id | name | dd | dm     |
+----+------+----+--------+
|  1 | paul |  1 | Retail |
|  1 | paul |  2 | IVS    |
|  2 | jude |  2 | IVS    |
|  1 | paul |  4 | IT     |
|  2 | jude |  4 | IT     |
|  3 | Tom  |  4 | IT     |
|  1 | paul |  5 | HRD    |
|  2 | jude |  5 | HRD    |
|  3 | Tom  |  5 | HRD    |
+----+------+----+--------+
9 rows in set (0.00 sec)

 

mysql> select id,name,dd,dm from a full join b on id>dd;
+----+------+----+--------+
| id | name | dd | dm     |
+----+------+----+--------+
|  2 | jude |  1 | Retail |
|  3 | Tom  |  1 | Retail |
|  3 | Tom  |  2 | IVS    |
+----+------+----+--------+
3 rows in set (0.00 sec)

 

mysql> select id,name,dd,dm from a full join b on id>=dd;
+----+------+----+--------+
| id | name | dd | dm     |
+----+------+----+--------+
|  1 | paul |  1 | Retail |
|  2 | jude |  1 | Retail |
|  3 | Tom  |  1 | Retail |
|  2 | jude |  2 | IVS    |
|  3 | Tom  |  2 | IVS    |
+----+------+----+--------+
5 rows in set (0.00 sec)

 

mysql> select id,name,dd,dm from a full join b on id<>dd;
+----+------+----+--------+
| id | name | dd | dm     |
+----+------+----+--------+
|  2 | jude |  1 | Retail |
|  3 | Tom  |  1 | Retail |
|  1 | paul |  2 | IVS    |
|  3 | Tom  |  2 | IVS    |
|  1 | paul |  4 | IT     |
|  2 | jude |  4 | IT     |
|  3 | Tom  |  4 | IT     |
|  1 | paul |  5 | HRD    |
|  2 | jude |  5 | HRD    |
|  3 | Tom  |  5 | HRD    |
+----+------+----+--------+
10 rows in set (0.00 sec)

 

分享到：
| Studio classroom 20100112
评论
发表评论

您还没有登录,请您登录后再发表评论

相关推荐

    inner join、 left join 、right join、 outer join之间的区别

    根据连接的方式不同，可以分为几种类型：`INNER JOIN`、`LEFT JOIN`、`RIGHT JOIN`以及`FULL OUTER JOIN`。下面将详细解释这些连接方式的区别。 #### INNER JOIN (内连接) `INNER JOIN`是最常用的一种连接方式，它...

    Linq to datable(Left join right join full join)实例

    Dim fullJoin = leftJoin.Union(rightJoin) ``` 在压缩包中的"WindowsApplication2"可能是VB2010项目文件，包含了具体的代码实现。通过查看和运行这个项目，你可以深入理解如何在实际应用中使用LINQ to ...

    Mysql之innerjoin,leftjoin,rightjoin详解.pdf

    Mysql 之 inner join、left join、right join 详解 Mysql 中的连接查询是指从多个表中检索数据，并将它们组合成一个结果集。inner join、left join 和 right join 是 Mysql 中三种最常用的连接查询方式。 inner ...

    SQL中的left outer join,inner join,right outer join用法详解

    * FULL OUTER JOIN：LEFT OUTER 和 RIGHT OUTER 中所有行的超集。 内连接（Inner Join） 内连接是最常见的一种连接，它也被称为普通连接，而 E.FCodd 最早称之为自然连接。例如： SELECT * FROM t_institution i ...

    SQL语句inner join,left join ,right join连接的不同之处

    根据连接方式的不同，可以分为多种类型，包括内连接（Inner Join）、左连接（Left Join）以及右连接（Right Join）。本文将详细探讨这三种连接方式的区别，并通过具体的例子来解释它们的应用场景。 ### 内连接...

    关于sql的left join,right join,inner join,outerjoin

    本篇文章将深入探讨四种基本的JOIN类型：LEFT JOIN（左连接）、RIGHT JOIN（右连接）、INNER JOIN（内连接）以及OUTER JOIN（外连接）。我们将详细解释这些概念，它们的工作原理以及在实际数据库操作中的应用。 1. ...

    SQL表连接查询(inner join、full join、left join、right join)1

    在SQL中，主要有四种类型的连接查询：内连接（INNER JOIN）、左连接（LEFT JOIN）、右连接（RIGHT JOIN）和全连接（FULL JOIN）。下面将详细解释这四种连接方式。 1. 内连接（INNER JOIN）： 内连接返回两个表中...

    left join 过滤条件写在on后面和写在where 后面的区别

    - **RIGHT JOIN (或 RIGHT OUTER JOIN)**：除了返回两个表中匹配的行外，还会返回右表中不匹配的所有行，并将左表的列设置为NULL。 - **FULL OUTER JOIN**：返回两个表中所有匹配和不匹配的行。 #### 示例表结构 ...

    join on 语句及扩展

    3. RIGHT JOIN (或 RIGHT OUTER JOIN): 右JOIN与LEFT JOIN相反，返回右表的所有记录，即使左表中没有匹配的记录。左表中未找到匹配项的记录将用NULL填充。语法： ```sql SELECT columns FROM table1 RIGHT JOIN ...

    MySQL JOIN 语法说明与 INNER JOIN 语法用法实例.docx

    本文档详细介绍了 MySQL 中的 JOIN 语法，包括 INNER JOIN、LEFT JOIN、RIGHT JOIN、FULL JOIN 等，并提供了实际的实例来说明 JOIN 的用法。 首先，MySQL 的 JOIN 语法用于根据两个或多个表中的字段之间的关系，从...

    sql join( inner join, outer join) 分析

    接下来，我们讨论OUTER JOIN，它分为LEFT JOIN、RIGHT JOIN和FULL JOIN。OUTER JOIN的目的是返回所有匹配的行以及至少一方表中的非匹配行。 - LEFT JOIN（或LEFT OUTER JOIN）返回左表中的所有行，即使右表中没有...

    oracle-join用法

    在Oracle SQL中，Join主要分为几个类型，包括INNER JOIN、LEFT JOIN (LEFT OUTER JOIN)、RIGHT JOIN (RIGHT OUTER JOIN)以及FULL JOIN (FULL OUTER JOIN)。这些类型在Java编程中同样可以使用，通过SQL查询语句来实现...

    sql left join 命令详解

    此外，LEFT JOIN还可以与其他类型的JOIN结合使用，如FULL OUTER JOIN（全外联接），它会返回左右两边表的所有行，无论是否有匹配的记录。在某些情况下，这可能是收集完整数据集的必要手段。 总结一下，LEFT JOIN是...

    mysql Join使用以及优化

    基本的Join类型包括INNER JOIN、LEFT JOIN、RIGHT JOIN和FULL JOIN等。 INNER JOIN仅返回两个表中匹配的记录，而LEFT JOIN、RIGHT JOIN和FULL JOIN分别返回左表、右表以及两个表中所有的记录，即使某些记录在另一表...

    sql中join总结

    3. **左外JOIN (LEFT OUTER JOIN)**: 左外JOIN返回所有左侧表格（在这个例子中是`A_test`）的记录，即使右侧表格（`B_test`）没有匹配的记录。对于右侧表格中没有匹配的记录，所有字段都会填充为NULL。在示例中，`3...

    SQL _join on 和where的执行顺序1

    JOIN操作主要有四种类型：LEFT JOIN、RIGHT JOIN、INNER JOIN和FULL JOIN，每种都有其特定的用途。 1. LEFT JOIN（左连接）： LEFT JOIN返回左表（即在JOIN语句前的表）的所有记录，即使在右表中没有匹配的记录。...

    hadoop Join代码（map join 和reduce join）

    Hive和Pig都支持多种JOIN类型，如LEFT JOIN、RIGHT JOIN、FULL OUTER JOIN等，并且能够自动优化JOIN策略。 Hive示例： ```sql SELECT a.*, b.* FROM table_a JOIN table_b ON (a.join_field = b.join_field); `...

Magicbox
Global site tag (gtag.js) - Google Analytics