Shulou(Shulou.com)05/31 Report--

Editor to share with you what is the left outer connection, right outer connection and inner connection in the sql sentence. I hope you will get something after reading this article. Let's discuss it together.

The following code demonstration is based on the name table and the country table.

Name table

Idname1 Xi Shi 2 Yang Yuhuan 3 Diao Chan 4 Wang Zhaojun 5 Zhao Feiyan

Country table

IdcountryA_ID1 Spring and Autumn period Yue 12 Tang Dynasty Puzhou Yongle 23 Eastern Han Dynasty Shanxi Xinzhou 34 Western Han period Southern County Zigui 45 warring States period 6

Note:

1) there is a relationship between table b A_ID and table an a_id

2) join query is to use association conditions to remove mismatched data, otherwise Cartesian product will occur.

1) Internal connection

Syntax: fields to be queried by select from table name 1 inner join table name 2 on table 1. Field = Table 2. Field; inner join can be abbreviated to comma, internal connection features: only data application scenarios that fully meet the conditions after on will be retained: if two tables have a foreign key relationship, you can use internal links, because only a single record select * from name n inner join country c on n.id = c.N_ID can be returned through internal links. IdnameidcountryN_ ID1 Xi Shi 1 Spring and Autumn period Yue 12 Yang Yuhuan 2 Tang Dynasty Puzhou Yongle 23 Diao Chan 3 late Eastern Han Dynasty Shanxi Xinzhou 34 Wang Zhaojun 4 Southern County Zigui 4

2) left outer connection

Syntax: fields to be queried by select from Table 1 left outer join Table 2 on Table 1. Field = Table 2. Field; outer can omit the characteristics of the left outer join: mainly the left table, it will retain the data application scenarios in the left table that do not meet the post-on conditions: only some records can be found in Table 2, but Table 1 can query with Table 2 through the left outer join if you want to show all the records. Select * from name n left join country c on n.idylc.NumbidacternativeidnameidationyNationID1 Xi Shi 1 Spring and Autumn period Yue 12 Yang Yuhuan 2 Tang Dynasty Puzhou Yongle 23 Diao Chan 3 late Eastern Han Dynasty Shanxi Xinzhou 34 Wang Zhaojun 4 Western Han Dynasty Zigui 45 Zhao Feiyan nullnullnull

3) right external connection

Syntax: select * from Table 1 right outer join Table 2 on Table 1. Field = Table 2. Field; outer can omit the right outer join features: mainly the right table, will retain the data application scenarios in the right table that do not meet the post-on conditions: contrary to the left outer join, only some records can be queried from Table 1, but Table 2 can query through the right outer join with Table 1 if you want to show all the records. Select * from name n right join country c on n.idler c.NabilityNaturID1 Xi Shi 1 Spring and Autumn period Yue 12 Yang Yuhuan 2 Tang Dynasty Puzhou Yongle 23 Diao Chan 3 late Eastern Han Dynasty Shanxi Xinzhou 34 Wang Zhaojun 4 Western Han period South County Zigui 4nullnull5 warring States period 6

4) full link

Syntax: select * from Table 1 full join Table 2 on Table 1. Field = Table 2. Field; complete connection characteristics: all the data in tables 1 and 2 will be retained select * from name n full join country c on n.idwitness c.NabilitysnameidentyNationID1 Xi Shi 1 Spring and Autumn period 12 Yang Yuhuan 2 Tang Dynasty Puzhou Yongle 23 Diao Chan 3 late Eastern Han Dynasty Shanxi Xinzhou 34 Wang Zhaojun 4 Western Han period Zigui 45 Zhao Feiyan nullnullnullnullnull5 warring States period 6

Note: this syntax is uncomfortable in MySql

After reading this article, I believe you have a certain understanding of "what is the left outer connection, right outer connection and inner connection in the sql sentence". If you want to know more about it, welcome to follow the industry information channel, thank you for your reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.