Shulou(Shulou.com)05/31 Report--

This article is about the difference between left join/right join/equivalent join in MySQL. Xiaobian thinks it is quite practical, so share it with everyone for reference. Let's follow Xiaobian and have a look.

Since my career, I have found that many technicians and colleagues have consulted the author or on the network about how to write LEFT JOIN and its characteristics, and the equivalent connection is generally no problem. I wrote a version of Microsoft SQL Server a few years ago. This time, I specially wrote a MySQL version. I hope that the technicians who have read it can play a role in solving puzzles.

1. Syntax of three kinds of connections

For the convenience of more technical friends to quickly understand and understand, we only discuss the case of connecting two table objects, and the operation principle of connecting more than two table objects is the same.

LEFT JOIN

SELECT M.columnname……,N.* columnname….. FROM left_table M LEFT JOIN right_table N ON M. columnname_join=N. columnname_join AND N. columnname=XXX WHERE M.columnname=XXX…..

RIGHT JOIN

SELECT M.columnname……,N.* columnname….. FROM left_table M RIGHT JOIN right_table N ON M. columnname_join=N. columnname_join AND M. columnname=XXXWHERE N.columnname=XXX…..

equijoin

SELECT M.columnname……,N.* columnname….. FROM left_table M [INNER] JOIN right_table N ON M. columnname_join=N. columnname_joinWHERE M.columnname=XXX….. AND N.columnname=XXX….

or

SELECT M.columnname……,N.* columnname….. FROM left_table M , right_table NWHERE M. columnname_join=N. columnname_join AND M.columnname=XXX….. AND N.columnname=XXX….

Note: Note the bold red font in the SQL syntax above, and the changes in the three different connection syntax.

Characteristics of three types of connections

LEFT JOIN

ON clause join condition, used to join equivalent records in 2 tables together, but does not affect the number of record sets. If a record in the table left_table cannot be found in the table right_table, the record is still displayed in the recordset clock, and only the column value displayed in the table right_table needs to be replaced with NULL;

ON sentence connection condition table right_table. columnname=XXX is used to control whether the right_table has column values that meet the requirements or is displayed in the query column by replacing NULL, without affecting the number of recordsets;

The WHERE sentence controls whether the record meets the query requirements, and if it does not, it is filtered out;

Summary: The ON clause controls whether the column values of the right_table match the display, or if they do not match, they are replaced with NULL, which does not affect the record set that ultimately meets the query requirements; the WHERE clause controls which records are displayed in the final record set.

RIGHT JOIN

ON sentence connection condition, used to connect records of equal value in the two tables together. If a record in the table right_table cannot be found in the table left_table, the table left_able needs to replace the value of the column displayed in the query with NULL;

left_table. columnname=XXX is used to control whether the left_table has column values that meet the requirements or is displayed in the query list by replacing them with NULL;

The WHERE sentence controls whether the record meets the query requirements, and if it does not, it is filtered out;

Summary: The ON clause controls whether the column values of the left_table match the display or do not match and replace them with NULL, which does not affect the record set that finally meets the query requirements; the WHERE clause controls which records are displayed in the final record set. We'll see that LEFT JOIN and RIGHT JOIN are similar, only matching the table to the left or right of the join keyword.

equijoin

The ON sentence connection condition is no longer the same as the left connection or right connection. In addition to being a matching condition for records in the 2 tables, it also plays a role in filtering records. If the records in the left_table cannot find corresponding records in the right_table, they will be filtered out.

WHERE sentence, whether it is related to the restriction conditions on the left_table and the right_table of the table, or the conditions related to the connection of the two tables, will filter the record set and select the records that do not meet the requirements;

Test environment for connection syntax

test table structure

CREATE TABLE left_table(ID INT UNSIGNED NOT NULL AUTO_INCREMENT, Username VARCHAR(40) NOT NULL, Birthday DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00', CityID SMALLINT NOT NULL DEFAULT 0, CreatDate TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00', AlterDate TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY(ID), KEY idx_username(Username) )ENGINE=InnoDB CHARACTER SET 'utf8' COLLATE 'utf8_general_ci'; CREATE TABLE right_table(UID INT UNSIGNED NOT NULL , CollectNum MEDIUMINT NOT NULL DEFAULT 0, BuyNum MEDIUMINT NOT NULL DEFAULT 0, SearchNum MEDIUMINT NOT NULL DEFAULT 0, CreatDate TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00', AlterDate TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY(UID) )ENGINE=InnoDB CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';

Data for testing

Repeat INSERT*** VALUE *** statement 10 times:

Thank you for reading! About "MySQL left connection/right connection/equivalent connection what is the difference" This article is shared here, I hope the above content can be of some help to everyone, so that everyone can learn more knowledge, if you think the article is good, you can share it to let more people see it!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.