MySQL external connection, internal connection, self connection, natural connection, cross connection

 

• Self join ( Most special )
• Put the same table
• Inquire about xx Name , And the name of the superior
• There is no superior's name in the record of the same line, only the superior's number

Example : Query the name of the changed employee And the name of the immediate superior

 

 

result :

• -- Summary ： 1 One table is used as two tables
• -- 2. Need to take an alias ( Tables are aliased respectively ) Add screening conditions where staff .mgr Number pointing to the superior
• -- 3 Unclear listing , You can alias Columns

 

Internal connection

Example :    Find out the student information in both the student form and the grade sheet

Internal connection , If you use students id To connect , Is to find the existing in the two tables i Student d, Then you can selectively show these students id Later data

1. Row data : adopt   where Statement to filter what to show

2, Column data : adopt select To select the display field

Feeling heel select  *  from   biao 1 biao2  where   biao1.xx=biao2.xx    There are not many checks, but there are differences in performance

 

For reference only : forehead    (where Merge first and then filter conditions ,on Filter conditions first and then merge ！？)

  -- It feels like connecting the two watches ( The difference between ordinary multi table query and inner join )
      
      -- difference  ：  on Filter first and then associate , High priority , So do it first hash Filter matches , Merge two more tables
             --  where First associate the consolidated table and then     Filter matches ( Large amount of ),
           --  on It refers to the end after matching a required record , The others don't match , Then merge the tables ,
           -- and where Will match until the end , Then merge the table
           
         --  on Match to the same data ->   join ( Merge )  ->  select Inquire about  
        --   Merge biao1,biao2 ,    -->where Filter the merged information -->select Inquire about
         -- on Efficient     First hash, After the merger , efficiency  0 logN
         -- where Low efficiency :   Multiple from Cartesian collection Merge Screening again  0(n^2)

Example :       Query the order quantity of process dimension

    Ordinary Cartesian product multi table query :

  SELECT  SUM( Number )FROM   Sales list , Employee information  WHERE   Sales list . Employee job number   =  Employee information . Job number  
  AND  Employee information . full name =" Cheng Wei "

    Internal connection join  

 SELECT SUM( Number ) FROM   Employee information  JOIN  Sales list   ON   Employee information . Job number = Sales list . Employee job number 
    
    WHERE  full name =" Cheng Wei " 

 

• External connection

• The left outer join ( If the table on the left completely shows 【 The table on the left will display even if the records of some fields are not associated with another table field 】)
• The right outer connection is the same

 

  Full outer join :  In the result table, except for the rows that meet the connection conditions , It also includes all the rows of the two tables ( Duplicate de duplication of all records in a single line )

 

  Example :

Prepare two simple tables    -- m2 surface   Student's id   Student scores     -- m1 surface   Student's id   Student num Grade point
 

 

 

Left connection test :      Students without grades id It also shows

  SELECT  *  FROM  m1 LEFT JOIN m2 ON m1.id=m2.id 

     

 

 

Right test :    Students without grade points id It also shows

 

Full outer join : at present mysql  There is such a saying    x biao  full  join  y biao    But this kind of writing is not supported now , It can be used union The joint query

 SELECT  *  FROM  m1 LEFT JOIN m2 ON m1.id=m2.id  -- m2  Students without grades id It also shows 
     UNION  
    SELECT  *  FROM  m1 RIGHT JOIN m2 ON m1.id=m2.id -- m1  Students without grade points id It also shows 

 

 

  ps : Here you can test again   union  all The difference between    

You can find union all Will delete... On the basis of all external connections , Records with identical records in the table will also be displayed repeatedly

 

Cross connect

Cross connect （ Cartesian product is also known as ）
First , Let's first briefly explain the Cartesian product .

There are two sets A and B,A = {0,1} B = {2,3,4}

aggregate  A×B  and  B×A The result set of can be expressed as the following form respectively ：

A×B = {（0,2）,（1,2）,（0,3）,（1,3）,（0,4）,（1,4）};
B×A = {（2,0）,（2,1）,（3,0）,（3,1）,（4,0）,（4,1）};
above A×B and B×A The result can be called the result of multiplication of two sets ‘ The cartesian product ’.

From the above data analysis, we can draw the following two conclusions ：

Multiply two sets , Not satisfied with the exchange rate , Both  A×B ≠ B×A; A The collection and B Gather and multiply , Contains sets A Elements and collections in B All the possibilities of combining elements in , That is to say, the number of elements of the new set obtained by multiplying two sets is  A The number of elements in the set  × B The number of elements in the set .

 

Cross connect ：

SELECT * from  surface 1 JOIN  surface 2;
A cross join is actually a Cartesian product of two tables , The result table is formed by splicing each row of the first table and each row of the second table , be called ‘ Cartesian product table ’, The number of rows in the result table is equal to the product of the number of rows in two tables .

If both tables have a large amount of data , That would take up a lot of memory space, which is obviously unreasonable . therefore , When we do table join queries, we usually use JOIN xxx ON xxx The grammar of ,ON The statement is executed in JOIN Before the statement , That is to say, when the data rows of two tables are matched , Will first determine whether the data row conforms to ON The condition after the statement , Then decide whether to JOIN.

therefore , There is an obvious SQL The optimal solution is , When the amount of data in the two tables is large , When you need to connect to a query , You should use  FROM table1 JOIN table2 ON xxx The grammar of , Avoid using  FROM table1,table2 WHERE xxx  The grammar of , Because the latter will form a Cartesian product table with large amount of data in memory , Increased memory overhead .

Natural join   

 --   natural Inside 、 Outside Connect ( Natural join )

      -- Natural internal connection ( It is the merging of the two tables that we usually use most , Do Cartesian product )
      --  ( But it has automatically helped us match other connection conditions )

      SELECT  *  FROM  USER NATURAL JOIN score      
      

      -- ps:   Internal connection     (inner) join   Just changed a word   
      --   The special feature of natural connection is that it automatically helps us match the wait connection

     

      
      --   Natural external connection is the same
        
    
     
     
     

        

As a study note, it's just a parameter