Catalog

One 、 summary

Two 、 Classification of join queries

3、 ... and 、 Seven common connection query details

1、 The cartesian product ：

2、 Internal connection

2.1 Implicit and explicit join

 2.2 Equivalent connection

 2.3 Non equivalent connection

 2.4 Self join

3 External connection

3.1 The left outer join ：

 3.2 Right connection ：

3.3 Full outer join

---

One 、 summary

        In actual development , In most cases, data is not queried from a single table , In general, multiple tables are queried jointly to get the final result . A business will correspond to multiple tables , such as ： Students and classes , At least two tables .( Avoid data redundancy ).

The difference between internal and external connections

       1、 Internal connection ：

        hypothesis A and B Tables to connect , If you use an internal connection , Anyone who A Table and B The table can be queried by matching the records on the table , This is the inner link .AB There is no difference between the two tables , The two tables are equal .

        2、 External connection ：

        hypothesis A and B Tables to connect , If you use external connections , AB One of the two tables is the main table , A watch is a secondary watch , It mainly queries the data in the main table , With the side table . When the data in the secondary table does not match the data in the primary table , The secondary table automatically simulates NULL To match .
 

Two 、 Classification of join queries

1、 Divide according to the age of grammar ：

•  sql97-- Only internal connections are supported ( Some old ones DBA This syntax may still be used .)
•  sql99-- Recommended , Support left outer + Right outside ( Left lateral + Right outside )+ cross

2、 Classify according to function :

• Cross connect ： The cartesian product .
• Internal connection ： Equivalent connection 、 Non equivalent connection 、 Self join .（ It can also be divided into implicit 【 nothing join】 And explicit 【 Yes join】）
• External connection ： The left outer join ( Left connection )、 Right connection ( The right connection )、 Full connection .

3、 Generate test data

      Execute the following sql Statements generated student Table and teacher surface , The two tables pass teacher_id relation .

create table student(
    id int(3) not null primary key,
    name char(6) not null,
    age int(2) not null,
    teacher_id int(3) not null
);
create table teacher(
    id int(3) not null primary key,
    name char(6) not null,
    age int(2) not null,
    class_id int(3) not null
);

 Insert value  id  full name   Age   Teachers' id

insert into student values(1, ' Liu Feng ', 20, 1);
insert into student values(2, ' Li Fu ', 22, 2);
insert into student values(3, ' Wang Zi ', 21, 3);
insert into student values(4, ' Zhao lan ', 24, 4);
insert into student values(5, ' summer ', 23, 5);
insert into student values(6, ' Zhan Qi ', 22, 6);
insert into student values(7, ' Liu Na ', 24, 4);
insert into student values(8, ' Wang Heng ', 23, 5);
insert into student values(9, ' Xiaofei ', 22, 6);

 Insert value  id  full name   Age   class id

insert into teacher values(1, ' Summer is fine ', 36, 1);
insert into teacher values(2, ' Li Chun ', 32, 2);
insert into teacher values(3, ' Zhang Ye ', 34, 3);
insert into teacher values(4, ' Deer snow ', 37, 3);
insert into teacher values(5, ' Liu Hua ', 36, 2);
insert into teacher values(6, ' Jia Yi ', 34, 1);

 Execute the following query statement ,student Table and teacher The table is as follows ：

select * from student;
select * from teacher;

                    student surface                                                               teacher surface

      

3、 ... and 、 Seven common connection query details

1、 The cartesian product ：

      Cartesian product is also called cross connection , Cross connection is a kind of inner connection .

      Suppose the set A={a,b}, aggregate B={0,1,2}, Then the Cartesian product of two sets is {(a,0),(a,1),(a,2),(b,0),(b,1), (b,2)}. If A A collection of students in a school ,B A collection of all the teachers of the school , be A And B The Cartesian product of represents all possible situations in which students choose teachers .

      The characteristic of Cartesian product ： It doesn't use any matching or selection criteria , Instead, each row in one data source is directly compared with each row in another data source One One matching .

      Key notes ：

      The cartesian product ： Use less , Because there is duplicate data
      The cartesian product ： Every data in one table matches all data in another table once
      junction         fruit ：  A watch 9 strip multiply Another watch 6 strip = 54 strip

1.1 The case is as follows ：

      Query the teacher corresponding to the student

     select * from student ,teacher;

      Student list Data in every 1 All the students are with Teachers list Medium All teachers All match once .

        problem ：
        When two tables are connected for query , There are no conditions to limit , The number of final query results is the product of the number of records in the two tables . This is it. Cartesian product .  The result of the query is the product of the records of the two tables  9*6=54, Many data are invalid data . How to avoid Cartesian product ？

        Solution ：  Add conditions to filter , But only valid records will be displayed . At this time, too Implicit （ nothing join） Internal connection .

        According to the teacher id Query the course selection teacher corresponding to the student
       select st.*,th.* from student st ,teacher th where st.teacher_id = th.id;

       

        Be careful ： Use as You can alias tables and fields , About the advantages of table aliases ： High execution efficiency ; Good readability .
                  among st yes student Alias of the table , Originally was from student as st, among as It can be omitted .
                  among th yes teacher Alias of the table , Originally was from teacher as th, among as It can be omitted .

2、 Internal connection

      Internal connection , Take the intersection of two tables .

     

     Internal connection is divided into equivalent connection 、 Non equivalent connection 、 Self join .（ It can also be divided into implicit 【 nothing join】 And explicit 【 Yes join】）

