Database connection query types and applications

 

In database development, achieve performance through single table, and sometimes need to combine a query to find the set of records that we need, this time we will use the connection to query.

The join queries include the following aspects:

Within the connection

Within the connection is generally the most commonly used, also known as the natural connection, compared with the comparison operators to join column values ??join. It is multi-table keyword (INNER JOIN or JOIN) to connect.Through the establishment of two tables to illustrate the problem:

StudentID StudentName StudentAge

————————————————– ———————-

Joe Smith 25

2 John Doe 26

3 Wangwu 27

4 Zhao Six 28

5 Anonymous 27

 

The above table Student storage students basic information.

BorrowBookID BorrowBookName StudentID BorrowBookPublish

————————————————– ————————————————– ——————

A Marxist political economy Electronic Industry Press

2 Mao Zedong Thought Higher Education Press

3 of Deng Xiaoping Theory 3 People’s Posts and Telecommunications Press

4 College Students’ Ideological and Moral Cultivation 4 China Railway Publishing House

The 5 C language programming NULL Higher Education Press

The above table BorrowBook ??storage students borrowed books.

More than two tables are associated StudentID to perform inline with statement here:

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

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

FROM Student, BorrowBook

WHERE Student.StudentID = BorrowBook.StudentID

Which, Inner Join is the SQL Server default connection may be abbreviated as JOIN. Specify the table behind Join for connection. On the back of the connection conditions specified.

The result of the operation is as follows:

tudentName StudentAge BorrowBookName BorrowBookPublish

————————————————– ——————————

Joe Smith 25 Marxist political economy Electronic Industry Press

John Doe 26 Mao Zedong Thought Higher Education Press

The king five 27 Deng Xiaoping Theory People’s Posts and Telecommunications Press

Zhao six 28 College Students’ Ideological and Moral Cultivation China Railway Publishing House

(The number of rows affected by line 4)

The analysis is carried out according to the results of the query to:

If more than one table to do connection must exist between those tables with primary key and foreign key relationship. So these key relationships need to be listed, you can come to the table join. In the above example, the StudentID Student table’s primary key StudentID is BorrowBook ??table foreign key, the two tables of the join condition is Student.StudentID = BorrowBook.StudentID comparison results can be learned within a join query only query the presence of the primary key StudentID Zhang table records like the fifth record in the Student table StudentID not exist in BorrowBook ??table, like the fifth record the BorrowBook ??table StudentID as Null, there is no record corresponding to the Student table, so will not be displayed. Therefore, within the connection is to participate in the data table for each column and the other column of the data table to match, temporary data table is formed, and meet the data item is equal to the record from the temporary data table selected.

Inner join query operation lists the rows that match the join condition, use the comparison operator is connected to the column value. Within the connection in three:

1) equivalent connection: use the equal sign (=) operator is connected to the value of the column in the join condition, its results be connected all the columns in the table are listed, including the duplicate columns.

Ranging from 2) connection: use comparison operators other than the equal operator is connected to the value of the column in the join condition. These operators include>,> =, <=, <,>, <and <>.

3) natural connection: using the equals (=) operator is connected to the value of the column in the join condition, but it is a select list that included in the query result set columns, and delete the duplicate columns in the connection table.

For example, with unequal connect statement:

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

From Student

Inner Join BorrowBook

On Student.StudentID <> BorrowBook.StudentID

StudentName StudentAge BorrowBookName BorrowBookPublish

————————————————– ——————————–

John Doe 26 Marxist political economy Electronic Industry Press

Wang 50 27 Marxist political economy Electronic Industry Press

Zhao six 28 Marxist political economy Electronic Industry Press

Anonymous 27 Marxist political economy Electronic Industry Press

Joe Smith 25 Mao Zedong Thought Higher Education Press

Wang five 27 Mao Zedong Thought Higher Education Press

Zhao six 28 Mao Zedong Thought Higher Education Press

Anonymous 27 Mao Zedong Thought Higher Education Press

Joe Smith 25 Deng Xiaoping Theory People’s Posts and Telecommunications Press

John Doe 26 Deng Xiaoping Theory and the People’s Posts and Telecommunications Press

Zhao six 28 Deng Xiaoping Theory People’s Posts and Telecommunications Press

Anonymous 27 Deng Xiaoping Theory People’s Posts and Telecommunications Press

Joe Smith 25 college students ideological and moral cultivation China Railway Publishing House

John Doe 26 college students ideological and moral cultivation China Railway Publishing House

The king five 27 college students ideological and moral cultivation China Railway Publishing House

Anonymous 27 ideological and moral cultivation China Railway Publishing House

It will be two tables corresponding records are not equal to the query.

Outer join

Outer join to connect the left and right and complete external connection.

1) left join: Left JOIN or LEFT OUTER JOIN

