1. Right-join outer: the right table (Sys_employee) is the answer, and the records in the left table (sys_department) are queried only when their departmentid exist in the right table;
2. The left connection (outer join) is interpreted as left table (sys_department), and the record in the right table (Sys_employee) is queried only when its departmentid exists in the left table;
3. Fully connected (full join, complete outer join) Explanation: DepartmentID (U) record in two tables;
4. Inner JOIN (join, INNER join) Explanation: Two table of intersection (n) records of DepartmentID;
5. Cross-Join, full connection explanation: There is no cross join of the WHERE clause, it returns the Cartesian product of all data rows of the two tables connected, and returns the number of rows in the result set equal to the number of rows in the first table that meet the query criteria multiplied by the data rows in the second table that meet the query criteria.
6. Self-join explanation: Using a self-join can treat one mirror of its own table as another table, using self-linking: When one of the fields in the table is related to another field in the table, we may use the self-join (the intention of the self-connection is to treat a table as multiple tables to connect).
For example, the database has the following departmental structure table
Below we need to find out the name of each department and the name of the parent department. If there are two tables, a departmental table, a parent department table, that's good for writing SQL statements, but now the department and the parent department are in a table, and this is better with self-linking. The SQL statements and query results are as follows:
Here we notice that the research and development department does not appear in the department list, because the research and development department is the most superior department, it has no parent department. If this is the case we want the development department to appear on the left, even if it has no parent department. We can combine the left concatenated SQL statement with the query results as follows:
Mysql Join Statement parsing
