`
acen.chen
  • 浏览: 157623 次
  • 性别: Icon_minigender_1
  • 来自: 深圳
社区版块
存档分类
最新评论

inner join&left outer join&right outer join

阅读更多
left outer join === left join
rirht outer join === right join
full outer join === full join
inner join  === A = B
 
no full inner join
no left inner join
no right inner join
 
they are the same as the "inner join"
 
 
 
 
Join types

By default, a join is assumed to be an inner join. You can also request other types of joins by clicking Join Type on the Joins page of SQL Assist. The following types of joins are available:

  • Inner join
  • Left outer join
  • Right outer join
  • Full outer join

7 An inner join is join method in which 7 a column that is not common to all of the tables being joined is dropped from 7 the resultant table. If your database supports the OUTER JOIN keywords, you 7 can extend the inner join to add rows from one table that have no matching 7 rows in the other table.

For example, you want to join two tables to get the last name of the manager for each department. The first table is a Department table that lists the employee number of each department manager. The second table is an Employee table that lists the employee number and last name of each employee. However, some departments do not have a manager; in these cases, the employee number of the department manager is null. To include all departments regardless of whether they have a manager, and the last name of the manager, if one exists, you create a left outer join. The left outer join includes rows in the first table that match the second table or are null. The resulting SQL statement is as follows:

SELECT DEPTNO, DEPTNAME, EMPNO, LASTNAME
   FROM DEPARTMENT LEFT OUTER JOIN EMPLOYEE
      ON MGRNO = EMPNO 

A right outer join is the same as a left outer join, except that it includes rows in the second table that match the first table or are null. A full outer join includes matching rows and null rows from both tables.

For example, you have two tables, Table 1 and Table 2, with the following data:

Table 1. Table 1 Column A Column B
1 A
2 B
3 C
Table 2. Table 2 Column C Column D
2 X
4 2

You specify a join condition of Column A = Column C. The result tables for the different types of joins are as follows:

Inner join
Table 3. Inner join result table Column A Column B Column C Column D
2 B 2 X
Left outer join
Table 4. Left outer join result table Column A Column B Column C Column D
1 A null null
2 B 2 X
3 C null null
Right outer join
Table 5. Right outer join result table Column A Column B Column C Column D
2 B 2 X
null null 4 2
Full outer join
Table 6. Full outer join result table Column A Column B Column C Column D
1 A null null
2 B 2 X
3 C null null
null null 4 2

If you specify value (a,c), you obtain the following result:

Table 7. Result of value (a,c) Value (a,c)
1
2
3
4
Related concepts
分享到:
评论

相关推荐

    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 ...

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

    ### inner join、left join、right join、outer join之间的区别 在数据库操作中,连接(Join)是一种非常重要的操作,用于组合两个或多个表中的数据。根据连接的方式不同,可以分为几种类型:`INNER JOIN`、`LEFT ...

    Mysql之innerjoin,leftjoin,rightjoin详解.pdf

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

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

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

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

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

    (Left join , Right Join, Inner Join)用法详解

    ### (Left join , Right Join, Inner Join)用法详解 #### 一、基本概念与应用场景 在数据库查询语言SQL中,连接(Join)是一种非常重要的操作,它允许我们结合两个或多个表的数据来检索信息。根据不同的连接方式,...

    inner join and outer join.pdf

    - **Outer Join**(外部连接)可以分为 Left Outer Join、Right Outer Join 和 Full Outer Join。 - 这种类型的连接不仅返回满足连接条件的行,还返回一个表中所有行(对于 Left 或 Right)或两个表中所有行(对于 ...

    sql join( inner join, outer join) 分析

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

    SQL联合查询inner join、outer join和cross join的区别详解

    这三者的区别很多人都应该不是很清楚,包括我自己,下面我们一起来看看,如果你使用join连表,缺陷的情况下是inner join,另外,开发中使用的left join和right join属于outer join,而outer join还包括full join....

    SQL中inner join、outer join和cross join的区别

    根据不同的连接方式,`JOIN`可以分为几种类型,包括`INNER JOIN`、`OUTER JOIN`(左外联接`LEFT JOIN`、右外联接`RIGHT JOIN`以及全外联接`FULL JOIN`)和`CROSS JOIN`。每种类型的`JOIN`都有其独特的应用场景和特点。...

    inner join-outer join-cross join查询方式总结

    根据连接方式的不同,可以分为内连接(INNER JOIN)、外连接(LEFT JOIN、RIGHT JOIN 和 FULL JOIN)以及交叉连接(CROSS JOIN)。下面我们将详细探讨每种连接方式的特点、适用场景及其具体的实现方法。 #### 内...

    数据库的外联和内联知识 Inner Join

    在关系数据库管理系统中,SQL 语言提供了多种类型的连接方式,如 inner join、left outer join、right outer join、full outer join、cross join 等。 inner join,也称为等值连接或自然连接,是最常见的一种连接。...

    join on 语句及扩展

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

    SQL left join

    在本文中,我们将详细介绍SQL Left Join的使用方法、特点和区别,以及与Right Join和Inner Join的比较。 一、SQL Left Join的定义和使用 SQL Left Join也称为左外连接,它可以将左表中的所有记录与右表中的记录...

    数据库Left join , Right Join, Inner Join 的相关内容,非常实用

    本文将详细解析LEFT JOIN、RIGHT JOIN以及INNER JOIN三种常见的JOIN类型。 INNER JOIN是数据库查询中最基本的JOIN类型,它只返回两个表中匹配的行。在描述的示例中,如果我们要从Table A和Table B中获取aid与bid...

    SQL多表连接查询、多表关联

    inner join,full outer join,left join,right jion 内部连接 inner join 两表都满足的组合 full outer 全连 两表相同的组合在一起,A表有,B表没有的数据(显示为null),同样B表有 A表没有的显示为(null) A表 left ...

    oracle-join用法

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

    关于in and inner/outer join and exists

    外连接分为LEFT JOIN(左外连接)、RIGHT JOIN(右外连接)和FULL OUTER JOIN(全外连接)。它们会返回所有满足连接条件的记录,以及其中一个表中的所有不满足条件的记录。如果某条记录在另一个表中没有匹配项,...

    【SQL高级(三)】【SQL 连接(JOIN)】

    文章目录SQL 连接(JOIN)不同的 SQL JOINSQL INNER JOIN 关键字SQL LEFT JOIN 关键字SQL RIGHT JOIN 关键字SQL FULL OUTER JOIN 关键字 SQL 连接(JOIN) SQL join 用于把来自两个或多个表的行结合起来。 下图展示了 ...

Global site tag (gtag.js) - Google Analytics