Different types of table joins in SQL

Http://www.linuxidc.com/Linux/2012-08/68035.htm

1. Introduction

In a relational database, a join operation is a very common practice when combining data from different tables. Let's start by looking at how joins work, and then we'll explore the order of execution when the join and where statements are present, and finally talk about the order of the different types of joins.

2, the establishment of the original test table structure (the table statement to download here)

After the table is set up, you will see the following three tables.

We will demonstrate the join operation through the three tables above. All three of these tables are for demonstration purposes, so I don't use primary and foreign keys.

3. Cartesian product of the table

In general, we use related fields from two tables for join operations, for example, the DeptID field in the Employee table corresponds to the Depid field in the department table, which is used in this way to join.

The following example does not use association fields to make a table connection. Here TableA and TableB are connected in the same way as the Cartesian product. The Cartesian product is to take a record from the first table, match each record in the second chapter, and then remove the second record, together with all the records of the second table, until all the records in the first table are taken. So the final number of results will be the product of the two tables.

4. Join two sheets

When we want to make a connection to two tables instead of getting a lot of useless results like the example above, we have to pick a join column from both tables. The example I gave below is the use of ID as the join column. This way we can make the results map only the part we need, thus filtering out useless data.

Note: The first and fifth rows in the Cartesian product table satisfy the mapping of the join, thus being treated as a result, and the others are filtered out.

5. Join more than one table

The above example is a join two table, if you want to join more than one table, we need to select a column in the above results, and then select a column in the new table, the two as the join field, and then specify the rules of the join, so we can theoretically join any number of tables.

First, table_a and Table_b do a connection, on top of the join two table example, and then the result of the join as a table AB. Connect the AB to the Table_c again.

6. Join type

There are 3 join types in which two different tables are connected.

1. Full Join
2. INNER JOIN
3. Outer join (left OUTER join, right outer join)

In the above two examples we see the inner join. If we connect the table itself it's called the self join. This particular type does not confuse the connection type.

7. Full Join

The full join is somewhat different from Descartes, and the Cartesian product gets all possible results. The full join adds the matching result to the left side of the table that does not match the right row and all the rows to the left of the table on the right, using NULL instead of the match. Result rows = number of matching rows + Left table remaining rows + right table remaining rows.

In the above picture, the Blue line is a row that matches two tables.

The second row, the left green, the right red is mismatched, the rows in the left table are present, and the fields in the right table are filled with null.

The third row, the left red, the right green is also mismatched, the row in the right table is present, and the field in the left table is filled with null.

8. Left Join

Left join (left join) guarantees that all rows in the table are available, and when mismatched, fills the right table field with NULL.

Blue match, red and green do not match

9. Right Join

In turn, the right join ensures that all rows in the right table have all, and when mismatched, fills the left table field with null.

Blue match, red and green do not match

10. INNER JOIN

Inner JOIN IS to list only matching rows.

11. Self Join

The table connection itself is called the self join. To explain this, let's look at the employee table as in. EmployeeID is the primary key for this table, and ReportsTo references the primary key for this table. As we can imagine, the Reportto field refers to the boss of the employee, whose boss is also an employee.

See the example below

Here, the line that Reportto points to IS manager, so employee is the left table and manager is the right table.

12. Order of execution

When there is a where statement in the connection, we need to be aware of the connection and where execution order issues.
1, the WHERE statement before the join execution, because the results of the where query will be less, so that the join operation performance will be improved.
2. Executes the WHERE statement after the join.
Both of these will return the same result when inner join, but at least one of the join operations has a different return result when using outer join. See the example below.

So remember to do the join operation first and then execute the WHERE statement when the outer join occurs.

13. Sequence of connections

The order of joins is also important when you want to use inner join and outer join simultaneously.

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

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

1. Right join in orders and customers. The right connection ensures that you get all the customer information, whether or not he has an order.
2. The above results will now be connected to order details. However, we need to note that there are two lines of roderid null in the result of the right connection, because the two customer does not have any order, and when the inner join is done later, the OrderID will skip the two lines because the Null,inner join. This led to the filtering of the two customer information.
Let's take a look at the following query "first inner join and outer join"

Let's analyze why this is the result we want.

The order and order Details tables are first inner join, all matching results are listed, then the result is left, the Customer table is the right table, and all the rows of the right table are listed, regardless of whether they match or not (implied, The two customer with no order will also be listed).

Therefore, it is important to consider the order of joins when we use both the inner join and the outer join.

14. Other ways to obtain the same data

Look at the following query

1, first query out customers it as the left table
2. Then the Orders table is queried, still as the left table
3. The Order Details table is then queried for inner join as the right table and the Orders table.
4, the final Customers table will be in the third step to query the results of the left connection. Don't forget that the left connection will ensure that the Customers table does not lose any records.

Different types of table joins in SQL