db数据库连接失败dbms_DBMS中的内部联接与外部联接

db数据库连接失败dbms

Consider the given tables:

考虑给定的表：

Student1 –

学生1 –

RollNo	Name	Marks
1	Ashish	98
2	Shivam	72
3	Tarun	53
4	Yash	89

卷号	名称	分数
1个	阿什什	98
2	湿婆	72
3	塔伦	53
4	亚什	89

Student2 –

学生2 –

RollNo	Name	Marks
1	Anjali	99
4	Dinesh	79
5	Harsh	95
7	Kartik	88

卷号	名称	分数
1个	安贾利	99
4	Dinesh	79
5	苛刻	95
7	卡蒂克	88

内部联接 (Inner Join )

Inner Join is that type of join which it returns the record that has matching values in both tables. There are mainly three types of join which we have studied about in Extended Operators in Relation Algebra section, i.e., Condition Join, Equi Join, and Natural Join. It is denoted by the symbol ⋈.

内部联接是一种联接类型，它返回两个表中具有匹配值的记录。 在关系代数部分的扩展算子中 ，我们主要研究了三种连接类型，即条件连接 ， 等式连接和自然连接 。 用符号⋈表示。

For e.g. – Select the students from Student1 whose RollNo is equal to the RollNo of Student2 table.

例如，–从Student1中选择RollNo等于Student2的RollNo表的学生。

Student1⋈_{Student1.RollNo = Student2.RollNo}Student2

_{Student1⋈Student1.RollNo = Student2.RollNo}学生2

Syntax:

句法：

    SELECT * FROM Student1 
    INNER JOIN Student2 ON Student1.RollNo = Student2.RollNo;

Output

输出量

外连接 (Outer Join)

There are three types of Outer Join,

外部联接有三种类型，

a) Left Outer Join

a)左外连接

Left Outer Join is that type of Join which returns all records from the left table and the matched record from the right table. It is denoted by the symbol ⟕.

左外部联接是这种联接类型，它返回左表中的所有记录和右表中的匹配记录。 用符号⟕表示。

For e.g. – Select the students from Student1 whose RollNo is greater than the RollNo of Student2 table.

例如，–从Student1中选择RollNo大于Student2的RollNo表的学生。

Student1⟕_{Student1.RollNo>Student2.RollNo}Student2

_{Student1⟕Student1.RollNo> Student2.RollNo} Student2

Syntax:

句法：

    SELECT * FROM Student1 
    LEFT OUTER JOIN Student2 ON Student1.RollNo > Student2.RollNo;

Output

输出量

b) Right Outer Join

b)右外连接

Right Outer Join is that type of Join which returns all records from the right table and the matched record from the left table. It is denoted by the symbol ⟖.

右外部联接是一种联接类型，它从右表返回所有记录，并从左表返回匹配的记录。 用符号⟖表示。

For e.g. – Select the students from Student1 whose RollNo is greater than the RollNo of Student2 table.

例如，–从Student1中选择RollNo大于Student2的RollNo表的学生。

Student1⟖_{Student1.RollNo>Student2.RollNo}Student2

_{Student1⟖Student1.RollNo> Student2.RollNo} Student2

Syntax:

句法：

    SELECT * FROM Student1 
    RIGHT OUTER JOIN Student2 ON Student1.RollNo > Student2.RollNo;

Output

输出量

c) Full Outer Join

c)完全外部联接

Full Outer Join is that type of Join which returns all records when there is a match in either the left or right table. It is denoted by the symbol ⟗.

完全外部联接是一种联接类型，当左表或右表中存在匹配项时，它将返回所有记录。 用符号⟗表示。

For e.g. – Select the students from Student1 whose RollNo is greater than the RollNo of Student2 table.

例如，–从Student1中选择RollNo大于Student2的RollNo表的学生。

Student1⟗_{Student1.RollNo>Student2.RollNo}Student2

_{Student1⟗Student1.RollNo> Student2.RollNo} Student2

Syntax:

句法：

    SELECT * FROM Student1 
    FULL OUTER JOIN Student2 ON Student1.RollNo > Student2.RollNo;

Output

输出量

内部联接与外部联接的摘要 (Summary of Inner Join vs Outer Join)

Inner Join	Outer Join
It returns the record that has a matching value in both tables.	It returns the record that has matching value as well as some dissimilar data from the tables.
Types of Inner Join - Condition Join, Equi Join and Natural Join.	Types of Outer Join - Left Outer Join, Right Outer Join, and Full Outer Join.
There must be at least one matching data between the two tables.	There may not be any matching data between the two tables.
It never produces NULL values.	It can produce NULL values.
It will not produce any tuple if no matching data is found between the two tables.	It will always produce at least one tuple even if no matching data is found in between the two tables.

内部联接	外连接
它返回两个表中具有匹配值的记录。	它返回具有匹配值的记录以及表中的一些不同数据。
内部联接的类型-条件联接，均等联接和自然联接。	外连接的类型-左外连接，右外连接和完全外连接。
两个表之间必须至少有一个匹配数据。	两个表之间可能没有任何匹配的数据。
它从不产生NULL值。	它可以产生NULL值。
如果在两个表之间找不到匹配的数据，它将不会产生任何元组。	即使在两个表之间找不到匹配的数据，它也将始终产生至少一个元组。

翻译自: https://www.includehelp.com/dbms/inner-join-vs-outer-join-in-dbms.aspx

db数据库连接失败dbms