Look at the database again--(6) Connect

Brief Introduction:

A connection is the basis of a multi-table query.

Because the relational database does not include pointers or other mechanisms for associating records with records, the connection becomes the only mechanism for generating cross-table data relationships.

This is why you want to use the connection.

Category:

1. Internal connection

The column values of the concatenated column are compared using the comparison operator.

Equivalent--"="

Unequal--operator other than "="

Natural-"=", but it uses a selection list to indicate the columns contained in the query result collection. and delete the repeating columns in the join table.

Demo Sample:

The two tables in the database are dbo, respectively. Staff and dbo.province.

NOTE: dbo. The primary key in the staff table is code. dbo. The Namecode in the province table is the primary key for the first table.

Dbo. Staff

Dbo. Province

watermark/2/text/ahr0cdovl2jsb2cuy3nkbi5uzxqvdtaxmda2njkzna==/font/5a6l5l2t/fontsize/400/fill/i0jbqkfcma==/ Dissolve/70/gravity/center ">

SELECT * FROM dbo. Staff,dbo. Province_staff WHERE dbo. Staff.code=dbo. Province_staff.namecode

Use aliases as

SELECT * FROM dbo. Staff as s,dbo. Province_staff as P WHERE s.code=p.namecode

Equivalent to

SELECT * FROM dbo. Staff as S INNER JOIN dbo. Province_staff as P on S.code=p.namecode

Show Results:

When connecting, it is assumed that the connected table does not include the matching records. It is possible to lose information.

2. External connection

The results found in the collection include rows that meet the join criteria. Also includes all rows in the connection

Left OUTER join (also called Left Association):

Demo Sample:

SELECT * FROM dbo. Staff as S left JOIN dbo. Province_staff as P on S.code=p.namecode

Show Results:

watermark/2/text/ahr0cdovl2jsb2cuy3nkbi5uzxqvdtaxmda2njkzna==/font/5a6l5l2t/fontsize/400/fill/i0jbqkfcma==/ Dissolve/70/gravity/center ">

Compare the two tables in the chart. The left OUTER join is the table on the left (dbo. Staff) as the benchmark. Complements the contents of the right table, assuming that there is no corresponding match, the display is null

Right outer connection

Demo Sample:

SELECT * FROM dbo. Staff as S right JOIN dbo. Province_staff as P on S.code=p.namecode

Show Results:

Full outer connection

Demo Sample:

SELECT * FROM dbo. Staff as s full OUTER JOIN dbo. Province_staff as P on S.code=p.namecode

Show Results:

The run-time is preceded by a left outer join with the Dbo.staff table. Then take the dbo. The Province_staff table makes a right outer join.

3. Cross-Connect

Demo Sample:

Select *from dbo. Staff as S cross Join dbo. Province_staff as Porder by S.code

Show Results:

First by the code of the Dbo.staff table, to join the right table of unconditional splicing. This runs sequentially so that such a record is a Cartesian product of the records of two tables.

Summary:

When using multi-table queries, it is also possible to use a large number of Where...and statements instead of connecting queries. Connection queries have strengths and weaknesses.

The advantage is that the SQL statement is readable and easy to maintain and extend.

The disadvantage is that the query efficiency is comparatively low. Of course, there are a lot of people who are proposing ways to improve the efficiency of the connection query, which is the developer's choice based on demand.

Look at the database again--(6) Connect