Database Connection Query

Join query category in SQL2

(1) Cartesian product | cross join: unconditional join. The result is the product of the number of two link element groups.

[From] <Link name 1> cross join <Link name 2>

(2) Natural join: equivalent join based on all public attributes. The result is the union of two relational modes (delete duplicate columns)

[From] <Link name 1> natural join <Link name 2>

(3) Internal join: The condition expression is used as the join condition. The floating tuples are not retained, and duplicate columns are retained. They can be equijoin or unequal join.

[From] <Link name 1> [INNER] JOIN <Link name 2> ON <connection condition expression> | USING (<column Name>)

(4) external connections: Floating tuples can be retained, which are divided into 3*2 = 6 cases.

[From] [NATURAL] <LEFT | RIGHT | FULL> outer join [ON <JOIN condition expression> USING (<column Name>)]

Additional Notes

(1) The connection can be established in the FROM clause or WHERE clause of the SELECT statement. It is helpful to distinguish the connection operation FROM the search conditions in the WHERE clause when pointing out the connection in the FROM clause;

(2) If the query involves multiple links (that is, connecting multiple links with commas), if the WHERE clause gives an internal connection condition, it is equivalent to using an internal connection; if no join condition is given, the FROM clause will join in the form of Cartesian product;

(3) when MySQL retrieves information from a table, you can prompt which index it chooses. This feature is useful if the EXPLAIN command shows that MySQL uses an index that may be incorrect in the index list. By specifying the use index (key_list), you can tell MySQL to USE the most appropriate INDEX to find record rows in the table. The optional syntax ignore index (key_list) can be used to tell MySQL not to use a specific INDEX.

(4) No matter which connection type, you cannot directly connect the columns of the text, ntext, and image data types, but you can indirectly connect these columns. For example:

SELECT p1.pub _ id, p2.pub _ id, p1.pr _ info

FROM pub_info ASp1 inner join pub_info AS p2

On datalength (p1.pr _ info) = DATALENGTH (p2.pr _ info)