“This is the 21st day of my participation in the Gwen Challenge in November. See details: The Last Gwen Challenge in 2021”

preface

Recently, when operating database, it is necessary to associate query with multiple tables. In many businesses, it is also necessary to associate query with multiple tables. I believe that the first idea is to use JOIN link query, so are you familiar with JOIN link query?

JOIN

In WRITING SQL, we always use Joins when we operate on multiple database tables. In practical development, JOIN is often used for database table joint check. The common methods include INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN, OUTER JOIN, and so on. The following will be combined with the actual data demonstration.

This demo will use two database tables in MySQL. SQL statement for creating a table:

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  `create_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4;

CREATE TABLE `user_subject` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `uid` int(11) DEFAULT NULL,
  `subject` varchar(255) DEFAULT NULL,
  `score` int(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=19 DEFAULT CHARSET=utf8mb4;
Copy the code

INNER JOIN

INNER JOIN is a more commonly used INNER JOIN method in SQL, which can also be called equivalent JOIN. Its main function is to obtain the equal data values in two or more tables. The general syntax format is:

SELECT * FROM table A AS A INNER JOIN table B AS B ON a.id= B.idCopy the code

The SQL used in this test is as follows:

SELECT * FROM `user` as a INNER JOIN user_subject as b ON a.id=b.uid;
Copy the code

After the INNER JOIN query, it can be found that ai in the user table is not queried, and the data with ids 16, 17 and 18 in the user_Subject table are not returned. Only columns in the USER table with the same ID as the UID in the USER_Subject table are returned.It can be concluded that the red part in the figure below is the data information queried by INNER JOIN.

LEFT JOIN

A LEFT JOIN retrieves all records in the LEFT table, even if there is no matching record in the right table. The general syntax format is:

SELECT * FROM table AasA LEFT JOIN table Bas b ON a.id=b.uid;
Copy the code

The SQL used in this test is as follows:

SELECT * FROM `user` as a  LEFT JOIN  user_subject as b ON a.id=b.uid;
Copy the code

You can see that all the data in the user table is listed, while only the same data in the user_Subject table is listed. The degree in the user table is also listed in the table where the user_subject has no data.A LEFT JOIN can be graphically understood as containing all the data in table A and including the data in table A that has the same value as table B.

RIGHT JOIN

RIGHT JOIN is the opposite of LEFT JOIN. It is used to retrieve all records in the RIGHT table, even if there is no matching record in the LEFT table. The general syntax format is:

SELECT * FROM table AasJOIN table Bas b ON a.id=b.uid;
Copy the code

The SQL used in this test is as follows:

SELECT * FROM `user` as a  RIGHT JOIN user_subject as b ON a.id=b.uid;
Copy the code

By executing the test SQL, you can see that all data in table USER_Subject is already listed, and data in table User that matches table USER_Subject is also listed.

You can graphically understand the RIGHT JOIN to include all the data in table B and to include the data in table B with the same value as in table A.

FULL JOIN

A FULL JOIN is a FULL JOIN that returns data information about the row where a match exists in one of the tables. The general syntax format is:

SELECT * FROM table AasA FULL JOIN table Bas b ON a.id=b.uid;
Copy the code

The SQL used in this test is as follows:

SELECT * FROM `user` as a  FULL JOIN user_subject as b ON a.id=b.uid;
Copy the code

conclusion

Thank you for your reading. I hope you like it. If it is helpful to you, welcome to like it. If there are shortcomings, welcome comments and corrections. See you next time.

About the author: [Little Ajie] a love tinkering with the program ape, JAVA developers and enthusiasts. Public number [Java full stack architect] maintainer, welcome to pay attention to reading communication.