This chapter briefly describes the problem of multi-table queries encountered during instance development.
It mainly describes
Select A. *, B. *, C. * from A, B, C
And
Select A. *, B. *, C. * from Table A inner join table B on A. id = B. id inner join Table C on A. code = C. code
Difference
Recently, we encountered a requirement that the page set display information of three associated tables.
Because there are many fields in the actual project, the simplified description is as follows:
Table A contains the UserName, CardCode, and ItamCode fields.
Table B contains the CardCode and CardName fields.
Table C contains the ItamCode and ItamName fields.
The user interface must contain UserName, CardName, and ItamName in a dataset.
Although I have learned the problem of SQL inline and external connection, the previous project basically encountered the association implementation of two tables. Now it is the first time that I have used the association implementation of more than two tables. After looking for information on Baidu, I found that the original basic principles were the same. Haha, I only joined the company last year and have insufficient experience. Please don't laugh!
Er, if the reader is not familiar with SQL basic inline and external knowledge for the time being, please feel free to contact du Niang. I will not comment on it here. Let's put it short. Now let's get started ......
We found that there are two ways to implement the above functions:
Select A. UserName, B. CardName, C. ItamName from A, B, C
And
Select A. UserName, B. CardName, C. ItamName from Table A inner join table B on A. CardCode = B. CardCode inner join Table C on A. ItamCode = C. ItamCode
(If the field is differentiated, You Can slightly write the table name)
As far as the above syntax is concerned, the two implementation methods have the same effect, but if you really want to elaborate on the advantages and disadvantages, the second implementation method is more practical.
Using inner join (full outer join), we can not only convert left join (left Outer join) and right join (right Outer join) as needed, but also indicate the order of combination, in particular, it supports the convenience of SQL optimization.
Another one is that when a field of mine can be null, the value obtained by the first query method is actually incomplete. For example, in my above example, when the ItanName of table C is not required, we can only achieve the desired effect through left join.
If you are interested, you can verify the differences between the two methods.