inner join,outer join,left join,right join的区别

外联接

外联接可以是左向外联接、右向外联接或完整外部联接。
在 FROM 子句中指定外联接时，可以由下列几组关键字中的一组指定：

LEFT JOIN 或 LEFT OUTER JOIN。
左向外联接的结果集包括 LEFT OUTER 子句中指定的左表的所有行，而不仅仅是联接列所匹配的行。如果左表的某行在右表中没有匹配行，则在相关联的结果集行中右表的所有选择列表列均为空值。

RIGHT JOIN 或 RIGHT OUTER JOIN。
右向外联接是左向外联接的反向联接。将返回右表的所有行。如果右表的某行在左表中没有匹配行，则将为左表返回空值。

FULL JOIN 或 FULL OUTER JOIN。
完整外部联接返回左表和右表中的所有行。当某行在另一个表中没有匹配行时，则另一个表的选择列表列包含空值。如果表之间有匹配行，则整个结果集行包含基表的数据值。

例如：

表a

id              name

1               a
2               b
3               c

表b

id              store

1              15
2              10
4              67

inner join ：

select * from a inner join b on a.id=b.id

结果：（只显示id匹配的选项）

id            name id store

1              a                 1             15
2              b                 2             10

内连接还有以下写法：

select * from a inner join b where a.id=b.id
select * from a,b where a.id=b.id（连接查询的另一种写法）
select * from a as e,b as r where e.id=r.id（使用as定义别名，当表名很长时有用）
select * from a e,b r where e.id=r.id（定义别名时可以省掉as）

外连接分左外连接和右外连接

左外连接：

select * from a left outer join b on a.id=b.id

或：

select * from a left join b on a.id=b.id

结果：（除了显示匹配记录，还显示a表中所有的记录）

id            name            id            stroe

1              a                 1             15
2              b                 2             10
3              c                /N             /N

右外连接：

select * from a right outer join b on a.id=b.id

或：
select * from a right join b on a.id=b.id

结果：（除了显示匹配的记录，还显示右表中所有的记录）

id            name            id            store

1              a                1              15
2              b                2              10
/N            /N               4              67

PS:本篇是翻译而来,原文如下:

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