[Personal notes] Join the "" table and note the "" table

[Personal notes] Join the "" table and note the "" table
Why table join?

• What is table join?
  • If the data comes from multiple tables, you can use the link query method. Therefore, table join is the combination of multiple tables to achieve query results.
• Table join Principle
  • Table join uses the Cartesian product, which is called a horizontal join.
• Cartesian Product
  • The basic principle of table join is to use Cartesian product. Cartesian product refers to the product that connects all data of two tables, and the result of the last join is the product of the number of two tables.

• From the figure above, we can see that table join is the result of multiplying the data of two tables. Each record of the first table is connected to all the records of the second table.
  To connect multiple tables in a database, you must use the JOIN keyword.
  Standard Structure

　　　　

• There are too many results produced by Descartes, and a lot of data is useless. When creating a table, a foreign key is used to determine the relationship to indicate its relationship. Therefore, useless data must be filtered based on its foreign key during table join. Use the ON keyword to determine whether it matches.
  Complete Structure

 

　　　　
-- Table connections in MySQL are divided into internal connections and external connections, which are both horizontal connections and oracle vertical connections.

 

-- Use table join -- the first Syntax of inner join inner can be omitted.

-- External link:

Definition:External join means that the table data of the defined external join will appear in the result no matter whether there is a match or not. For example, if the left Outer JOIN clause is used, the table on the left of the JOIN clause is defined as an outer JOIN. All data in this table will appear in the query results. Outer JOIN on the right is the outer JOIN table on the right of JOIN. The outer JOIN means that both the left and right tables of the JOIN operation are outer JOIN tables.

• Left Outer Join
  Usage: left outer join or LEFT JOIN
• Outer right join
  Usage: right outer join or RIGHT JOIN
• Full outer join
  Usage: full outer join or FULL JOIN
  
• Note: MYSQL does not support full outer join.

 

-- Self-connection

Definition:Self-JOIN is actually a special case of inner join or outer join. You can also use inner join or outer join.
The joined table is from the same table.

• Example:A table that stores employee information. Each employee has a direct supervisor. Of course direct leaders are employees, so both employee information and their leadership information are shown in another table. The following table structure: