SQL join example. (Left connection, right connection, full connection, inner connection, cross connection, self-connection) Self-steel life)

Recently, the company is recruiting people. My colleagues asked a few questions about database connection that I think the database can be applied ~
Now I want to write about their functions here.
Assume that the following table is used:

One is the voting master table, and the other is the voter information table ~ Record the IP address of the voter and the corresponding voting type. The left-right connection is actually the result of our joint query. Which table prevails ~
1: for example, right join or right outer join:

Take the voter table on the right as the standard. The record in the left table (votemaster) is displayed only when its ID exists in the right table (voter). For example, id 3.4.5.6 on the left is not displayed because the IDS do not have corresponding records in the right table!
2: therefore, we can naturally understand left join or left Outer Join.

It can be seen that the ID on the right is displayed only when it exists. If there is no corresponding data on the right, use null instead!
3: Full join or full outer join For the data in both tables, the effect is the same as that shown above!
4: inner join or join; It is the record that the returned field ID exists in both the table votemaster and voter.

5: cross join (full join) cross join without the where Condition
A cross join without a where clause will generate the Cartesian product of the table involved in the join. The number of rows in the first table multiplied by the number of rows in the second table is equal to the size of the Cartesian result set. (Table 1 and Table 2 generate 6*3 = 18 Records)

Equivalent Select VM. ID, VM. votetitle, vt. IP From Votemaster As VM, voter As Vt

6: Self-join. here I use an example of a Power Project I used earlier (transformed)
The following table:
This is a department table, the Department and its parent departments are stored in the same table. We assume that you need to use SQL to query the departments and their parent departments! Just do it.
of course, the same can be done without self-connection:
we achieved the expected goal! In this query, A subquery is used to query the names of the upper-level departments. If you use a self-connection, the structure is much clearer.
is the function also completed? In addition to self-connection, the left connection is also used, because the provincial power company does not have a higher-level department and he is the boss, if an internal connection is used, this record is filtered out because no parent department matches the record.
a lot of self-connections use queries on the weight structure! Similar to the above table!