sql基础教程示例程序下载_SQL通过查询和示例加入教程

sql基础教程示例程序下载

A SQL Join statement is used to join rows as well as data from two or more than two tables. This combination is based on a common field between them.

SQL Join语句用于连接行以及来自两个或两个以上表的数据。 此组合基于它们之间的公共字段。

Different types of Joins are as follows:

联接的不同类型如下：

1. INNER JOIN

   内部联接

2. OUTER JOIN

   外连接

   1. LEFT JOIN
   2. RIGHT JOIN
   3. FULL JOIN

Tables, which we are going to use in this tutorial

表格，我们将在本教程中使用

1)SQL内部联接 (1) SQL INNER JOIN)

INNER JOIN joins both the tables. This selects all rows from both the tables. This keyword will combine columns values of both the tables based on join predicate. Join predicate normally we can call the same column data in both tables like above both tables are having ‘Roll No.’ column in same. This will be join predicate. Join will continue as long as the join predicate satisfies.

INNER JOIN联接两个表。 这将从两个表中选择所有行。 此关键字将基于连接谓词组合两个表的列值。 联接谓词通常可以在两个表中调用相同的列数据，就像上面两个表都具有“ Roll No.”一样。 同一列。 这将是连接谓词。 只要连接谓词满足，连接就会继续。

Syntax:

句法：

    SELECT table1.column1, table1.column2, table2.column1 
    FROM table1 
    INNER JOIN table2
    ON table1.common_column = table2.common_column;

table1: Student_table
table2: Course_table
join predicate: Roll No.

table1 ：Student_table
table2 ：Course_table
加入谓词 ：卷号

Code to Join

加入代码

SELECT Course_table.COURSE_ID, Student_table.NAME, Student_table.AGE 
FROM Student_table
INNER JOIN Student_table
ON Student_table.ROLL_NO = Course_table.ROLL_NO;

Output:

输出：

2)SQL外部联接 (2) SQL OUTER JOIN)

This is of three types.

这是三种类型。

2-A)左外连接 (2 - A) Left Outer Join)

LEFT OUTER JOIN performs a join starting with the first (left-most) table and then any matching second (right-most) table records. This join gives a result set of all the rows of the left table and matching rows from the right table. If there are no matching rows on right side, this gives null. This is also called left join.

LEFT OUTER JOIN从第一个(最左侧)表开始，然后是任何匹配的第二个(最右侧)表记录开始执行连接。 该联接给出了左表的所有行和右表的匹配行的结果集。 如果右侧没有匹配的行，则为null。 这也称为左连接 。

Syntax:

句法：

    SELECT table1.column1, table1.column2, table2.column1,
    FROM table1 
    LEFT JOIN table2
    ON table1.matching_column = table2.matching_column;

table1: Student_table
table2: Course_table
join predicate: Roll No.

table1 ：Student_table
table2 ：Course_table
加入谓词 ：卷号

Code to Join

加入代码

SELECT Student_table.NAME, Course_table.COURSE_ID 
FROM Student_table
LEFT JOIN Course_table 
ON Course_table.ROLL_NO = Student_table.ROLL_NO;

Output

输出量

2-B)右外连接 (2 - B) Right Outer Join)

Right Outer JOIN performs a join starting with the second/right table and then any matching second from first/left table. This join gives a result set of all the rows of the second/right table and matching rows from the first/left table. If there are no matching rows on left first/left side, this gives null. This is also called right join.

Right Outer JOIN从第二个/右表开始执行联接，然后从第一个/左表开始执行任何匹配的秒。 此联接给出第二个/右表的所有行以及第一个/左表的匹配行的结果集。 如果左侧/左侧没有匹配的行，则为null。 这也称为右连接 。

Syntax:

句法：

    SELECT table1.column1, table1.column2, table2.column1,
    FROM table1 
    RIGHT JOIN table2
    ON table1.matching_column = table2.matching_column;

table1: Student_table
table2: Course_table
join predicate: Roll No.

table1 ：Student_table
table2 ：Course_table
加入谓词 ：卷号

Code to Join

加入代码

SELECT Student_table.NAME, Course_table.COURSE_ID 
FROM Student_table
RIGHT JOIN Course_table 
ON Course_table.ROLL_NO = Student_table.ROLL_NO;

Output

输出量

2-C)完全加入 (2 - C) Full Join)

FULL JOIN joins both LEFT JOIN and RIGHT JOIN. The result will contain all the records from both the tables. If there is no matching record, there will be NULL values.

FULL JOIN同时加入LEFT JOIN和RIGHT JOIN。 结果将包含两个表中的所有记录。 如果没有匹配的记录，则将有NULL值。

Syntax:

句法：

    SELECT table1.column1, table1.column2, table2.column1,
    FROM table1 
    FULL JOIN table2
    ON table1.matching_column = table2.matching_column;

table1: Student_table
table2: Course_table
join predicate: Roll No.

table1 ：Student_table
table2 ：Course_table
加入谓词 ：卷号

Code to Join

加入代码

SELECT Student_table.NAME, Course_table.COURSE_ID 
FROM Student_table
FULL JOIN Course_table 
ON Course_table.ROLL_NO = Student_table.ROLL_NO;

Output

输出量

翻译自: https://www.includehelp.com/dbms/sql-join-tutorial-with-queries-and-examples.aspx

sql基础教程示例程序下载