MySQL tutorial (quad) SQL connection query

In more cases, the data we are querying comes from multiple tables, and it is necessary to have a look at the connection queries in MySQL.

SQL divides connection queries into four categories: cross-joins, inner joins, outer joins, and natural joins.

Data preparation

Student table

--------------------------------table structure for ' student '------------------------------DROP table IF EXISTS ' Student '; CREATE TABLE ' student ' (  ' id ' int (one) not null auto_increment,  ' name ' varchar () DEFAULT NULL,  ' age ' tinyint (4) default null,  ' classId ' int (one) default null,  PRIMARY KEY (' id ')) engine=innodb auto_increment=7 default CHAR Set=utf8;--------------------------------Records of student------------------------------INSERT into ' student '  VALUES (' 1 ', ' s1 ', ' A ', ' 1 '), insert INTO ' student ' values (' 2 ', ' s2 ', ' A ', ' 1 '), insert INTO ' student ' values (' 3 ', ' S3 ', ' 2 '); INSERT into ' Student ' VALUES (' 4 ', ' S4 ', ' n ', null);

Class table

--------------------------------table structure for ' class '------------------------------DROP table IF EXISTS ' class ' ; CREATE TABLE ' class ' (  ' id ' int (one) not null auto_increment,  ' cname ' varchar) DEFAULT NULL,  PRIMARY KEY (' I d ')) Engine=innodb auto_increment=4 DEFAULT Charset=utf8;--------------------------------Records of class----------- -------------------INSERT INTO ' class ' values (' 1 ', ' one Shift '); INSERT INTO ' class ' values (' 2 ', ' Class two '); INSERT INTO ' class ' VALUE S (' 3 ', ');

Score Table

--------------------------------table structure for ' score '------------------------------DROP table IF EXISTS ' score ' ; CREATE TABLE ' score ' (  ' id ' int (one) default null,  ' name ' varchar () default NULL,  ' score ' decimal (4,1) Defau LT null,  ' studentid ' int (one) default NULL) engine=innodb default Charset=utf8;-------------------------------- Records of score------------------------------insert INTO ' score ' VALUES (' 1 ', ' language ', ' 90.0 ', ' 1 '); INSERT INTO ' score ' VAL UES (' 2 ', ' math ', ' 95.0 ', ' 1 '); INSERT INTO ' score ' values (' 3 ', ' language ', ' 92.0 ', ' 2 '); INSERT INTO ' score ' values (' 4 ', ' Math ', ' 8 8.0 ', ' 2 '); INSERT INTO ' score ' values (' 5 ', ' language ', ' 96.0 ', ' 3 '); INSERT INTO ' score ' values (' 6 ', ' math ', NULL, ' 3 ');

Cross Connect

Cross joins are used to connect each row in the left table to each row in the right table and cannot use the on keyword. The result will be all combinations of the rows of data in both tables, the Cartesian product of all data for both tables. If there are 4 records in Table A and 3 in B, the result is 4*3=12 records.

Mysql> SELECT * from student cross JOIN class;+----+------+-----+---------+----+-------+| ID | name | Age | ClassId | ID | CNAME |+----+------+-----+---------+----+-------+|  1 | S1   |  |       1 |  1 | Class One  | |  1 | S1   |  |       1 |  2 | Class II  | |  1 | S1   |  |       1 |  3 | |       |  2 | S2   |  |       1 |  1 | Class One  | |  2 | S2   |  |       1 |  2 | Class II  | |  2 | S2   |  |       1 |  3 | |       |  3 | S3   |  |       2 |  1 | Class One  | |  3 | S3   |  |       2 |  2 | Class II  | |  3 | S3   |  |       2 |  3 | |       |  4 | S4   |  22 | NULL    |  1 | Class One  | |  4 | S4   |  22 | NULL    |  2 | Class II  | |  4 | S4   |  22 | NULL    |  3 |       | +----+------+-----+---------+----+-------+12 rows in Set

If you add a where keyword to a cross join, the result set that matches the condition is returned, which is the same as the execution result of the inner join.

Mysql> SELECT * From student Cross JOIN class WHERE Student.classid = class.id;+----+------+-----+---------+----+----- --+| ID | name | Age | ClassId | ID | CNAME |+----+------+-----+---------+----+-------+|  1 | S1   |  |       1 |  1 | Class One  | |  2 | S2   |  |       1 |  1 | Class One  | |  3 | S3   |  |       2 |  2 | Second Class  |+----+------+-----+---------+----+-------+3 rows in Set

Internal Connection

An inner join (INNER join) matches every row in the left table with all records in the right table, with the result that the query results in a Cartesian product with two tables filtered by on condition

Mysql> SELECT * FROM student INNER JOIN class on student.classid = Class.id; --Recommended writing, inner can write not write +----+------+-----+---------+----+-------+| ID | name | Age | ClassId | ID | CNAME |+----+------+-----+---------+----+-------+|  1 | S1   |  |       1 |  1 | Class One  | |  2 | S2   |  |       1 |  1 | Class One  | |  3 | S3   |  |       2 |  2 | Second Class  |+----+------+-----+---------+----+-------+3 rows in Set

Equivalent to

Mysql> SELECT * FROM student, class WHERE student.classid = class.id;+----+------+-----+---------+----+------+| ID | name | Age | ClassId | ID | Name |+----+------+-----+---------+----+------+|  1 | S1   |  |       1 |  1 | Class One | |  2 | S2   |  |       1 |  1 | Class One | |  3 | S3   |  |       2 |  2 | Second Class |+----+------+-----+---------+----+------+3 rows in Set

Multiple tables can be connected

Mysql> SELECT * FROM student,    join class on student.classid = Class.id,    join score on student.id = SC ore.studentid;+----+------+-----+---------+----+-------+----+------+-------+-----------+| ID | name | Age | ClassId | ID | CNAME | ID | name | Score | StudentID |+----+------+-----+---------+----+-------+----+------+-------+-----------+|  1 | S1   |  |       1 |  1 | Class One  |  1 | language |    |         1 | |  1 | S1   |  |       1 |  1 | Class One  |  2 | Math |    |         1 | |  2 | S2   |  |       1 |  1 | Class One  |  3 | language |    |         2 | |  2 | S2   |  |       1 |  1 | Class One  |  4 | Math |    |         2 | |  3 | S3   |  |       2 |  2 | Class II  |  5 | language |    |         3 | |  3 | S3   |  |       2 |  2 | Class II  |  6 | Math | NULL  |         3 |+----+------+-----+---------+----+-------+----+------+-------+-----------+6 rows in Set

External Connection Left outer connection

The left OUTER join contains all the rows from the left table in a left join, and if there is no match on the right table in the table, the portion of the right table in the corresponding row in the result is all empty (null).

Mysql> SELECT * FROM student left JOIN class on student.classid = Class.id; --or left OUTER join+----+------+-----+---------+------+-------+| ID | name | Age | ClassId | ID   | CNAME |+----+------+-----+---------+------+-------+|  1 | S1   |  |       1 |    1 | Class One  | |  2 | S2   |  |       1 |    1 | Class One  | |  3 | S3   |  |       2 |    2 | Class II  | |  4 | S4   |  22 | NULL    | NULL | NULL  |+----+------+-----+---------+------+-------+4 rows in Set

Right outer connection

The right outer join contains all rows from the right join's left table, and if there is no match in the left table for a row in the correct table, the portion of the left table in the corresponding row in the result is all empty (null).

Mysql> SELECT * FROM student right JOIN class on student.classid = Class.id; --or right OUTER join+------+------+------+---------+----+-------+| ID |   name | age  | classId | id | CNAME |+------+------+------+---------+----+-------+|    1 | S1   |   |       1 |  1 | Class One  | |    2 | S2   |   |       1 |  1 | Class One  | |    3 | S3   |   |       2 |  2 | Class II  | | NULL | NULL | NULL | NULL    |  3 |       | +------+------+------+---------+----+-------+4 rows in Set

Full outer connection

If a row in the left table does not have a match on the right table, the portion of the right table in the corresponding row in the result is all empty (null), and if a row in the right table does not match on the left table, the portion of the left table of the corresponding row in the result is all empty (null). MySQL does not support full JOIN, but we can do a union operation on the results of the left and right connections.

Mysql> SELECT * FROM student left JOIN class on student.classid = Class.id,    UNION,    select * FROM Stud ENT right JOIN class on student.classid = class.id;+------+------+------+---------+------+-------+| ID |   name | age  | classId | ID   | CNAME |+------+------+------+---------+------+-------+|    1 | S1   |   |       1 |    1 | Class One  | |    2 | S2   |   |       1 |    1 | Class One  | |    3 | S3   |   |       2 |    2 | Class II  | |    4 | S4   |   22 | NULL    | NULL | NULL  | | NULL | NULL | NULL | NULL    |    3 |       | +------+------+------+---------+------+-------+5 rows in Set

Natural connection

Natural connections without specifying a connection column, SQL checks two tables for a column with the same name, and the same column name can only have one , and the natural connection is essentially unused.

Mysql> SELECT * FROM student NATURAL JOIN class;+----+------+-----+---------+-------+| ID | name | Age | ClassId | CNAME |+----+------+-----+---------+-------+|  1 | S1   |  |       1 | Class One  | |  2 | S2   |  |       1 | Class II  | |  3 | S3   |  |       2 |       | +----+------+-----+---------+-------+3 rows in Set

You can see that there is only one column ID, because student is automatically merged with the same ID column in Class Two table, which is equivalent to the inner join

SELECT * FROM student INNER JOIN class on student.id = Class.id

What happens if you change the name of the CNAME field named name for the class table?

Mysql> ALTER TABLE class Change cname name VARCHAR (10);  Query OK, 3 rows affectedrecords:3  duplicates:0  warnings:0mysql> SELECT * FROM student NATURAL JOIN class; --because the two tables have two of the same name field, all the results are null empty set

Since student now has two fields with the same name as the class table, all results are empty

MySQL tutorial (quad) SQL connection query