Left join result set includes all the rows of the left table specified in the LEFT OUTER clause, not just the matching join column row. If there is no match in the left table in a row in the table on the right line, a list of all the options in the associated row of the result set table on the right columns are null (Null).

We see 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

————————————————– ——————————-

Joe Smith 25 Marxist political economy Electronic Industry Press

John Doe 26 Mao Zedong Thought Higher Education Press

Wang 5 27 Deng Xiaoping Theory People’s Posts and Telecommunications Press

Zhao six 28 College Students’ Ideological and Moral Cultivation China Railway Publishing House

Anonymous 27 NULL NULL

(The number of rows affected line 5)

Can see is that it is the result of the query is left table Student Student corresponding StudentID the right table BorrowBook ??if it does not exist, it will use a NULL value instead.

2) the right connections: Right Join or Right Outer Join

Right and left connected to the contrary, it will return all the rows of the table on the right. If the right table in a row left the table without matching rows, the left table will return a null value (Null).

We see 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

————————————————– ————————————————– ——————

Joe Smith 25 Marxist political economy Electronic Industry Press

John Doe 26 Mao Zedong Thought Higher Education Press

Wang 5 27 Deng Xiaoping Theory People’s Posts and Telecommunications Press

Zhao six 28 College Students’ Ideological and Moral Cultivation China Railway Publishing House

Null null C language program design Higher Education Press

(The number of rows affected line 5)

You can see, it is the result of the query is based on the right table BorrowBook ??main corresponding StudentID BorrowBook ??if it does not exist in the left table Student will use a NULL value instead.

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

Full outer join returns the left and all the rows in the table on the right. When a bank does not match rows in another table, another table select list column contains a null value. If there is a match between the table line, the entire result set row contains data values ??of the base table.

We see the corresponding SQL statement

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

From Student

Full OUTER JOIN BorrowBook ??This

On Student.StudentID = BorrowBook.StudentID

The results are as follows:

StudentName StudentAge BorrowBookName BorrowBookPublish ———————————————- ————————————————– ———————-

Null null C language program design Higher Education Press

Joe Smith 25 Marxist political economy Electronic Industry Press

John Doe 26 Mao Zedong Thought Higher Education Press

Wang 5 27 Deng Xiaoping Theory People’s Posts and Telecommunications Press

Zhao six 28 College Students’ Ideological and Moral Cultivation China Railway Publishing House

Anonymous 27 NULL NULL

(The number of rows affected line 6)

It can be seen, the query results in addition to the corresponding exact match found out later, but also to connect the left and right both cases, the corresponding value NULL value instead.

Cross-connect

Cross-connect (CROSS JOIN) means without the WHERE clause of the query. In mathematics, the Cartesian product of the table. Is the behavior of the number of records it queries the product of the two tables, the corresponding record is Table B Table A *.

We see 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

————————————————– ——————————-

Joe Smith 25 Marxist political economy Electronic Industry Press

John Doe 26 Marxist political economy Electronic Industry Press

Wang 50 27 Marxist political economy Electronic Industry Press

Zhao six 28 Marxist political economy Electronic Industry Press

Anonymous 27 Marxist political economy Electronic Industry Press

Joe Smith 25 Mao Zedong Thought Higher Education Press

John Doe 26 Mao Zedong Thought Higher Education Press

Wang five 27 Mao Zedong Thought Higher Education Press

Zhao six 28 Mao Zedong Thought Higher Education Press

Anonymous 27 Mao Zedong Thought Higher Education Press

Joe Smith 25 Deng Xiaoping Theory People’s Posts and Telecommunications Press

John Doe 26 Deng Xiaoping Theory and the People’s Posts and Telecommunications Press

Wang 5 27 Deng Xiaoping Theory People’s Posts and Telecommunications Press

Zhao six 28 Deng Xiaoping Theory People’s Posts and Telecommunications Press

Anonymous 27 Deng Xiaoping Theory People’s Posts and Telecommunications Press

Joe Smith 25 college students ideological and moral cultivation China Railway Publishing House

John Doe 26 college students ideological and moral cultivation China Railway Publishing House

The king five 27 college students ideological and moral cultivation China Railway Publishing House

Zhao six 28 College Students’ Ideological and Moral Cultivation China Railway Publishing House

Anonymous 27 ideological and moral cultivation China Railway Publishing House

Joe Smith 25 C Programming Language Higher Education Press

John Doe 26 C Programming Language Higher Education Press

Wang five 27 C Programming Language Higher Education Press

Zhao six 28 C Programming Language Higher Education Press

Anonymous 27 C Programming Language, Higher Education Press

(The number of rows affected 25 lines)

Can see, it the table Student each row and BorrowBook ??of the associate each record, the number of records returned 5 * 5 = 25 lines, Cartesian product, it executes the statement is also equivalent at

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

From Student, BorrowBook