Introduction to MySQL LEFT JOIN

Table A

1 Aid Adate

2 1 A1

3 2 A2

4 3 A3
Table B

1 Bid bdate

2 1 B1

3 2 B2

4 4 B4

1

The code is as follows	Copy Code
SELECT * from a LEFT join B on a.aid = B.bid

First remove all the data from table A, then add the data that matches a and B.

At this point, the removal is:

1 1 A1 B1

2 2 A2 B2

3 3 A3 Null characters
There is also the right join

Refers to the first to remove all the data in table B, and then add a and b matching data.

At this point, the removal is:

1 1 A1 B1

2 2 A2 B2

Left JOIN performance

1 examples

The code is as follows	Copy Code
SELECT Distinct count (' goods_id ') as num, g.goods_id,g.goods_name from Ms_goods as G-left join Ms_order_goods as OG on g.goods_id = og.goods_id GROUP BY goods_id

Show Row 0-29 (1,475 total, query takes 0.0167 seconds)

2 examples

The code is as follows	Copy Code
SELECT Distinct count (' goods_id ') as num, g.goods_id,g.goods_name from Ms_goods as G, ms_order_goods as og where g.good s_id = og.goods_id GROUP BY goods_id

Show Row 0-29 (257 total, query takes 0.0088 seconds)

Instance

The table structure is as follows:

Mdate storecode goodscode tostorecode goodsamount Goodsflag
2005-12-12 001 101 888 2 3
2005-12-13 001 101 3 1
2005-12-15 001 101 1 2

The meaning of three records is:
2005-12-12 moved from 001 to 888 2 pieces
2005-12-13 from 001 sales of 3 Pieces
2005-12-15 Warehouse store 001 purchase 2 Pieces

Out of report
storecode  goodscode  stockamount   inamount     OutAmount     Sellamount
001              101                         3                   0                       2             1
Note: Stockamount Purchase quantity    inamount Move quantity      outamount emigration quantity     Number of Sellamount sales

code is as follows

copy code

Select & nbsp;   Storecode,     goodscode,     stockamount = SUM (case Goodsflag When 2 then Goodsamount else 0 end),     inamount    = SUM (case Goodsflag when 4 then go Odsamount else 0 end),     outamount   = SUM (case Goodsflag when 3 then goodsamount else 0 en d),     sellamount  = SUM (case goodsflag when 1 then goodsamount else 0 ") from  &N bsp;  Table Group by     storecode,goodscode

Example 3
Po_order_det table
ID ma_id QTY
30 #21钢
#22钢 40
30 #23铝

St_conver table
ID QTY
01 20
02 10
Requires return set QTY = Po_order_det. Qty-st_conver. QTY and Po_order_det.id=st_conver.id

ID ma_id QTY
10 #21钢
#22钢 30
30 #23铝

The code is as follows	Copy Code
Select A.order_id,a.id,a.ma_id,a.qty,isnull (b.qty,0) qtyy, IsNull (a.qty-b.qty, 0) Qtyx From Po_order_det a LEFT join St_conver b On A.id=b.id and A.filid=b.filid and A.id=b.id and a.order_id=b.order_id Where a.filid= ' S '

Summarize

Only one left connection appears in SQL:

The code is as follows	Copy Code
Select A.*, b.*, c.* from B, a Left JOIN C on a.id = C.id where b.id = A.iid

Note that table A is next to the nearest associated leftjoin.

There are 2 or more left connections in sql:

The code is as follows	Copy Code
Select A.*, b.* c.*, d.*, e.* From (( C, B, a ) Left Join D on d.id = a.ID ) LEFT join E on e.id = b.ID where c.id = b.ID and b.id = a.ID

Leftjoin sum the nearest table next to each other, with the help of parentheses