Shulou(Shulou.com)05/31 Report--

This article mainly introduces how to connect SQL query 2 external connection, has a certain reference value, interested friends can refer to, I hope you can learn a lot after reading this article, the following let Xiaobian take you to understand.

External connection mainly includes left connection, right connection and complete external connection.

1) left connection: Left Join or Left Outer Join

The result set of the left join includes all rows of the left table specified in the LEFT OUTER clause, not just the rows matched by the join column. If a row of the left table does not match in the right table, all select list columns of the right table in the associated result set row are Null.

Let's look at the corresponding SQL statement:

Select Student.StudentName,Student.StudentAge,BorrowBook.BorrowBookName,BorrowBook.BorrowBookPublish

From Student

Left JOIN BorrowBook

On Student.StudentID = BorrowBook.StudentID

The result of the operation is as follows:

StudentName StudentAge BorrowBookName BorrowBookPublish

Zhang San 25 Marxist political economy Electronic Industry Press

An introduction to Li Si's 26 thoughts higher Education Press

Wang Wu 27 Deng Xiaoping Theory people's posts and Telecommunications Publishing House

Zhao Liu28 College students' ideological and moral cultivation China Railway Publishing House

John Doe 27 NULL NULL

(the number of rows affected is 5)

What you can see is that the result of its query is mainly the left table Student, and the corresponding StudentID of Student is in the right table BorrowBook. If it does not exist, it will be replaced by null value.

2) right connection: Right Join or Right Outer Join

The right join is the opposite of the left join, which returns all rows of the right table. If a row of the right table does not match in the left table, a null value (Null) is returned for the left table.

Let's look at the corresponding SQL statement.

Select Student.StudentName,Student.StudentAge,BorrowBook.BorrowBookName,BorrowBook.BorrowBookPublish

From Student

Right JOIN BorrowBook

On Student.StudentID = BorrowBook.StudentID

The result of the operation is as follows:

StudentName StudentAge BorrowBookName BorrowBookPublish

-

Zhang San 25 Marxist political economy Electronic Industry Press

An introduction to Li Si's 26 thoughts higher Education Press

Wang Wu 27 Deng Xiaoping Theory people's posts and Telecommunications Publishing House

Zhao Liu28 College students' ideological and moral cultivation China Railway Publishing House

NULL NULL C language programming higher Education Press

(the number of rows affected is 5)

What you can see is that the result of its query is mainly the right table BorrowBook, and the corresponding StudentID of BorrowBook is replaced by null value in the left table Student if it does not exist.

3) full outer join: Full Join or Full Outer Join

The full outer join returns all rows in the left and right tables. When a row does not match a row in another table, the select list column of the other table contains null values. If there are matching rows between tables, the entire result set row contains the data values of the base table.

Let's look at the corresponding SQL statement.

Select Student.StudentName,Student.StudentAge,BorrowBook.BorrowBookName,BorrowBook.BorrowBookPublish

From Student

FULL OUTER JOIN BorrowBook

On Student.StudentID = BorrowBook.StudentID

The results are as follows:

StudentName StudentAge BorrowBookName BorrowBookPublish-

NULL NULL C language programming higher Education Press

Zhang San 25 Marxist political economy Electronic Industry Press

An introduction to Li Si's 26 thoughts higher Education Press

Wang Wu 27 Deng Xiaoping Theory people's posts and Telecommunications Publishing House

Zhao Liu28 College students' ideological and moral cultivation China Railway Publishing House

John Doe 27 NULL NULL

(the number of rows affected is 6)

What you can see is that the query result will not only find out the corresponding matching records, but also include both left join and right join, and the corresponding value will be replaced by null value.

Cross connection

A CROSS JOIN is a query without a W H E R E clause. In mathematics, it is the Cartesian product of a table. That is, the number of records it queries behaves as the product of two tables, and the corresponding record is table A * Table B.

Let's look at the corresponding SQL statement.

Select Student.StudentName,Student.StudentAge,BorrowBook.BorrowBookName,BorrowBook.BorrowBookPublish

From Student Cross Join BorrowBook

The result of the operation is as follows:

StudentName StudentAge BorrowBookName BorrowBookPublish

Zhang San 25 Marxist political economy Electronic Industry Press

Li Si 26 Marxist political economy Electronic Industry Press

Wang Wu 27 Marxist political economy Electronic Industry Press

Zhao Liu28 Marxist political economy Electronic Industry Press

John Doe 27 Marxist political economy Electronic Industry Press

An introduction to Zhang San's 25 thoughts higher Education Press

An introduction to Li Si's 26 thoughts higher Education Press

An introduction to Wang Wu's 27 thoughts higher Education Press

An introduction to Zhao Liu 28 thoughts higher Education Press

An introduction to the thoughts of John Doe 27 * higher Education Press

Zhang San 25 Deng Xiaoping Theory people's posts and Telecommunications Publishing House

Li Si 26 Deng Xiaoping Theory people's posts and Telecommunications Publishing House

Wang Wu 27 Deng Xiaoping Theory people's posts and Telecommunications Publishing House

Zhao Liu 28 Deng Xiaoping Theory people's posts and Telecommunications Publishing House

John Doe 27 Deng Xiaoping Theory people's posts and Telecommunications Press

Zhang San 25 ideological and moral cultivation of college students China Railway Publishing House

Li Si 26 College students' ideological and moral cultivation China Railway Publishing House

Wang Wu 27 College students' ideological and moral cultivation China Railway Publishing House

Zhao Liu28 College students' ideological and moral cultivation China Railway Publishing House

Anonymous 27 College students' ideological and moral cultivation China Railway Publishing House

Zhang San 25C language programming higher Education Press

Li Si 26 C language programming higher Education Press

Wang Wu 27 C language programming higher Education Press

Zhao Liu 28 C language programming higher Education Press

John Doe 27 C language programming higher Education Press

(the number of rows affected is 25)

What you can see is that it associates every row in the table Student with every record in the BorrowBook, and the number of records returned is 5'5'25 rows, that is, the Cartesian product, and the statement it executes is equivalent to

Select Student.StudentName,Student.StudentAge,BorrowBook.BorrowBookName,BorrowBook.BorrowBookPublish

From Student,BorrowBook

Thank you for reading this article carefully. I hope the article "how to connect SQL query 2 external links" shared by the editor will be helpful to everyone. At the same time, I also hope that you will support us and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.