Data table Association left join / right join / inner join / full join / cross join

1. Connection introduction

At present, there are five association methods: left join / right join / inner join / full join / cross join. Different association effects will be presented according to different association methods.

• A left join B: all records in table a are reserved, and all records not associated in table B are discarded;

  

• A right join B: all records in table B are reserved, and all records not associated in table a are discarded;

  

• A inner join B: take the intersection of the two, which means that the data of the result set must exist in both tables a and B! In actual use, the inner can be omitted, that is, it can be written as A join B;

  

• A full join B: keep all records of table a and table B;

  

• A cross join B: cross join is special. It means that table a and table B are Cartesian products, and there is no need to follow the condition of on column A = column B.
  Suppose that there are m records in table A and n records in table B, then there will be m*n records in the final correlation result. We will further understand this through examples.

II. Example comparison

1. data preparation

• Create table statement:

CREATE TABLE kwan.`score` (
  `student_id` int(8) DEFAULT NULL,
  `score` int(8) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE kwan.`student` (
  `student_id` int(8) NOT NULL,
  `student_name` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`student_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

• Insert statement:

INSERT INTO kwan.`student`
VALUES
	( 1, 'Xiao Ming' ),
	( 2, 'Xiao Wang' ),
	( 3, 'Xiao Zhang' ),
	( 4, 'petty thief' );


INSERT INTO kwan.`score`
VALUES
	( 1, 100 ),
	( 2, 60 ),
	( 3, 98 ),
	( 5, 78 );

• select * from kwan.student;
  

  

• select * from kwan.score;
  

  

Effect comparison

left join

• SQL statement:

  SELECT
  	*
  FROM
  	kwan.score
  LEFT JOIN kwan.student ON
  	score.student_id = student.student_id;
  

• In the above statement, score will be used as the main table and all records of the score table will be retained. If there is another record (4, 'Xiao Li') in the student table that is not associated with the score table, it will be discarded!

  

right join

• SQL statement:

  SELECT
  	*
  FROM
  	kwan.score
  RIGHT JOIN kwan.student ON
  	score.student_id = student.student_id;
  

• The above statement will use the student table as the main table. The records discarded in the previous example (4, 'Xiao Li') are retained, while the records discarded in the score table (5, 78) are discarded.

  

• If the sudent table and the score table of the above statement are interchanged, the same effect will be achieved:

  SELECT
  	*
  FROM
  	kwan.student
  LEFT JOIN kwan.score ON
  	score.student_id = student.student_id;
  

inner join

• SQL statement:

  SELECT
  	*
  FROM
  	kwan.score
  INNER JOIN kwan.student ON
  	score.student_id = student.student_id;
  

• After the inner join, the empty records will be discarded, and only the records contained in both tables will be retained;

  

full join

• SQL statement:

  SELECT
  	*
  FROM
  	kwan.score
  FULL JOIN kwan.student ON
  	score.student_id = student.student_id;
  

• be careful:

  Full Join is not supported in Mysql. If you need to use Full Join, you can use the following statements (union the results of left join and right join):

  SELECT
  	*
  FROM
  	kwan.score
  LEFT JOIN kwan.student ON
  	score.student_id = student.student_id
  UNION 
  SELECT
  	*
  FROM
  	kwan.score
  RIGHT JOIN kwan.student ON
  	score.student_id = student.student_id;
  

• All records of the two tables will be retained, and those not associated will be blank;

• 

cross join

The difference between cross join and left join / right join / inner join / full join is that cross join does not need Association conditions (note that it is not necessary, but not impossible)! It's just a Cartesian product of two tables.

• SQL statement:

  SELECT
  	*
  FROM
  	kwan.score
  CROSS JOIN kwan.student;
  

•