case study
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 ULEFT JOIN USER_ACITION a on u.id = a.user_id
Result: ID | Name | Action -------------------------------- 1 | LIBK | JUMP 1 | LIBK | Kick 2 1 | LIBK | JUMP 3 2 | Zyfon | Run 4 3 | Daodao | NULL 5
Analysis: Note that there is a user_id = 4 in the user_action, an action = Swim record, but there is no appearance in the results,
And the ID = 3 in the USER table, Name = DaoDao users have no corresponding records in the user_action, but it appears in the result set.
Because now is Left Join, all work is subject to Left.
Results 1, 2, 3, 4 are recorded in the left table and in the right table, 5 is only the record of the left table, not in the right table
in conclusion:
We can imagine that Left Join is read from the left table, selects all the Table Record (N) matching the ON match, forms N records (including repetitive rows, such as: Results 1 and Results 3 ), If there is no table that matches the ON condition on the right, the field of the connection is null. Then continue to read one.
Extended:
We can use the right table without ON matching to display NULL rules to find all the records in the left table, not in the right table, and note that the column used to determine must be NOT NULL. Such as:
SQL:
SELECT ID, NAME, ACTION FROM User AS U
LEFT JOIN USER_ACITION a on u.id = a.user_id
Where a.user_id is NULL
(note:
1. The column value is NULL should use is NULL without using = null
2. Here 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) 2. Inner Join, (comma) in semantics is equal 3. When MySQL retrieves information from a table, you can prompt it to choose Which index is indexed. This feature will be useful if EXPLAIN displays MySQL using the error index in the list of possible index. By specifying use index (key_list), you can tell Mysql to find records in the table in the table using the most suitable index in the possible index. Optional two selection one syntax ignore index can be used to tell Mysql does not 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> selection * 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; The following is the principle of mysql about Join Matter instructions
5.2.6 How mysql Optimises Left Join and Right Join
A Left Join B in MySQL IS IMPLEMENTED AS FOLLOWS:
The table B is set to be dependent on table A and all tables that A is dependent on. The table A is set to be dependent on all tables (except B) that are used in the LEFT JOIN condition. All LEFT JOIN conditions are moved to the WHERE clause. All standard join optimisations are done, with the exception that a table is always read after all tables it is dependent on. If there is a circular dependence then MySQL will issue an error. All standard WHERE optimisations are done. If there is a row in A that matches the WHERE clause, but there wasn "If you use LEFT JOIN to find rows that do not exist in some table and you have the following test: column_name IS NULL in the WHERE part, where column_name is a column that is declared as NOT NULL, then MySQL will stop searching after more rows (for a particular key combination) after it has found one row that matches the LEFT JOIN condition. RIGHT JOIN is implemented analogously as LEFT JOIN.The table read order FORCED B Y Left Join and Straight Join Will Help The Join Optimiser (Which Calculates In Which Order Tables Should Be Joined) To do its Work Much More Quickly, As There Are Fewer Table Permutations to Check.
Note That The Above Means That if you do a query of type:
Select * from a, b left join c on (c.key = a.key) Left join d (d.key = a.key) Where b.key = d.key
Mysql Will Do A Full Scan On B As The Left Join Will Force It To Be Read Before D.
THE FIX IN this case is to change the query to:
Select * from b, a left join c on (c.key = a.key) Left join d (d.key = a.key) Where b.key = d.key
