MS SQL Basics Tutorial: Connection queries in a database

Each table in the database holds different data, and users often need to combine the data from multiple tables to extract the information they need. If a query needs to operate on more than one table, it is called a connection query. The result set or result table of a connection query, called a connection connection between tables. Queries actually query data through the association of common columns between tables, which is the main feature of relational database queries.

The connection query is divided into equivalent connection query, non equivalence connection query, self connection query, external connection query and compound conditional connection query.

10.3.1 equivalence and non-equivalence connection query

A connection between tables is a query that is connected by an equal field value called an equivalent connection query. You can specify a join condition in two ways. The two program segments in the following example run the same results.

Note: in the join condition of the edge-continuation query, the data types of each join field need not be the same, but must be compatible.

10.3.2 non-equivalence connection query

The equals sign is not used in the join condition of the equivalence query, and other comparison operators are used to form a non-equivalence join query. The comparison operators you can use are:>, >, =, <, <=,!=, and you can also use between ... predicates such as and.

Obviously, this example has no practical application value, and also shows that the non-equivalence connection query often needs to be used in conjunction with other connection queries, especially with the equivalent connection query. For its use, see the composite conditional connection query described later in this article.

10.3.3 Self-Connection query

Connections can be made not only between tables, but also from one table to itself, a connection called a self-connected

(self join), the corresponding query is called a self-connection query.

Example 10-21: Query employees who work with the same seniority in the company.

Use Pangu

10.3.4 External Connection Query

In the preceding example, the result of the connection is to select the data that matches the join condition from a combination of two or more tables, and discard the data if it cannot meet the join condition. This method is usually referred to as an internal connection (Innerjoin). In an internal connection, the status of the table participating in the connection is equal. The way that is relative to an internal connection is called an outer join (Outer join). In an external connection, the table participating in the connection has a master-slave matches data columns from the table with each row of data in the primary table, data that meets the join condition is returned directly to the result set, and columns that do not meet the join condition are filled with null values and then returned to the result set (for the bit type column, because bit The data type does not allow null values and will therefore be filled with a value of 0 and returned to the result.

The external connection is divided into a left outer join (the right Outer join) and a Outer join. The external connection is distinguished by the direction in which the primary table is located, and the primary table on the left is called the left outer join, and the primary table is on the right, called the right outer join.

10.3.5 Composite Conditional Join query

A query that uses multiple join conditions in a WHERE clause, called a composite conditional join query.

See the full set of "MS SQL Basics Tutorials"