Internal Connection, external connection, and Cross Connection

Explore internal connections, external connections, and cross connections
(1) The Connection query operation in the internal connection lists the data rows that match the connection conditions. It uses the comparison operator to compare the column values of the connected columns.
Intranet connections are divided into three types:
1. equijoin: Use the equal sign (=) operator in the connection condition to compare the column values of connected columns. All columns in the connected table, including duplicate columns, are listed in the query results.
Use equijoin to list duplicate columns (towncode) in the landtbl and towntbl tables in the selection list ):
Example: Select * From landtbl as a inner join towntbl as B on A. towncode = B. towncode
2. Unequal join: Use a comparison operator other than the equal operator to compare the column values of the connected columns.
These operators include >,>=, <=, <,!> ,! <And <>.
Example: Select * From landtbl as a inner join towntbl as B on A. towncode <> B. towncode
3. Natural join: Use the equal to (=) operator in the connection condition to compare the column values in the connected column. However, it uses the selection list to indicate the columns included in the query result set, delete duplicate columns in the connection table.
Delete duplicate columns (towncode) in the landtbl and towntbl tables in the selection list using a natural connection ):
Select a. *, B. citycode, B. townname from landtbl as a inner join towntbl as B on A. towncode = B. towncode

(2) When connecting to an external connection, only the rows that meet the query condition (where search condition or having condition) and connection condition in the returned query result set are returned. When an external connection is used, it returns to the query result set that contains not only rows that meet the connection conditions, but also the left table (when the left outer connection is used) and the right table (when the right outer connection is used) or all data rows in two edge join tables (all Outer Join.
The left Outer Join shows the full-condition data rows and the non-conforming data rows in the left data table.
Example:
Select a. *, B. citycode, B. townname from landtbl as a left (outer) join towntbl as B on A. towncode = B. towncode
Outer right join: displays data rows with full conditions and non-conforming data rows in the data table on the right
Example:
Select a. *, B. citycode, B. townname from landtbl as a right (outer) join towntbl as B on A. towncode = B. towncode
Full outer join: displays data rows with full conditions and data rows that do not meet the conditions in the left and right data tables
Example:
Select a. *, B. citycode, B. townname from landtbl as a full (outer) join towntbl as B on A. towncode = B. towncode
(3) crossjoin without the WHERE clause returns the Cartesian product of all data rows in the two joined tables, the number of rows returned to 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.
Select a. *, B. citycode, B. townname from landtbl cross join towntbl

 

Appendix (for future exams ):

Chinese description of table towntbl
column name
citycode Code
towncode
townname township name
table landtbl
Chinese field Name Description
landcode Land Code
landname land name
landqty land area
towncode township Code
type land type