join on详解（On detailed connection）.docVIP

join on详解（On detailed connection） The use of JOIN in the SQL statement The connection (join) keyword in the SQL statement is a more commonly used and less easily understood keyword, and the following example gives a simple explanation that I believe will enlighten you. - build table table1, table2: Create, table, table1 (ID, int, name, varchar (10)) Create, table, table2 (ID, int, score, int) Insert into table1 select 1,lee Insert into table1 select 2,zhang Insert into table1 select 4,wang Insert, into, table2, select, 1,90 Insert into table2 select 2100 Insert, into, table2, select, 3,70 Table , Table1 table2 | | , ID name |id score | 1 Lee |1 90 | 2 Zhang |2 100 | 4 Wang |3 70 | , The following are performed in the query analyzer I. external connections 1. concepts: including left to outer connections, right outer connections or complete external connections 2., left connection: left join or left outer join (1) the result set that is left outward joins includes all rows of the left table specified in the LEFT OUTER clause, not just the rows that the join column matches. If the left table with a row in the right line, no table, then the associated results set all select list row right table are null (null). (2) SQL statement Select * from, table1, left, join, table2, on, table1.id=table2.id Results - - ID name ID score The 1 Lee 190 2 Zhang 2100 4 Wang NULL NULL The Note: all clauses that contain table1 return the corresponding field of the null based on the specified condition. The table2 is displayed not in accord 3. right connection: right join or right outer join (1) the right outer connection is the reverse connection of the left outer connection. All rows of the right table will be returned. If a row in the right table does not have a matching row in the left table, the null value is returned for the left table. (2) SQL statement Select * from, table1, right, join, table2, on, table1.id=table2.id Results - - ID name ID score The 1 Lee 190 2 Zhang 2100 NULL NU