MySQL Development join Statement Learning

Inner Connection: innerjoin--Full external link: fully outer left outer connection: Right outer connection to outer: starboard outer Cross Connection
Inner joins, two repeating parts of a table
Full outer joins, two tables all fields
Left outer join, showing all fields on the left table and repeating fields of left and right table
Right outer join, show right table all fields and left table repeating field
Cross join, left table each record multiplied by right table per record, if left table 4 records, right table 3 records, 3*4=12 Records
Null if no data is present in the table

INNER JOIN (inner join) You can choose the intersection of the A and B tables  select field from a Table a table alias inner join B Table B table alias on a. field =b. field;

Left OUTER JOIN: Contains all the data from the table in the right, and the right table of the newly generated table is null when one of the join predicates is not. Expands the information on the left table data to increase the fields in the right table, and when a join verb is not in the right table, the right table of the newly generated table is null. Use the left OUTER join to optimize for not. SELECT select_sxpressionsfrom Table A left OUTER joins table B on A.key=b.keywhere b.key is NULL; Table A does not contain data from table B. SELECT select_sxpressionsfrom Table A left OUTER joins table B on A.key=b.keywhere B.key are not NULL, table A and table B intersect, equivalent to inner join 。

Right OUTER JOIN: Contains all of the data in the table that is left, and when a connection predicate is not in the left table, the newly generated table is null. Expands the information on the right table data, increasing the fields in the left table, and when a connection predicate is not in the left table, the newly generated table's left field is null. Use the right OUTER join to optimize for not. SELECT select_sxpressionsfrom Table A right OUTER joins table B on A.key=b.keywhere a.key is NULL; table B does not contain data from table A. SELECT select_sxpressionsfrom Table A right OUTER joins table B on A.key=b.keywhere A.key are not NULL, table A and table B intersect, equivalent to inner JOI N.

Full JOIN: Is a collection of left OUTER joins and right OUTER joins. Shows all the data in two tables, and when the join verb for one table is not satisfied, the field in the other table is null in the table. MySQL does not support full JOIN Workaround: Merge the results of the left and right joins by Union Select Select_expressionsfrom Table A OUTER JOIN table B on A.key=b.keyuni On Allselect select_expressionsfrom table A right OUTER joins TABLE B on A.key=b.key; Union will automatically delete duplicates, brush the selection of fields to be aware of, if the brush selected two field values are identical, there will be one left. The Union all and union differ in that union all lists all the data for the results of the left and right two queries, regardless of whether the data values are duplicates or not.

Cross JOIN: The Cartesian connection, if a and B are two sets, their crossing connection is recorded as a X B. Each record of one table is connected to all records of another table, forming a table of a.length*b.length data items. (No on required) SELECT Select_expressionsfrom table A Cross JOIN table B;

Join Optimization sub-query technique: General subquery notation: (data hours, not much impact, if the volume of data is large, then consume a large number of queries) Select A.user_name, A.over, (select over from User2 where A.user_ name = B,user_name) as Over2from user1 A; If the records of these two tables are quite large then this subquery is equivalent to one subquery for each record of a. Join optimization (left connection): Select A.user_name, A.over, b.over from User1 Aleft joins User2 b on a.user_name = B.user_name

MySQL Development join Statement Learning