How database tables are connected to tables

Connection query methods are:

Internal connection, outer connection (left, right, full), cross Connect

The difference between left join and right connections:

Left join the left table as the benchmark to query, the left table data will be all displayed, the right table if the data matching the left table shows the corresponding field data, if not match, the display is null; Right connection is just the opposite.

The full connection is the left outer connection with the left table, then the right outer join.

Description: The so-called benchmark, that is, a table of restrictions on the condition of the search criteria prevail!

Specific as follows:

One, inner connection

The INNER JOIN query operation lists the data rows that match the join criteria, and it uses comparison operators to compare the column values of the concatenated columns. There are three types of internal connections:

1. Equivalent connection: Use the equals sign (=) operator in the join condition to compare the column values of the joined columns, and the query results list all the columns in the joined table, including the repeating columns.

2, unequal connections: Use a comparison operator other than the equals operator in the join condition to compare the column values of the connected columns. These operators include >, >=, <=, <,!>,!<,! =, and <>.

3. Natural connection: Use the Equals (=) operator in the join condition to compare the column values of the connected column, but it uses the selection list to indicate which columns are included in the query result collection and to delete the duplicate columns in the Join table.

Second, outer connection

Returning to the query results collection includes not only rows that meet the join criteria, but also all rows of data in the left table (when left outer joins), right table (when right outer joins), or two edge tables (full outer joins).

Three, cross-linking

The cross join does not take a WHERE clause, it returns the Cartesian product of all data rows of the two connected tables, and the number of rows returned to the result set equals the number of data rows in the first table that meet the query criteria multiplied by the number of data rows in the second table that meet the query criteria. example, there are 6 types of books in the titles table, and 8 publishers in the publishers table, the number of records retrieved by the following cross-connection will be equal to the 6*8=48 row.

How database tables are connected to tables