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