SQL Server connection instance

SQL Server connections can be roughly divided into three types: internal connections, external connections, and cross connections.

I. Inner join)

Use the comparison operator to compare data in a certain column between tables and list the data rows that match the connection conditions in these tables. The comparison method is different.Internal connections are divided into three types:

1. Equijoin
2. Natural connection
3. Three types of connections are not supported.

 

2. External join)

Different from internal connections, external connections not only list the rows that match the connection conditions, but also list the left table (when the left Outer Join is performed) and the right table (when the right outer join is performed) or all data rows that meet the search criteria in two tables (when the table is fully connected. External ConnectionThere are three types:

1. Left Outer Join (left Outer Join or left join)
2. Right Outer Join (right Outer Join or right join)
3. Full outer join (full outer join or full join)

 

Iii. Cross join)

Without the WHERE clause, it returns the Cartesian product of all data rows in the join table, the number of rows in the result set is equal to the number of rows that meet the query conditions in the first table multiplied by the number of rows that meet the query conditions in the second table.

 

Some connected instances are as follows:

Assume that the database has the following Relational Tables:

The first relational table is the voting master table votemaster, and the second table is the voter information table voter, which is used to record the IP address and voting time of the voter.

Left-side join, that is, the table in which the results of the Union query are taken as the standard. If the left-side relational table is used as the criterion, the left-side join is used as the criterion. If the right-side relational table is used as the criterion, the right join is used.

1: for example, right join or right outer join:

Take the voter table on the right as the standard. The record in the left table (votemaster) is displayed only when its ID exists in the right table (voter). For example, the records with IDs 3, 4, 5, and 6 on the left are not shown in the right table!

2: for example, left join or left Outer Join

It can be seen that the ID on the right is displayed only when it exists on the left. If there is no corresponding data on the right, use null instead!

3: Full join or full outer joinFor the data in both tables, the effect is the same as that shown above!

4: inner join or join;It is the record that the returned field ID exists in both the table votemaster and voter.

5: cross join (full join) cross join without the where Condition
A cross join without a where clause will generate the Cartesian product of the table involved in the join. The number of rows in the first table multiplied by the number of rows in the second table is equal to the size of the Cartesian result set. (Table 1 and Table 2 generate 6*3 = 18 Records)

EquivalentSelectVM. ID, VM. votetitle, vt. IPFromVotemasterAsVM, voterAsVt

Note: The results displayed by full join are generally meaningless and are rarely used in practice.

6: Self-connection. In fact, the relational table is connected to itself.
See the following table:

This is a department table that stores departments and their upper-level departments, but all of them are placed in the same table. We assume that you need to use SQL to query the departments and their upper-level departments! What should I do?

Of course, the same can be achieved without self-connection:

We have achieved the expected goal! In this query, A subquery is used to query the names of upper-level departments.

 

if self-join is used, the structure is much clearer.

have you done the same thing, in addition to the self-connection, the left connection is also used, because the provincial power has no higher-level department and he is the boss. If the internal connection is used, this record will be filtered out, because there is no higher-level department that matches him.
Self-connection is usually used to query the weight structure! Similar to the above table!