Advanced Learning tutorials for Mysql's left join table joins _mysql

Main Table of LEFT join

The main table mentioned here refers to which table MySQL is querying in the connection query. For example, in a left join query, the left-hand table is generally the main table, but this is just a rule of thumb, many times empirical is unreliable, in order to illustrate the problem, first of all, build two demo table categories and posts:

CREATE TABLE IF not EXISTS ' categories ' (
' id ' int (a) unsigned not NULL auto_increment,
' name ' varchar (?) not NU LL,
' created ' datetime not NULL,
PRIMARY KEY (' id ')
);

CREATE TABLE IF not EXISTS ' posts ' (
' id ' int (a) unsigned not NULL auto_increment,
' category_id ' int (a) unsigned Not null,
' title ' varchar (m) not null,
' content ' varchar ' is not NULL,
' created ' datetime not NULL,
   primary key (' id '),
key ' category_id ' (' category_id ')
;

First notice the index of each table, later will use, remember to insert a little bit of test data, not too much, but how to get more than two lines, and then execute the following sql:

EXPLAIN SELECT * from
posts left
JOIN categories on posts.category_id = categories.id
WHERE categories.id = ' An already existing ID ' ORDER by
posts.created DESC

Table   key     Extra
categories PRIMARY   using Filesort
posts   using where

In the result of explain, the table in the first row is the primary table, so categories is the primary table in this query, and in our experience, the left table (the posts table) should be the primary table in the left-hand join query, which creates a fundamental contradiction, which is how MySQL handles Because in our where, the query conditions are filtered according to the fields of the Categories table, and the exact categories table has an appropriate index, it is more advantageous to reduce the result set when querying the Categories table as the primary table.

What is the using Filesort in the explain result? This is because the primary table is a Categories table, from the table is the posts table, and we use the field from the table to order by, which is usually not a good choice, preferably converted to the Main table field, if the requirements are limited to the main tables, you can try to add the following index:

ALTER TABLE ' posts ' ADD INDEX (' category_id ', ' created ');

There will be no using filesort when running SQL, because the primary table categories can then get sorted posts results directly through the index when posts from the table through the category_id connection.

Subjectively, once the master table is mistaken, it is possible to adjust the index without efficient SQL, so when writing SQL, for example, when writing a LEFT join query, if you want the left-hand table to be the primary table, make sure that the query condition in the WHERE statement uses as much of the left table field as possible, and that, once the master table is determined, It's also best to go through the Main table field only.

Analysis on the efficiency of left JOIN query
User 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 in u.id = a.user_id result

:
ID | name | acti On
--------------------------------
1 | libk | Jump①
1 | libk | Kick②
1 | libk | Jump③
2 | zyf On | Run④
3 | daodao | null⑤

Analysis:
Note that there is also a user_id=4, Action=swim record in the user_action, but not in the results,
Id=3 in the user table, Name=daodao users do not have a corresponding record in user_action, but they appear in the result set
Because now is the left join, all work is left.
Results 1,2,3,4 are both in the left table and on the right table record, 5 is only on the left table, not on the right table record

Conclusion:
We can imagine that the left join works like this.
Read one from the left table and select all the right table records (n bars) to match on to form N records (including duplicate rows, such as: result 1 and result 3),
If there is no table on the right that matches the on condition, the connected fields are null.
Then read on to the next one.

Extended:
We can use the right table without on match to show the law of NULL, to find all the records in the left table, not the right table, note that the column to be judged must be declared 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. A column value of NULL should be null and cannot be used with =null
2. Here the a.user_id column must be declared not NULL

Result:
ID | name | action
--------------------------
3 | daodao | NULL

--------------------------------------------------------------------------------

Tips:
1. On a.c1 = B.C1 equivalent to using (C1)
2. INNER JOIN and, (comma) are semantically equivalent
3. When MySQL retrieves information from a table, you can indicate which index it chooses.
This feature is useful if EXPLAIN shows that MySQL uses the wrong index in the list of possible indexes.
By specifying use index (key_list), you can tell MySQL to find a record row in a table using the most appropriate index in a possible index.
An optional two-choice 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;