Different types of table connections in SQL

In relational databases, join operations are common when data in different tables are joined. First, let's look at how join is operated. Then we explore

In relational databases, join operations are common when data in different tables are joined. First, let's look at how join is operated. Then we explore

1. Introduction

In relational databases, join operations are common when data in different tables are joined. First, let's take a look at how the join operation works. Then, let's explore the execution sequence when the join and where statements exist at the same time. Finally, let's talk about the sequence of different types of joins.

2. Create an initial test table structure (the table creation statement is downloaded here)

After the table is created, the following three tables are displayed.

The preceding three tables are used to demonstrate the join operation. These three tables are used for demonstration, so I didn't use primary keys or foreign keys.

3. Cartesian product of a table

Generally, we use related fields in two tables for join operations. For example, the DeptId field in the employee table corresponds to the DepId field in the Department table.

In the following example, table join is performed without using association fields. TableA and TableB are connected by the Cartesian product. The Cartesian product is to first retrieve a record from the first table, cooperate with each record in the second chapter, and then retrieve the second record. It also works with all the records in the second table, until all records in the first table are obtained. Therefore, the final result quantity is the product of two tables.

4. join two tables

When we want to connect two tables, instead of getting a lot of useless results like the above example, We have to select a join column from the two tables. The example below uses id as the join column. In this way, we can map the result to only the part we need to filter out useless data.

Note: In the Cartesian Product table, the first row and the fifth row satisfy the join ing relationship and are used as the result. All others are filtered out.

5. join multiple tables

The example above is to join two tables. If you want to join multiple tables, you need to select a column in the above results and then select a column in the new table, use the two as join fields and specify the join rules. In this way, we can theoretically join any multiple tables.

First, Table_A and Table_ B are connected. In the preceding join two tables example, the join result is used as a table AB. Connect AB to Table_C.

6. join type

There are three join types for joining two different tables.

1. full join
2. inner join
3. outer join (left outer join, right outer join)

In the above two examples, we see inner join. If we connect the table itself, it is called self join. This special type does not confuse the connection type.

7. full join

Full join is somewhat different from Descartes. cartesian products obtain all possible results. Full join adds the matched results to the rows on the right that do not match the rows on the left that do not match the rows on the right. Use NULL in the unmatched places. Result row number = number of matched rows + number of remaining rows in the left table + number of remaining rows in the right table.

In the preceding figure, the blue rows match the two tables.

The second row is green on the left, and the red on the right does not match. The rows in the left table exist, while the fields in the right table are filled with null.

The third row is red on the left, and the green on the right does not match. The rows in the right table exist, while the fields in the left table are filled with null.

8. left join

Left join ensures that all rows in the left table exist. If the left join clause does not match, the right table field is filled with NULL.

Match in blue, not in red or green

9. right join

In turn, right join ensures that all rows in the right table exist, and fill in the left table fields with NULL when there is no matching.

Match in blue, not in red or green

10. inner join

Inner join is used to list matching rows only.

11. self join

Table join itself is called self join. To explain this, let's look at the employee table as shown in. EmployeeID is the primary key of the table, and ReportsTo references the primary key of the table. As we can imagine, the ReportTo Field Reference represents the employee's boss, who is also an employee.

See the following example.

Here, the row to which ReportTo points is Manager, so "employee" is the left table and "Manager" is the right table.

12. execution sequence

When there is a where statement in the connection, we need to pay attention to the sequence of connection and where execution.
1. Run the where statement before the join statement, because the results of the where query will be relatively small after execution, thus improving the performance of the join operation.
2. Run the where statement and join.
The above two will return the same results during inner join, but at least one join operation will return different results when outer join is used. See the following example.

So remember to execute the join operation before executing the where statement when the outer join is performed.

13. Connection Sequence

When you want to use both inner join and outer join, the order of join is also very important.

What is the order of connection? If I connect three tables [X inner Y] left Z like this, the order is to join inner first and then left join.

Let's go back to the example above. The result you want is to get the names of all customers, whether or not they have orders. If they do have some orders, they also need to list the customer's order quantity.
See the following query [first join outer and then join inner]

1. right join is performed in Orders and MERs. The right connection ensures that you obtain information about all customers, regardless of whether there is an order or not.
2. Now the above result will be connected to Order Details. However, we need to note that there are two rows in the result of the right join: roderid is null, because the two customers do not have any order, and when the inner join is followed, because orderid is null, inner join skips the two rows, so the information of the two customers is filtered out.
Let's take a look at the following query [inner join before outer join]

Let's analyze why this is what we want.

First, the Order and Order Details tables are joined by inner. All matching results are listed. Then, the results are used as the left table, and the Customer table as the right table ,, all the rows in the right table will be listed, regardless of whether they match or not (in other words, the two customers without order will also be listed ).

Therefore, when we use inner join and outer join at the same time, we must carefully consider the order of the connections.

14. Other methods to obtain the same data

See the following query.

1. First, query Customers to use it as the left table.
2. query the Orders table as the left table.
3. query the Order Details table and use it as the right table for inner join with the Orders table.
4. The Customers table is connected to the left of the result queried in step 3. Do not forget that the left join ensures that no records are lost in the MERs table.

OSChina. NET Original translation/original article link