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

This article focuses on "connection operation in SQL". Interested friends may wish to have a look at it. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn the connection operation in SQL.

Using join operations, you can query data from two or more tables based on the logical relationship between tables.

In earlier versions of SQL, the join method was to give the table name of the join operation in the from clause, and in the where clause

The connection conditions are given.

For example, generate transcripts for courses from students, courses, and transcripts. The from clause lists the value of the join operation

Three tables, and the where clause gives the join conditions. The join condition and selection are included in the conditional expression of the where clause

Conditions.

Example 1 generates the transcript of the database course from the students, courses and grades. The SQL statement is:

Select name, course name, score

From student S, course C, grade G

Where S. Student number = G. Student number AND G. Course number = C. Course number AND C. Course name = 'database'

SQL-92 provides a wealth of connection operations, including: inner connection, conditional connection, natural connection, left outside.

Join, right outer join, full join, and add the method to define the connection condition in the from clause. From in SQL-92

The grammatical format of the clause connection:

From [on ()]

Therefore, the above SQL statement that produces the student database course transcript can be written as follows:

Select name, course name, score

From students'S join score G on (S. Student number = G. Student number)

Join course C on (G. Course number = C. Course number)

Where C. Course name = 'database'

This method not only simplifies the expression of connection conditions, but also improves the query efficiency.

The logical order in which the SQL system processes is:

1. Join conditions in the from clause

2. Join condition and selection condition in where clause

3. Filter conditions in having clause

Therefore, when using a system that supports the SQL-92 standard, it is recommended that you use the from clause to define the connection condition.

The relation R and S perform the connection operation, and the connection condition is P. If only R and S are matched on P in the result of connection.

This connection belongs to the inner join (inner join) The method of expressing inner joins with a from clause:

From R [inner] join S [on ()] where inner can be omitted.

Natural connection is a special case of equivalent connection.

Let R and S carry out the connection operation, and the connection condition is P. If P is the connection attribute in R and S

An equivalent comparison is called an equivalent connection.

If R and S make an equivalent connection, and the connection attribute names of R and S are the same, it is called natural connection.

The realization method of natural join is to eliminate duplicate columns with select clause on the basis of inner join.

The Cartesian product of R and S is called cross-connection. The realization method of cross connection: in the from sub-

The type of cross connection is defined in the sentence: from R cross join S.

The relation R is connected with S, and the connection condition is P. If the result of the join operation is connected on P except R and S

In addition to the result, there are also tuples that do not match in the join operation, including the left relation R, which corresponds to the attribute assignment of S

Null value, this connection is called the left outer connection. Representation of left outer connection: from R left outer join S on

()

The relation R is connected with S, and the connection condition is P. If in the result of the connection operation, except R and S are connected on P

In addition to the result, there are also tuples that do not match in the join operation, including the right relation S, and the attribute corresponding to R is assigned a null value.

This connection is called the right outer connection The representation of the right outer connection: from R right outer join S on ()

Full external connection is a combined application of left outer connection and right outer connection. Representation of full external connection: from R full

Outer join S on ()

Self-join: join operations can use aliases to join a table itself. In essence, this self-connection method is similar to that of

The join operations of the two tables are exactly similar.

I. Internal connection

The relation R and S perform the connection operation, and the connection condition is P. If the result of the join operation contains only R and S matching on P

This connection belongs to the inner join. Use the from clause to represent the inner join clause:

From R [inner] join S [on ()]

Inner can be omitted. Suppose there are two tables for mentors and graduate students.

Example 1 inquire about graduate students and their mentors. This query involves two tables for teachers and graduate students, and the join conditions are:

. The SQL statement is:

Select * from graduate An inner join teacher B on A. Tutor number = B. Teacher number

You can also write the equivalent SQL statement:

Select *

From graduate A, teacher B

Where A. Tutor number = B. Teacher number

Description: this is an example of an internal join, and the query result contains only 4 rows that meet the join conditions.

II. Natural connection

Let R and S carry out the connection operation, and the connection condition is P. If P is the equivalent comparison of connection attributes in R and S

It's called an equivalent connection. If R and S make an equivalent connection, and the connection attribute names of R and S are the same, it is called natural connection. Natural company

Then there is a special case of equivalent connection. The realization method of natural join is to eliminate repetition with select clause on the basis of inner connection.

Column.

Suppose there are three tables of students, courses and grades. Example 1: output the number according to the information of students, courses and grades.

The transcript of the "database" course, including names and scores. The SQL sentence is:

Select name, score

From student A join score B on (A. Student number = B. ) join course C on (B. Course number = C. Course number)

Where C. Course name = 'database'

Equivalent SQL statement:

Select name, score

From student A, grade B, course C

Where A. Student number = B. Student number AND B. Course number = C. Course number AND C. Course name = 'database'

This is an instance of a natural join, and the query result contains a row that meets the join condition, and there are no duplicate columns in the query result.

III. Cross connection

The Cartesian product with relations R and S between R and S is called cross connection. In SQL, the way to achieve cross-connect is in from

The type of cross connection is defined in the clause: from R cross join S

Example 1 the Cartesian product of the student and the course.

Select *

Cross join course for from students

Equivalent SQL statement:

Select *

From students, Cour

IV. Left outer connection

The relation R is connected with S, and the connection condition is P. If the result of the connection operation, except for the result of the connection between R and S on P,

It also includes the tuples that do not match in the join operation, including the left relation R, and the attribute corresponding to S is assigned a null value.

It is called the left outer connection (left outer join).

The SQL clause that implements the left outer connection: from R left outer join S on ()

Example 1: inquire about the selection of courses by students, including those who do not take courses. The SQL statement is:

Select A. Student number, A. Name, B. Course number, B. Score

From students'A left outer join scores B on (A. Student number = B. Student number)

Example 2: inquire about the situation of teachers guiding graduate students, including teachers who do not guide graduate students. The SQL statement is:

Select *

From teacher A left outer join graduate student B on (A. Teacher number = B. Tutor number)

These are two instances of a left outer join. The query result not only satisfies the join condition tuple, but also contains the left relation is not satisfied.

Join the tuples of the condition, and their corresponding right-hand attributes are all populated with NULL.

5. Right outer connection

The relation R is connected with S, and the connection condition is P. If the result of the connection operation, except for the result of the connection between R and S on P,

It also includes the tuples that do not match in the join operation, including the relation S on the right, and the attribute corresponding to R is assigned a null value.

It is called the right outer connection (right outer join).

The SQL clause that implements the right outer connection: from R right outer join S on ()

Example 1: inquire about the electives of the courses offered, including those that do not have any students. The SQL statement is:

Select *

From score A right outer join course B on (A. Course number = B. Course number)

Example 2: inquire about the course offered and the learning status of the students (student number, course name and score), including that there are no students taking electives.

Sam's class. The SQL statement is:

Select student number, course name, score

From score A right outer join course B on (A. Course number = B. Course number)

These are two instances of a right outer join. In addition to the tuples that satisfy the join conditions, the query results also include that the relationship on the right does not satisfy the join.

Condition, and its corresponding left properties are all populated with NULL.

VI. Full external connection

Full external connection (full outer join) is a combined application of left outer connection and right outer connection. The SQL clause that implements the full outer connection:

From R full outer join S on ()

Example 1: inquire about graduate students and teachers, including graduate students without mentors and teachers without mentors.

The SQL statement is:

Select *

From graduate student A full outer join teacher B on (A. Tutor number = B. Teacher number)

This is an instance of an all-outer connection. In addition to the tuples that satisfy the join conditions, the query result also contains two relationships that are not satisfied.

Join the tuples of the condition, and the properties corresponding to these tuples are all populated with NULL.

7. Self-connection

The join operation can use the alias method to implement a join of the table itself. In essence, this self-join method is similar to the

The connection operation is completely similar. Suppose there is a teaching schedule, in which the first column is a prerequisite for this course. For example, learn first

"data structure" course, and then study the "database" course. Example 1: inquire about the prerequisites for each course. For example, C5

The prerequisite course is C6. Query is implemented with self-join, and the connection conditions are as follows:

From teaching progress A join teaching progress B on (A. First repair = B. Course number)

Select A. Course number, B. Repair first

From Teaching Progress A join Teaching Progress B

On (A. First repair = B. Course number)

At this point, I believe you have a deeper understanding of the "connection operation in SQL". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue 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.