2.1 Implicit and explicit join

      Implicit （ nothing join） Connection syntax ：select Field from surface A, surface B​ where Eliminate the connection condition of Cartesian product

      Case study ： According to the teacher id Query the course selection teacher corresponding to the student
     select st.*,th.* from student st ,teacher th where st.teacher_id = th.id;

     

        Explicit （ Yes join） Connection syntax ：select Field * from surface A Alias INNER( It can be omitted ) JOIN surface B Alias ON Eliminate the connection condition of Cartesian product

       Case study ： According to the teacher id Query the course selection teacher corresponding to the student

      select st.*,th.* from student st inner join teacher th on st.teacher_id = th.id;

      select st.*,th.* from student st join teacher th on st.teacher_id = th.id;（inner It can be omitted ）

     

 2.2 Equivalent connection

      The biggest feature of equivalent connection is ： The condition is the equivalence relationship ,SQL99( The most commonly used ).

      Equivalent connection syntax
     select Field * from surface 1  INNER( It can be omitted ) JOIN surface 2  ON Eliminate the connection condition of Cartesian product A=B

      Case study ： According to the teacher id Query the course selection teacher corresponding to the student

     select st.*,th.* from student st join teacher th on st.teacher_id = th.id;

     

 2.3 Non equivalent connection

      The biggest feature of non equivalent connection is ： The condition is not an equivalent relationship ,SQL99( The most commonly used ).

      Non equivalent connection syntax
     select Field * from surface 1  INNER( It can be omitted ) JOIN surface 2  ON Eliminate the connection condition of Cartesian product

      Case study ： Query teacher id stay 4-6 Information between students and teachers taught
select st.*,th.* from student st join teacher th on st.teacher_id = th.id and th.id between 4 and 6;

     

 2.4 Self join

      The biggest feature of self connection ： It's just a table. Think of it as two tables , Connect yourself

      The essence is equivalent connection , It's just the connection table itself .

      Case study ： Ask the students id And teachers id The same students
     select s.*,st.teacher_id from student s,student st where s.id = st.teacher_id;

3 External connection

    The outer connection is divided into left outer connection 、 Right connection 、 Full connection

    The left outer join （LEFT OUTER JOIN）, Left connection for short （LEFT JOIN）

    Right connection （RIGHT OUTER JOIN）, Right connection for short （RIGHT JOIN）

    Full outer join （FULL OUTER JOIN）, Full connection for short （FULL JOIN）

    towards student surface   Insert two statements ,teacher_id Of 13  and 15  stay teacher There is no such thing as .
    INSERT INTO `cqyddx`.`student`(`id`, `name`, `age`, `teacher_id`) VALUES (10, ' Xia Yu ', 21,13);
    INSERT INTO `cqyddx`.`student`(`id`, `name`, `age`, `teacher_id`) VALUES (11, ' Winter snow ', 22,15);

     Insert teacher surface   Three statements ,id Respectively
    INSERT INTO `cqyddx`.`teacher`(`id`, `name`, `age`, `class_id`) VALUES (7, ' Fu Xia ', 34, 4);
    INSERT INTO `cqyddx`.`teacher`(`id`, `name`, `age`, `class_id`) VALUES (8, ' Hao Ren ', 33, 5);
    INSERT INTO `cqyddx`.`teacher`(`id`, `name`, `age`, `class_id`) VALUES (9, ' Zhao Gang ', 35, 7);

3.1 The left outer join ：

      The left outer join ：left join or left outrer join  （outer It can be omitted ）

      The left outer join ： On the left is the main watch , The data in the left table are all displayed , The table on the right shows that ON After the condition data , Non conforming use NULL Instead of .

       

       select * from student st left join teacher th on st.teacher_id = th.id;（outer It can be omitted ）

      

      Through the query results, it is found that , Left outer connection query Yes. Data unique to the left table add Data shared by the two tables

     

      Left outer special case ： Return no matching records

     

       Case study ： Query student information without teachers

      select * from student st left join teacher th on st.teacher_id = th.id where th.id is null;

      

      Discovery by query , The left join query is the data unique to the left table .

 3.2 Right connection ：

      Right connection ：right join or right outrer join  （outer It can be omitted ）

      Right connection ： On the right is the main table , The data in the right table are all displayed , The table on the left shows that ON After the condition data , Non conforming use NULL Instead of .

       

       select * from student st right join teacher th on st.teacher_id = th.id;（outer It can be omitted ）

       

        And it turns out that , Right outer connection query Yes. The data unique to the right table add Data shared by the two tables

     

        Special circumstances outside the right ： Return no matching records

        

       Case study ： Query teacher information without students

       select * from student st right join teacher th on st.teacher_id = th.id where st.teacher_id is null;

       

        Discovery by query , The data of right join query is unique to the right table .

3.3 Full outer join

      Full outer join ：full join  or full outer join（outer It can be omitted ）, but Mysql I won't support it , have access to union Combine and de duplicate to achieve . 
       Simple understanding

      Full external query ： Namely Data unique to the left table add The data unique to the right table

      

    select * from student st left join teacher th on st.teacher_id = th.id where th.id is null
    union 
    select * from student st right join teacher th on st.teacher_id = th.id where st.teacher_id is null

    

3.4 Full connection

      Full connection ：full join  or full outer join（outer It can be omitted ）, but Mysql I won't support it , have access to union Combine and de duplicate to achieve .

        Simple understanding

        Full connection query Yes. All the data in the left table    add All the data in the right table   And remove the weight . 

       
       select * from student st left join teacher th on st.teacher_id = th.id
         union 
       select * from student st right join teacher th on st.teacher_id = th.id

       

summary ：mysql There are seven connections , They are internal connections 、 The left outer join 、 Special case of left outer connection 、 Right connection 、 Special case of right outer connection 、 Full connection 、 Full outer join . Summing up together is inner connection 、 The left outer join 、 Right connection 、 Full outer join .