Advanced joins of SQL Learning (self-joins, natural joins, outer joins)

Create TableCustomers (Idint Identity(1000000,1), Companyvarchar( -)NULL, Namevarchar( -)NULL)Insert  intoCustomersValues('Fun4all','Tom')Insert  intoCustomersValues('Alibaba','Tim')Insert  intoCustomersValues('BaiDu','Jerry')Insert  intoCustomersValues('G0ogle','Herry')Insert  intoCustomersValues('Tencent','Mahuateng')Insert  intoCustomersValues('Fun4all','Tom')

This is the SQL script needed to explain

1. The self-junction code is as follows:

Select *  from Customers

Now there is a need to send a message to all of Tom's members in the same company.

Analysis of the basic ideas, the first according to Tom find his company name, in accordance with the company name to find all the members of his company. Here is the workaround code:

Select *  from  wherein (selectfromwhere Name='  Tom')

OK, through the sub-query to complete the requirements easily!

But there is a point here, you have to know, although the sub-query is convenient, but most of the DBMS processing sub-query faster than processing the junction is much slower, if the amount of data is small, almost no impact, but if the data will be very large, you should use self-coupling!

Here is the workaround code that uses the self-junction:

Select *  from Customers c1,customers C2 where c1.company=c2.company and C2. Name='Tom'

This is the two tables (actually a table) after the junction of the result diagram, OK to complete the demand!

Through the linkage mechanism

Advanced joins of SQL Learning (self-joins, natural joins, outer joins)