How to use inner join and left join in mysql

Difference

Return differences
1. Inner join only returns rows with equal join fields in the two tables
2. The number of left joins is less than or equal to the left table and The number of records in the table on the right.

Different numbers
1. Inner join returns all records in the left table that are equal to the join fields in the right table.
2. The number of left joins is the same as the number of records in the left table.

The record attributes are different.
1. The record attributes that are insufficient for inner join will be discarded directly.
2. Fill the record attributes with insufficient left join with NULL

inner join scenario

Design two tables:

• channel channel table: with channel id , channel name, etc.

• module module table: module id, module name, channel_id.

When the page displays channels, there may be a new channel but no module. At this time, inner join cannot be used, otherwise the newly added module cannot be queried, so left join

 select channel.* from  channel left join modules on channel.id = modules.channel_id
 where page_id=1
 group by channel.id order by channel.new_sort asc , channel.id desc

1. Page_id refers to which page type is defined in the channel table to distinguish which page type
2. A channel id may have multiple modules to associate, so it needs to be associated with the channel id Grouping
3. New_sort can set the sorting display of channels. In addition, the channel with the same sorting should be displayed first.

In the following cases, newly created channels will not be displayed, and channels without modules will not be displayed either.

select channel.*  from channel inner join modules on channel.id=modules.channel_id
where page_id=1 group by channel.id order by channel.new_sort asc , channel.id desc

Finally: I have some doubts. The channel only displays channel-related information and does not use the associated module information, so I feel that this can meet the needs. I’ll update it if I find any problems later

 select channel.* from  channel
 where page_id=1 order by channel.new_sort asc , channel.id desc

Um ~ To add to the question, the remaining problem above is that the module’s id needs to be queried on the page, so it is associated.

I have encountered an inner join scenario before, and I will add it later~

inner join scenario

Add a new policy table policy_lib, and then create an intermediate table channel_policy record The channel channel is associated with the policy_lib policy table.
Then obviously the sql is written like this

select policy_lib.id,channel_policy.policy_id,channel_policy.channel_id,channel.id  from policy_lib inner join channel_policy on channel_policy.policy_id=policy_lib.id 
inner join channel on channel.id=channel_policy.channel_id

Using inner join, obviously the result query will not be empty. If you use left join, in case there is dirty data in the table, there is data on the left and space on the right, during processing It is obviously unreasonable to need to consider whether you are available when doing so.

The above is the detailed content of How to use inner join and left join in mysql. For more information, please follow other related articles on the PHP Chinese website!