Internal connection, left connection, right connection, full external link

( rookie One )

Recently, when looking at other people's code, always encountered such as join on the SQL statement, in class, the teacher also carefully said,

It's also easy to come up with a separate look, but it's hard to read in a complex SQL statement.

Just not very busy today, check the information in summary.

Work, we are accustomed to querying only one table of data, if the business needs to query the data in another table,

We are often accustomed to traversing the data of the query and then querying the other table based on the foreign key field, so there is no problem.

But when we need to correlate 3 tables or even 4 tables, or some very complex SQL statements in some large projects

We found that this makes the code for the business layer very, very complex.

At this point, the association table query becomes very important.

Basic definition:

Left join: Returns records that include all the records in the left table and the equivalent of the join fields in the right table.

Right join: Returns records that include all records in the right table and the connection fields in the left table.

Inner JOIN (equivalent connection): Returns only rows that have equal join fields in two tables.

Full join: Returns all records in the left and right tables, and records that are equal to the join fields in the left and right tables.

Here's an example:

Table A

ID Name

1 Xiao Wang

2 Xiao Li

3 Xiao Liu

b table

ID A_ID Job

1 2 Teacher

2 4 Programmers

　　

Select A.name,b.job from a a inner joins b b on a.id=b.a_id

You can only get one record.

Miss Xiao Li

　　Select A.name,b.job from a a LEFT join b b on a.id=b.a_id

Three records

Xiao Wang Null

Miss Xiao Li

Xiao Liu Null

　　Select A.name,b.job from a-a right join B-B on a.id=b.a_id

Two records

Miss Xiao Li

NULL programmer

　　Select A.name,b.job from a full join b b on a.id=b.a_id

Four piece of data

Xiao Wang Null

Miss Xiao Li

Xiao Liu Null

NULL programmer

The specific usage of the above depends on your business needs, such as query more people's occupation, no job set to NULL, left connection is undoubtedly the most

Correct, such as querying all professions for the person, no corresponding person is set to 0. Right connection is more correct.

Of course at work we will see a number of tables associated with the case, this time we will write more join on the statement, specifically which connection to press

Specific business.

　　

1 <?XML version= "1.0" encoding= "UTF-8"?>2 <!DOCTYPE Mapper Public "-//mybatis.org//dtd mapper 3.0//en" "Http://mybatis.org/dtd/mybatis-3-mapper.dtd ">3 <!--Inventory Mapping -4 <Mappernamespace= "Com.hebg3.mobiledealer.modules.client.store.order.dao.OrderDao">5 6     <SQLID= "Tordercolumns">7a.ID as "id",<!--PRIMARY Key -8A.order_no as "OrderNo",<!--Order Number -9a.t_customer_id as "Customer.id",<!--Customer number -Tena.sys_office_id as "Companyoffice.id",<!--Company Number - OneA.order_date as "OrderDate",<!--Order Date - AA.document_status as "Documentstatus",<!--Order Status - -A.send_date as "Senddate",<!--Send Time - -a.open_id as "OpenId",<!--numbering - theA.create_by as "Createby.id",<!--Founding People - -A.create_date as "CreateDate",<!--Settling Time - -A.update_by as "Updateby.id",<!--Update Person - -A.update_date as "Updatedate",<!--Update Time - +A.remarks as "Remarks",<!--Notes - -A.del_flag as "Delflag",<!--Remove Flag - +a.t_sales_entry_id as "Salesentry.id",<!--Sales Tracking number - A Se.orderno as "Salesentry.orderno", atC.name as "Customer.name"<!--Customer Name - -     </SQL> -  -     <SQLID= "Torderjoins"> -JOIN t_customer_relation cr on cr.t_customer_id = a.t_customer_id<!--Associating customer Relationships - -JOIN T_customer C on c.id=a.t_customer_id<!--Associating customer Relationships - inLeft JoIN t_sales_entry se on se.id=a.t_sales_entry_id<!--Associate a sales order - -     </SQL> to  +  -     <!--Get order Information list by condition - the     <SelectID= "Findpageorder"Resulttype= "Torder"> * SELECT $         <includerefID= "Tordercolumns" />Panax Notoginseng From T_order a -         <includerefID= "Torderjoins" /> the         <where> + A.del_flag = #{del_flag_normal} A             <ifTest= "Userid!=null and userid!="> the and Cr.sys_user_id=#{userid} +             </if> -             <ifTest= "Id!=null and id!="> $ and A.id=#{id} $             </if> -             <if -                 Test= "Companyoffice!=null and Companyoffice.id!=null and companyoffice.id!=" "> the and Cr.sys_office_id=#{companyoffice.id} -             </if>Wuyi             <ifTest= "Documentstatus!=null and documentstatus!="> the and A.document_status =#{documentstatus} -             </if> Wu             <offTest= "Documentstatuslist! = null"><!--find based on document status - - and A.document_status in About                 <foreachItem= "Item"Index= "Index"Collection= "Documentstatuslist"Open="("Separator=","Close=")"> $ #{item} -                 </foreach> -                   -             </if> A             <ifTest= "page!=null and page.groupby! = null and Page.groupby! =" "> + GROUP BY ${page.groupby} the             </if> -         </where> $         <Choose> the             < whenTest= "page!=null and Page.orderby! = null and Page.orderby! =" "><!--Sort by Sort field - the ORDER by ${page.orderby} the             </ when> the             <otherwise> - ORDER by A.create_date DESC in             </otherwise> the         </Choose> the  About     </Select>

Rows 26 through 30 are complex queries that correlate multiple tables. I mentioned in the original blog dynamic query, we can also learn from it.

　　

Above

Internal connection, left connection, right connection, full external link