How to use mysql table connection

Why use table joins

• #What is table join?

• #If the data comes from multiple tables, it can be implemented using linked queries. Therefore, table connection is to combine multiple table connections to achieve query effect

• The principle of table connection

• Table connection The Cartesian product is used, which is called a lateral connection.

• Cartesian product

• The basic principle of table connection is to use Cartesian product. The Cartesian product refers to connecting all the data in the two tables, and the final number of the connected results is the product of the quantities in the two tables.

• As can be seen from the above diagram, the table join is the result of multiplying the data of the two tables. Each record of the first table will be All records in the table are connected.
  To connect multiple tables in the database, you need to use the JOIN keyword.
  Standard structure

　　

• There are too many results from the Cartesian product, and a lot of the data is useless of. Because in order to express their relationship when we create a table, we will establish foreign keys to determine the relationship, so when joining tables, we must filter useless data based on their foreign keys. Use the ON keyword to determine if it matches.
  Complete structure

　
--Table connections in MySQL are divided into: inner connections, external links, both horizontal connections and Oracle's vertical connections

--Use table join -- The first syntax of inner join inner join inner can be omitted

1. select * from main table name as main alias join join table name as connected Alias ​​on main table name. Column name = connection table name. Column name where condition

-- External link:

Definition: External join This means that regardless of whether there is a match or not, the table data with outer joins defined will appear in the results. For example, in a left outer join, the table on the left side of the JOIN is defined as an outer join, and all data in this table will appear in the query results. A right outer join means that the table on the right side of the JOIN is an outer join table. A full outer join means that the left and right tables of JOIN are both outer join tables.

• Left outer join
  Usage: LEFT OUTER JOIN or LEFT JOIN

• Right outer 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 joins

# --Self-join

Definition:

Self-join is actually a special case of inner join or outer join. INNER JOIN or OUTER JOIN can also be used.

The tables connected by self-join are from the same table.

Example:

• A table that stores employee information. Each employee has a direct leader. Of course, direct leaders are also employees, so employee information and their leader information are in one table. The following is the table structure:
  

The above is the detailed content of How to use mysql table connection. For more information, please follow other related articles on the PHP Chinese website!