Differences and differences between inner join and outer join query scenarios in MySQL query

created at 09-15-2021 views: 2

Preface

When I write sql query, the most commonly used query is where conditional query. This query is also called inner join query. Of course, there are outer join query outer join, left outer join, right outer join query, commonly used in multiple pairs In multiple relationships, what are their differences and connections?

Inner join

The most commonly used definition of internal connections:

The join result contains only the rows that meet the join conditions and are combined as a result set. The two tables participating in the join should meet the join conditions. Use keywords: INNER JOIN to join multiple tables
That is, return the intersection (shaded) part of the two tables, as shown in the following figure:

Inner join

The following sql query statement

Query the information of all users assigned with department information

That is, the department id is in the user table, and the department table has rows that meet the conditions before it is displayed.

select u.USERNAME, u.MOBILE, u.EMAIL, d.DEPT_NAME
from t_user u inner join
     t_dept d
on u.DEPT_ID = d.DEPT_ID

database

There is also an implicit way of inner join, that is, the specified INNER JOIN keyword does not need to be displayed

Equivalent to

select u.USERNAME, u.MOBILE, u.EMAIL, d.DEPT_NAME
from t_user u,
     t_dept d
where u.DEPT_ID = d.DEPT_ID

Generally, we often use the where keyword to query the connection conditions directly, which is more convenient and simple

Outer join

Left (outer) join left join

definition:

For the left (outer) connection, all the records in the left table will be displayed, while the right table will only display the records that meet the search criteria. The insufficient records in the right table are all NULL, as shown in the following figure:

database

grammar

LEFT JOIN ON
LEFT OUTER JOIN ON

Left join is the abbreviation of left outer join, its full name is left outer join, which is a kind of outer join.

The following sql query statement

Query all employee information (including department information)

select u.USERNAME, u.MOBILE, u.EMAIL,u.DEPT_ID, d.DEPT_NAME
from t_user u left outer join
     t_dept d
on u.DEPT_ID = d.DEPT_ID

equal to 

select u.USERNAME, u.MOBILE, u.EMAIL,u.DEPT_ID, d.DEPT_NAME
from t_user u left  join
     t_dept d
on u.DEPT_ID = d.DEPT_ID

As shown in the figure below, the record whose USERNAME is kenx in the left outer join t_user table has a DEPT_ID of 15, but the record with a DEPT_ID of 15 does not exist in the t_dept table. At this time, we use the left outer join. Therefore, the record can be found, but the values of the fields in the t_dept table are all NULL.

example

Right (outer) join right join

definition:

For the right (outer) connection, all the records in the right table will be displayed, while the left table will only display the records that meet the search criteria. Insufficient records in the left table are all NULL, as shown in the following figure:

Right (outer) join right join

syntax

RIGHT JOIN ON
RIGHT OUTER JOIN ON

Right join is the abbreviation of right outer join. Its full name is right outer join, which is a kind of outer join.

Query the sql statement as follows

select u.USERNAME, u.MOBILE, u.EMAIL,u.DEPT_ID, d.DEPT_NAME
from t_user u right join
     t_dept d
on u.DEPT_ID = d.DEPT_ID

equal to

select u.USERNAME, u.MOBILE, u.EMAIL,u.DEPT_ID, d.DEPT_NAME
from t_user u right outer join
     t_dept d
on u.DEPT_ID = d.DEPT_ID

The following figure is just the opposite of the left outer join. All department information is queried, and the user information fields that do not meet the conditions are all NULL.

 left outer join

Summary

Inner join: the join result only contains rows that meet the join conditions, and the two tables participating in the join should meet the join conditions.

Outer join: The join result contains not only the rows that meet the join conditions, but also the rows that do not meet the conditions. Including left outer connection, right outer connection and full outer connection.

Left outer join: All data rows of the left table are reserved, and the right table keeps the rows that meet the join conditions.

Right outer join: all rows of data in the right table are reserved, and rows that meet the join conditions are reserved in the left table.

Full outer join: left outer join union right outer join, not currently supported in Mysql.

created at:09-15-2021
edited at: 09-15-2021: