Home>

left join main table

The main table mentioned here refers to which table mysql uses for querying in the join query.For example, in a left join query, the left table is generally the main table.But this is just a matter of experience,Many times empiricism is unreliable,To illustrate,Let's start with an example.Create two demo tables categories and posts:

create table if not exists `categories` (
`id` int (10) unsigned not null auto_increment,`name` varchar (15) not null,`created` datetime not null,primary key (`id`)
);
create table if not exists `posts` (
`id` int (10) unsigned not null auto_increment,`category_id` int (10) unsigned not null,`title` varchar (100) not null,`content` varchar (200) not null,`created` datetime not null,primary key (`id`),key `category_id` (` category_id`)
);

First pay attention to the index of each table,Will be used laterRemember to insert a little test data,Not too much,But how can I get more than two lines?Then execute the following sql:

explain select *
from posts
left join categories on posts.category_id=categories.id
where categories.id="an existing id"
order by posts.created desc
table key extra
categories primary using filesort
posts category_id using where

In the result of explain,The table represented by the first row is the main table,So in this query categories is the main table, and in our experience,In a left join query, the left table (posts table) should be the main table.This creates a fundamental contradiction,The reason why mysql handles this,Because in our where part, the query conditions are filtered according to the fields of the categories table.And there is a proper index on the categories table.Therefore, the categories table as the main table in the query is more conducive to reducing the result set.

Then why is using filesort in the explain result?This is because the main table is the categories table, the slave table is the posts table, and we use the fields of the slave table to order by. This is usually not a good choice.It is better to change to the main table field,If demand is limited,Cannot be changed to a field of the main table,Then you can try adding the following indexes:

alter table `posts` add index (` category_id`, `created`);

When running SQL again, there will be no using filesort. This is because when the main table categories are connected to the posts from the table through category_id, the sorted posts result can be directly obtained through the index.

Once the subject table is subjectively wrong,How to adjust the index may not get efficient SQL, so when writing SQL, for example, when writing a left join query, if i want the left table to be the main table,Then we must ensure that the query conditions in the where statement use as many left table fields as possible,Furthermore, once the master table is determined,It is also best to go through order by only the main table fields.

left join query efficiency analysisuser table:

id | name
---------
1 | libk
2 | zyfon
3 | daodao
user_action table:
user_id | action
---------------
1 | jump
1 | kick
1 | jump
2 | run
4 | swim

sql:

select id, name, action from user as u
left join user_action a on u.id=a.user_id
result:
id | name | action
--------------------------------
1 | libk | jump ①
1 | libk | kick ②
1 | libk | jump ③
2 | zyfon | run ④
3 | daodao | null ⑤

analysis:

Note that there is also a record of user_id=4, action=swim in user_action, but it does not appear in the results.

The user in the user table with id=3, name=daodao has no corresponding record in user_action.But it appeared in the result set

Because it is a left join, all work is based on left.

Results 1, 2, 3, and 4 are records in both the left and right tables.5 is only on the left table,Not on the right table

in conclusion:

We can imagine that left join works like this

Read one from the left table,Select all the right table records (n) that match on to connect.Form n records (including duplicate lines,Example:Results 1 and 3),

If there is no table on the right that matches the on condition,The connected fields are all null.

Then proceed to the next one.

Extension:

We can use the right table to display the rule of null without on matching, to find all in the left table,Records not on the right table, Note that the column used to judge must be declared as not null.

Such as:

select id, name, action from user as u
left join user_action a on u.id=a.user_id
where a.user_id is null

(Note:1. column values ​​should be null instead of=null

2.Here the a.user_id column must be declared as not null)

result:
id | name | action
--------------------------
3 | daodao | null
-------------------------------------------------- ------------------------------

tips:

1. on a.c1=b.c1 is equivalent to using (c1)

Inner join and, (comma) are semantically equivalent

3. When mysql is retrieving information from a table,You can indicate which index it chose.

If explain shows that mysql uses the wrong index from the list of possible indexes,This feature will be useful.

By specifying the use index (key_list), you can tell mysql to find the row in the table using one of the most suitable indexes possible.

The optional alternative syntax ignore index (key_list) can be used to tell mysql not to use a specific index.

4. Some examples:

mysql>select * from table1, table2 where table1.id=table2.id;
mysql>select * from table1 left join table2 on table1.id=table2.id;
mysql>select * from table1 left join table2 using (id);
mysql>select * from table1 left join table2 on table1.id=table2.id
->Left join table3 on table2.id=table3.id;
mysql>select * from table1 use index (key1, key2)
->Where key1=1 and key2=2 and key3=3;
mysql>select * from table1 ignore index (key3)
->Where key1=1 and key2=2 and key3=3;
  • Previous Some basic exception handling tutorials in MySQL stored procedures
  • Next Java observer pattern example