Connection queries in the 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.

The connection between the

10.3.1 equivalence and non-equivalent connection query
table is called an equivalent join query by a query that is connected by an equal field value. 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 a continuous query, The data types for each connection field do not have to be the same, but must be compatible. The

10.3.2 non-equivalence connection query
does not use an equal sign in the join condition of an equivalent query, and other comparison operators form a non-equivalence join query. The comparison operators you can use are:>, >, =, <, <=,!=, and you can also use between ... predicates such as and. Connection query in


     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. The

10.3.3 connection Query
Connection can be made not only between tables, but also a table with itself, which is called a self-connected
connection (self join), and the corresponding query is called a self-connection query.
Example 10-21: Query for employees with the same seniority of work 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 calledInternal 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.