On the difference inner,left,right,full_mysql of SQL connection query

--table1 table

ID NAME QQ PHONE
1 Qin Yun 10102800 13500000
2 on the road 10378 13600000
3 LEO 10000 13900000
4 Qin Yun 0241458 54564512

--table2 table

ID NAME SJSJ gly
1 Qin Yun 2004-01-01 00:00:00.000 Li Dawei
2 Qin Yun 2005-01-01 00:00:00.000 Ma Teng
3 on the way 2005-01-01 00:00:00.000
4 Qin Yun 2005-01-01 00:00:00.000 Li Dawei
5 on the way 2005-01-01 00:00:00.000 Li Dawei

The--inner join appears as long as it meets the requirements.

 

results from the SELECT * FROM table1 t1 inner join table2 T2 on t1.name=t2.name<br><br>--

ID name QQ PHONE ID name SJSJ gly
1 Qin Yun 10102800 13500000 1 Qin Yun 2004-01-01 00:00:00.000 Li Dawei
4 Qin Yun 0241458 54564512 1 Qin Yun 2004-01-01 00:00:00.000 Li Dawei
1 Qin Yun 10102800 13500000 2 Qin Yun 2005-01-01 00:00:00.000 Ma Teng
4 Qin Yun 0241458 54564512 2 Qin Yun 2005-01-01 00:00:00.000 Ma Teng
2 on the road 10378 13600000 3 on the road 2005-01-01 00:00:00.000 Ma Teng
1 Qin Yun 10102800 13500000 4 Qin Yun 2005-01-01 00:00:00.000 Li Dawei
4 Qin Yun 0241458 54564512 4 Qin Yun 2005-01-01 00:00:00.000 Li Dawei
2 on the road 10378 13600000 5 on the way 2005-01-01 00:00:00.000 Li Dawei

--left join with the left table as the main, you will see if the right table related to the field repeat, the left associated field information doubled.

SELECT * FROM table1 T1 (1) left join Table2 T2 on T1.name=t2.name with select * FROM Table2 T2

 

 

(2) Right join Table1 T1 on t1.name=t2.name results are equal, but the order of the two tables is changed.  (1) Result ID name QQ PHONE ID name SJSJ gly 1 Qin Yun 10102800 13500000 1 Qin Yun 2004-01-01 00:00:00.000 li Dawei 1 Qin Yun 10102800 13500000 2 Qin Yun 2005-01-01 00:00:00.000 1 Qin Yun 10102800 13500000 4 Qin Yun 2005-01-01 00:00:00.000 Li Dawei 2 on the road 10378 1 3600000 3 on the road 2005-01-01 00:00:00.000 2 on the road 10378 13600000 5 on the road 2005-01-01 00:00:00.000 Li Dawei 3 LEO 10000 139 00000 null NULL NULL 4 Qin Yun 0241458 54564512 1 Qin Yun 2004-01-01 00:00:00.000 li Dawei 4 Qin Yun 0241458 54564512 2 Qin Yun 20   05-01-01 00:00:00.000 ma 4 Qin Yun 0241458 54564512 4 Qin Yun 2005-01-01 00:00:00.000 li Dawei (2) Result ID name SJSJ gly ID Name  QQ PHONE 1 Qin Yun 2004-01-01 00:00:00.000 li Dawei 1 Qin Yun 10102800 13500000 2 Qin Yun 2005-01-01 00:00:00.000 ma Teng 1 Qin Yun 10102800 13500000 4 Qin Yun 2005-01-01 00:00:00.000 li Dawei 1 Qin Yun 10102800 13500000 3 on the road 2005-01-01 00:00:00.000 Ma Teng 2 on the road 10378 13600000 5 on the road 2005-01-01 00:00:00.000 Li Dawei 2 on the road 10378 1 3600000 null NULL NULL 3 LEO 10000 13900000 1 Qin Yun 2004-01-01 00:00:00.000 li Dawei 4 Qin Yun 0241458 54564512 2 Qin Yun 2 005-01-01 00:00:00.000 ma 4 Qin Yun 0241458 54564512 4 Qin Yun 2005-01-01 00:00:00.000 li Dawei 4 Qin Yun 0241458 54564512

--left join has the right table as the main, with left the same

SELECT * FROM table1 T1 right 

join table2 T2 on t1.name=t2.name

 

ID name  QQ PHONE  ID name  SJSJ  gly< C8/>1  Qin Yun 10102800  13500000  1  Qin Yun 2004-01-01 00:00:00.000 li Dawei

4  Qin Yun 0241458  54564512 1  Qin Yun 2004-01-01 00:00:00.000 li Dawei

1  Qin Yun 10102800  13500000  2  Qin Yun 2005-01-01 00:00:00.000 Ma

4  Qin Yun 0241458 54564512  2  Qin Yun 2005-01-01 00:00:00.000 ma

2  on the road 10378  13600000  3  on the way 2005-01-01 00:00:00.000 ma

1  Qin Yun 10102800  13500000  4  Qin Yun 2005-01-01 00:00:00.000 Li Dawei

4  Qin Yun 0241458 54564512  4  Qin Yun 2005-01-01 00:00:00.000 li Dawei

2  on the road 10378  13600000  5  on the way 2005-01-01 00:00:00.000 Li Dawei

--full join both tables of data are taken out, whether or not they conform to

SELECT * FROM table1 T1 full 

join table2 T2 on t1.name=t2.name

 

result

ID name  QQ PHONE  ID name  sjsj
   gly

1  Qin Yun 10102800  13500000  1  Qin Yun 2004-01-01 00:00:00.000 li Dawei

1  Qin Yun 10102800  13500000  2  Qin Yun 2005-01-01 00:00:00.000 ma

1  Qin Yun 10102800  13500000  4  Qin Yun 2005-01-01 00:00:00.000 Li Dawei

2 on  the road 10378  13600000  3  on the road 2005-01-01 00:00:00.000 ma

2  on the road 10378  13600000  5  on the road 2005-01-01 00:00:00.000 Li Dawei

3  LEO 10000  13900000  NULL  NULL  Null  null

4  Qin Yun 0241458 54564512  1  Qin Yun 2004-01-01 00:00:00.000 li Dawei

4  Qin Yun 0241458 54564512  2  Qin Yun 2005-01-01 00:00:00.000 ma

4  Qin Yun 0241458 54564512  4  Qin Yun 2005-01-01 00:00:00.000 Li Dawei

The above is a small series for everyone to talk about the difference of SQL connection query Inner,left,right,full All content, I hope we support cloud Habitat Community ~