MySQL advanced query

One, inner connection (INNER join)

1. Equivalent connection

Overview : The value of the connection column of the two tables is compared with the equals "=", which is equivalent to the two tables when the Cartesian is executed, and the values of the concatenated columns of the two tables are equal.

Syntax :

SELECT column from table 1 INNER JOIN table 2ON table 1. column = table 2. Column

Example :

SELECT a.*, B.*from student_info A inner JOIN student_score BON a.student_id = b.student_id

2, non-equivalent connection

Overview : refers to the value of a join column that compares two tables using the greater than sign > or less than "<", which is equivalent to a record of a table greater than or less than the value of a linked column of another table after the two tables perform Cartesian.

Syntax :

SELECT column from table 1 INNER JOIN table 2ON table 1. Columns <> table 2. Columns

Example :

SELECT a.*, B.*from student_info A inner join Student_score BON a.student_id > b.student_id

Results :

Second, outer coupling

1. Left outer connection (OUTER join)

Overview : Refers to the records that match all records of the left table to the right table, the result of the returned results, and a record of the left table that does not meet the criteria, and NULL in the appropriate column of the right table.

Example :

SELECT a.*, B.*from student_info A left join Student_score BON a.student_id = b.student_id

Results :

3. Full join--mysql not supported

Overview : Refers to all records in the left table are connected to all records in the right table, the results of the return, in addition to the results of the join, there are left table and the right table does not meet the criteria of the record, and the left table and the right table in the corresponding column to fill null.

Third, natural connection (NATURAL join)

Overview : Refers to automatically record matching of columns of the same name in a table.

Example :

SELECT a.*, B.*from student_info A natural Join Student_score B

Results :

Iv. Self-connected

Overview : Refers to the use of table aliases to implement the table itself connection.

Example :

SELECT b.*from student_score A, student_score bwhere a.student_id = B.student_idand b.student_score > 80

Results :

MySQL advanced query