Internal connection cross Connect outside connection

database table connections are broadly divided into three types: cross-connect, inner-connect, outer-join

Cross join: In fact, it is a special case of inner join, without query condition

INNER JOIN (INNER join): Equal connection, unequal connection, natural connection

Outer JOIN (OUTER join): Left outer join, right outer link, full outer join (full outer connection only part of the RDBMS system can do, such as Oracle. Do not have MySQL, SQL Server, Access)

Cross Connect:

The simplest is to return the result of multiplying each row of multiple tables, that is, the Cartesian product. The keyword is cross JOIN

There are three types of notation: Cross join, join, comma separated table name

A cross-connection is an inner connection that is basically a cross-connection that removes some result data rows through a query condition.

The following four ways to get the results are the same, the first three are cross-linking, the last is a special case of the inner connection (without conditions):

1 SELECT *  fromTb_person Cross JOINTb_book;--Full write: Corss JOIN2 SELECT *  fromTb_personJOINTb_book;--Shorthand: JOIN3 SELECT *  fromTb_person,tb_book;--can also be shortened:,4 5 SELECT *  fromTb_personINNER JOINTb_book;--same as non-conditional inner joins

Internal connection:

Equal connection: The condition uses the equivalent symbol (=)

Unequal connections: Conditional use of unequal symbols (>,>=,<,<=,!>,!<,<>)

Natural connections: Natural connections are used only if the same column names are included in two tables, and natural joins use the same list to construct an equal connection by default

1 SELECT *  fromTb_personINNER JOINTb_bookWHERETb_person.id=tb_book.person_id;--Equal Connections2 SELECT *  fromTb_personINNER JOINTb_bookWHERETb_person.id<>tb_book.person_id;--Unequal Connections3 SELECT *  fromTb_person NATURALJOINTb_book;--Natural Connection4 SELECT *  fromTb_person NATURALJOINTb_bookWHERETb_person.id=Tb_book.id;--as in the previous sentence, only the default condition is explicitly written out.

External connection:

Focus on the relationship between the two tables, that is, the left and right order of the table, often referred to as the table.

Left OUTER join: Receives each row in the left table and uses the rows to match the right table to find the qualifying rows in the right table and eventually joins to form a data result set. If the right table for the left table row Records cannot find a row record that matches it, a null is used instead of the right table to get the connection matching result. Left outer joins are especially useful when left table has a one-to-many relationship with the right table.

Right-OUTER join: The same as the left outer join, except that the right table dominates. If you swap the left and right tables in the left outer join, you get the result of the right outer joins.

Full OUTER join: The characteristics of the left and right outer joins are synthesized, the left table and the right table have the same status, the left table has no matching data with null substitution, and the right table no matching data is substituted with NULL, that is, the left and right table data will be in the query results, no less data.

1 SELECT *  fromTb_person Left OUTER JOINTb_book onTb_person.id=tb_book.person_id;2 SELECT *  fromTb_person Left JOINTb_book onTb_person.id=tb_book.person_id;--Shorthand3 4 SELECT *  fromTb_person Right OUTER JOINTb_book onTb_person.id=tb_book.person_id;5 SELECT *  fromTb_person Right JOINTb_book onTb_person.id=tb_book.person_id;--Shorthand6 7 --outer joins are not a feature of every database8 SELECT *  fromTb_person Full OUTER JOINTb_book onTb_person.id=tb_book.person_id;9 SELECT *  fromTb_person Full JOINTb_book onTb_person.id=tb_book.person_id;--Shorthand

Here's one thing to note:

The comma form of the cross join and the natural join can only use the Where condition, the outer join can only use the on condition, the remaining connection forms can use where and can use the on

1 --Use the WHERE keyword only2 SELECT *  fromTb_person,tb_bookWHERETb_person.id=tb_book.person_id;3 SELECT *  fromTb_person NATURALJOINTb_bookWHERETb_person.id=Tb_book.id;--In fact, the natural connection does not need explicit addition conditions at all4  5 --The on keyword can only be used6 SELECT *  fromTb_person Left OUTER JOINTb_book onTb_person.id=tb_book.person_id;7 SELECT *  fromTb_person Right OUTER JOINTb_book onTb_person.id=tb_book.person_id;8 SELECT *  fromTb_person Full OUTER JOINTb_book onTb_person.id=tb_book.person_id;

Internal connection cross Connect outside connection