Keyword about left, right, inner

For the left, right, and inner keywords, pay attention to the details. The details are very important! For example, remember to add comments when writing code! 1. Typographical editing-when writing SQL statements, especially writing batches, be sure to shrink them! This is beautiful and looks convenient. Although it is a detail, it is very important. Of course, the detailed design of SQL should also be done! 2. Table association when it comes to table association, we have to say left, inner, and right keywords. (Here we will only introduce left and right, which is similar.) eg: A is the primary table (primary key: user_code) B is the sub table (field user_code is the foreign key and is subject to the user_code constraints in table, table B user_code cannot be empty.) 2.1 discard from A, B where. user_code = B. user_code is written in a way that involves a large number of associated tables, the association conditions will be complex, inconvenient to read, and not beautiful. Sometimes you have to read what you write for a long time. What's more, others can read it... even if you only write the association between two tables, you cannot write it like this, because this is a good habit, and this is the details. It should be written as follows:

from A inner join B on A.user_code = B.user_code

 

2.2 differences between left inner (Table B is a sub-table on the premise that select count (1 )......)

A left join B on. user_code = B. the data such as user_code must be greater than or equal to the data in Table B left join A on. user_code = B. the data such as user_code must be equal to the data in Table B, A inner join B on. user_code = B. if the user_code data in Table B is not empty, it is equal to the data in table B.

 

2012-5-19 add by xingshi89 // add the following content to this article 3. in most cases, in order to avoid project errors caused by inaccurate raw data provided by the customer, in daily Project Creation, only the logical primary and Foreign keys are set for the table, there is no mandatory constraint in the database. In this way, the relationship between table A and table B is just A theory, rather than an actual application. Therefore, the condition is changed to the following: eg: A is the primary table (primary key: user_code) B is A sub-table (also with the field user_code). Table B of Table A is designed in this way, but the primary foreign key constraint is not set. Table A user_code is A primary key, so it cannot be blank, table B user_code can be empty. If table A contains 20 data records and table B contains 40 data records

select count(1) from A left join B on A.user_code = B.user_code 

The resulting data will be (20 ~ 59) if there are at least 20 pieces of data in Table B, 20 or fewer pieces of data can match the data in table. A maximum of 59 data records are returned when the values of user_code in the 40 data records of Table B are the same and the values of user_code in Table A are the same.

select count(1) from B left join A on A.user_code = B.user_code 

The data obtained is ~ 59) Same principle as above