Database Connection sorting

Connections can be divided into internal connections, external connections, cross connections, and self-connections. 1. Crossjoin1) supported databases: ORACLE, MYSQL, SQL Server, and DB2. 2) Function Description: The result returned by the cross-Join Operation contains a combination of records in the connected two tables. The number of records contained in the returned results is the product of the number of rows that meet the condition of the two tables. CROSSJOIN limit

Connections can be divided into internal connections, external connections, cross connections, and self-connections. 1. Cross join 1) supported databases: ORACLE, MYSQL, SQL Server, and DB2. 2) Function Description: The result returned by the cross-Join Operation contains a combination of records in the connected two tables. The number of records contained in the returned results is the product of the number of rows that meet the condition of the two tables. Cross join limit

Connections can be divided into internal connections, external connections, cross connections, and self-connections.

1. Cross join

1) supported databases: ORACLE, MYSQL, SQL Server, and DB2.

2) Function Description: The result returned by the cross-Join Operation contains a combination of records in the connected two tables. The number of records contained in the returned results is the product of the number of rows that meet the condition of the two tables. After cross join, only the WHERE clause can be used and the ON clause cannot be used.

3) underlying implementation and performance: cross join first produces the Cartesian Product tables of the two tables. If the table contains the WHERE clause, the results are filtered out from the Cartesian Product table (temporary table) based on the constraints, if the WHERE clause is not included, the Cartesian Product table is directly returned. Because cross join requires the production of cartesian products, the query speed is slow and has no practical significance. Therefore, it is often replaced by external connections and internal connections.

2. left join/right join, FULL JOIN

1) supported databases: ORACLE, MYSQL, SQL Server, and DB2.

2) Function Description: left join/right join and full join are external connections. In many databases, you can also write left outer join/right outer join and full outer join, left join (right join) returns results no matter whether the LEFT (RIGHT) Table record meets the conditions, it will return records in the LEFT (RIGHT) Table, LEFT (RIGHT) the connection contains all the records that meet the conditions in the two tables and the records that do not meet the conditions in the left (right) table. If no corresponding record exists in the right (left) Table, NULL is displayed. The return result of full join contains all the records that meet the conditions in the two tables and the records that do not meet the conditions in the two tables. The corresponding vacancy is displayed as NULL.

3) underlying implementation: The left and right connections generate temporary tables based on the left and right tables.

3. NATURAL JOIN

1) supported databases: ORACLE.

2) Function Description: if the field names and Data Types in the two tables are the same, natural join will automatically JOIN the two tables. If the field names are the same and the types are different, an error is returned.

4. INNER JOIN

1) supported databases: ORACLE, MYSQL, SQL Server, and DB2.

2) Function Description: In general databases, the default connection is inner join, and only records meeting both conditions are returned.

3) underlying implementation and performance: inner join and on are used together to produce temporary tables that meet the conditions. Therefore, innner join has a high performance.

5. Self-connection

1) supported databases: ORACLE, MYSQL, SQL Server, and DB2.

2) Function Description: self join connects itself to itself through a table to obtain special functions. In fact, self join is used for other connections, but only one table is used for objects. You can use aliases to connect to the same table.

The example is not complete. It needs to be improved ......