Special join query

In addition to the traditional three join queries, there are also some special join queries. This article will summarize the following types of special join queries.

1. Combined join query

2. Unequal join query

3. Multi-table join query

Combined join query

A composite join is a query of multiple columns on both sides of a join. When you need to join two tables based on the primary key-foreign key relationship, and the primary key-foreign key relationship is composite (that is, the relationship is based on multiple columns), you usually need to use a composite join.

For example, assume that DBO. table 2 defines two foreign keys (col1 column and col2 column) and references DBO. for the columns col1 and col2 in Table 1, you need to write a query statement that connects the two tables based on the relationship between the primary and Foreign keys. The query statement can be written as follows:

-- Composite join query from DBO. Table1 as t1join DBO. Table2 as T2 on t1.col1 = t2.col1 and t1.col2 = t2.col2

Unequal join query

Join conditions include only equal signs. We generally call them equal join conditions. While unequal joins are just the opposite. Their join conditions include operators other than equal signs.

As an example of an unequal join query, the following query statement joins two instances in the Employees table to generate a unique pairing between employees.

Use dependencies; go -- unequal join query select employees1.empid, latency, latency, employees2.empid, latency, latency from HR. Employees as employees1join HR. Employees as employees2 on employees1.empid

Query results:

Multi-table join query

If the above two special joins are still used less, the queries for multi-Table joins will be used more.

A join table operator operates only two tables, and a query statement can contain multiple joins. Generally, when the from clause contains multiple table operators, the table operators are logically processed from left to right. That is to say, the result table of the first table operator will be used as the input of the second table operator, and the result of the second table operator will be used as the input on the left of the third table operator, and so on.

Therefore, if the from clause contains multiple joins, only the first join is logically used to operate on two basic tables. Other joins use the result of the previous join as the input on the left.

For example, the following query first joins the MERs table and the orders table to match the order between the customer and their orders, and then joins the result table of the first join with the orderdetails table, to match the order and order details.

SQL query code:

-- Multi-table join query select MERs. custid, customers. companyName, orders. orderid, orderdetails. productid, orderdetails. qty from sales. customers as customersleft join sales. orders as orders on customers. custid = orders. custidleft join sales. orderdetails as orderdetails on orders. orderid = orderdetails. orderidorder by MERs. custid;

Query results: