MySQL table connection _ MySQL

MySQL table connection 1. internal join

Syntax:

SELECT ... FROM join_table    [INNER] JOIN join_table2     [ON join_condition]     WHERE where_definition

Only data rows that match the connection conditions in these connection tables are listed.

Example:

SELECT * FROM table1 a JOIN table2 B ON a. dept_id = B. id; or SELECT * FROM table1 a, table2 B WHERE a. dept_id = B. id;

II. external connection

Syntax:

SELECT ... FROM join_table    (LEFT|RIGHT|FULL) [OUTER] JOIN join_table2    ON join_condition    WHERE where_definition

Lists not only the rows that match the connection conditions, but also the left table (left outer join), right table (right outer join), or two tables (all outer join) all data rows that meet the WHERE filter conditions. Note that MySQL5.1 currently does not support full join connections.

Example:

LEFT outer JOIN: SELECT * FROM table1 a left join table2 B ON. dept_id = B. id; RIGHT outer JOIN: SELECT * FROM table1 a right join table2 B ON. dept_id = B. id;

III. Cross join

Syntax:

SELECT　... FROM join_table CROSS JOIN join_table2;

Without the ON clause and the WHERE clause, it returns the Cartesian product of all data rows in the join table.

The number of rows in the result set is equal to the number of rows that meet the query conditions in the first table multiplied by the number of rows that meet the query conditions in the second table.

Equivalent:

SELECT ... FROM table1, table2;

IV. Self-connection

The tables involved in the connection are all the same table. (Virtual output is achieved by giving the table an alias)

SELECT. name aname, B. name bname FROM table a left join table bON B. id =. gmr; -- query the employee name and the manager name. SELECT. name aname FROM table a right join table B ON. id = B. gmr; -- query the names of all managers

Self-join is common in practical applications, and its statements can also be bypassed. you can think of the same table as two different tables. for example, if the table is clearly the same, the basic information of an employee is recorded, which includes the ID of the manager of each employee. you can treat the employee as an employee table and a manager table. Er ~ How can this problem be solved? it's still a bit difficult. wait for the actual application to explore it slowly...