The types of SQL Server database connection queries and their application

Developer on Alibaba Coud: Build your first app with APIs, SDKs, and tutorials on the Alibaba Cloud. Read more ＞

In database development, through the implementation of a single table, sometimes we need to combine queries to find the recordset we need, at which point we will use the connection query.

The connection query mainly includes the following aspects:

INNER JOIN

The inner joins are generally the most commonly used, also called Natural joins, which compare the values of the columns to be joined by comparison operators. It connects multiple tables through a keyword (INNER join or join). We illustrate the problem by creating two tables:

StudentID Studentname Studentage

----------- -------------------------------------------------- -----------

1 Sheets 325

2 Lee 426

3 King 527

4 Zhao 628

5 John Doe 27

Above is the table student, holds the student basic information.

Borrowbookid Borrowbookname StudentID Borrowbookpublish

--------------------------------------------------------------------------------------------------------------- -------

1 Marxist Political Economy 1 electronics publishing house

2 Introduction to Mao Zedong Thought 2 higher Education press

3 Deng Xiaoping Theory 3 people posts and telecommunications press

4 college students ' ideological and moral training 4 China Railway Publishing house

5 C language Programming NULL higher Education publishing house

Above is the table Borrowbook, holds the student to borrow the book.

The above two tables are associated with StudentID, where the inline statement is executed first:

Select Student.studentname,student.studentage,borrowbook.borrowbookname,borrowbook.borrowbookpublish

From Student

Inner Join Borrowbook

On student.studentid = Borrowbook.studentid

The above statement can also be written as

Select Student.studentname,student.studentage,borrowbook.borrowbookname,borrowbook.borrowbookpublish

From Student,borrowbook

WHERE Student.studentid = Borrowbook.studentid

Where the Inner join is the default connection for SQL Server and can be abbreviated to join. Specify a specific table to join after the join. On the following specifies the condition of the connection.

The results of the operation are as follows:

Tudentname studentage Borrowbookname Borrowbookpublish

--------------------------------------------------------------------------------

Zhang 325, Marxist political Economy, electronic industry publishing house

Li 426 Introduction to Mao Zedong Thought higher Education press

Wang 527 Deng Xiaoping theory people posts and telecommunications press

Zhao 628 College students ' ideological and moral cultivation China Railway publishing house

(The number of rows affected is 4 rows)

Based on the results of the query:

If more than one table is to be connected, there must be a relationship between the primary key and the foreign key between the tables. So you need to list the relationships of these keys to get the result of the table join. In the example above, StudentID is the primary key of the student table, StudentID is the foreign key to the Borrowbook table, and the join condition of the two tables is Student.studentid = Borrowbook.studentid, the query results can be learned that the INNER JOIN query only query the primary key StudentID in another table exists, like the student table in the fifth record, because in the Borrowbook table StudentID does not exist, like the Borrowbook table The fifth record StudentID is null, and the corresponding student table has no records, so it is not displayed. So the inner join is to match each column in the participating datasheet with the columns of the other datasheet, form a temporary data table, and select the records that meet the same data items from the temporary data table.

The INNER JOIN query operation lists the rows of data that match the join criteria, which compares the column values of the connected columns using comparison operators. The inner connection is divided into three kinds:

1) Equivalent connection: Use the equals sign (=) operator in the join condition to compare the column values of the connected columns, whose query results list all the columns in the connected table, including the repeating columns.

2 Unequal connections: The column values of the connected columns are compared using comparison operators other than the equals operator in the join condition. These operators include >, > =, <=, <,!>,! < and <>.

3) Natural connection: Use the Equals (=) operator in the join condition to compare the column values of the connected columns, but it uses a select list to indicate which columns are included in the query result collection, and deletes duplicate columns from the attached table.

For example, for a connection statement with no equivalence:

Select Student.studentname,student.studentage,borrowbook.borrowbookname,borrowbook.borrowbookpublish

From Student

Inner Join Borrowbook

On Student.studentid <> Borrowbook.studentid

Studentname studentage Borrowbookname Borrowbookpublish

----------------------------------------------------------------------------------

Lee 426 Marxist Political Economics, electronic industry publishing house

Wang 527, Marxist political economics, electronic industry publishing house

Zhao 628, Marxist political economics, electronic industry publishing house

John Doe 27 Marxist Political Economy, electronic industry publishing house

Zhang 325 Introduction to Mao Zedong Thought higher Education press

Wang 527 Introduction to Mao Zedong Thought higher Education press

Zhao 628 Introduction to Mao Zedong Thought higher Education press

John Doe 27 Overview of Mao Zedong Thought higher Education press

Zhang 325 Deng Xiaoping theory people posts and telecommunications press

Li 426 Deng Xiaoping theory people posts and telecommunications press

Zhao 628 Deng Xiaoping theory people posts and telecommunications press

John Doe 27 Deng Xiaoping theory people posts and telecommunications press

Zhang 325 college students ' ideological and moral cultivation China Railway publishing house

Li 426 College students ' ideological and moral cultivation China Railway publishing house

Wang 527 college students ' ideological and moral cultivation China Railway publishing house

27 college students ' ideological and moral cultivation China Railway publishing house

It will be the two tables corresponding to the unequal records to the query.

Outer Joins

The outer joins mainly include left join, right connection and complete outer connection.

1) left-side connection: A/Outer Join

The result set of the left connection includes all the rows of the left table specified in the outer clause, not just the rows that the join columns match. If a row in the left table does not have a matching row in the right table, all picklist columns in the right table in the associated result set row are null (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 results of the operation are as follows:

Studentname studentage Borrowbookname Borrowbookpublish

---------------------------------------------------------------------------------

Zhang 325, Marxist political Economy, electronic industry publishing house

Li 426 Introduction to Mao Zedong Thought higher Education press

Wang 527 Deng Xiaoping theory people posts and telecommunications press

Zhao 628 College students ' ideological and moral cultivation China Railway publishing house

anonymous NULL NULL

(The number of rows affected is 5 rows)

What can be seen is that its query results are left table student, student corresponding studentid in the right table Borrowbook if not exist, will be replaced with null values.

2) Right connection: Right-hand join or Outer join

Right and left joins, instead, it returns all rows of the right table. If a row in the right table does not have a matching row in the left table, a null value (NULL) is returned for left table.

We 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 results of the operation are as follows:

Studentname studentage Borrowbookname Borrowbookpublish

--------------------------------------------------------------------------------------------------------------- -------

Zhang 325, Marxist political Economy, electronic industry publishing house

Li 426 Introduction to Mao Zedong Thought higher Education press

Wang 527 Deng Xiaoping theory people posts and telecommunications press

Zhao 628 College students ' ideological and moral cultivation China Railway publishing house

NULL NULL C language Programming higher education publishing house

(The number of rows affected is 5 rows)

What you can see is that the result of its query is the borrowbook of the right table, the borrowbook corresponding studentid in the left table student if it does not exist, it will be replaced with a null value.

3 Complete outer join: Full JOIN or fully Outer join

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

We 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 publishing house

Zhang 325, Marxist political Economy, electronic industry publishing house

Li 426 Introduction to Mao Zedong Thought higher Education press

Wang 527 Deng Xiaoping theory people posts and telecommunications press

Zhao 628 College students ' ideological and moral cultivation China Railway publishing house

anonymous NULL NULL

(The number of rows affected is 6 rows)

As you can see, the results of its query, in addition to the relative should exactly match the records found out, but also the left and right connection two kinds of cases are included, the corresponding values are replaced with null values.

Cross Connect

Cross joins (CROSS join) are queries that do not have the W H e R e clause. In mathematics, it is the Cartesian product of a table. That is, it queries out the number of records of the behavior of two tables, the corresponding record is a * table B.

We look at the corresponding SQL statement

Select Student.studentname,student.studentage,borrowbook.borrowbookname,borrowbook.borrowbookpublish

From Student Cross Join Borrowbook