MySQL Multi-table query

Multi-table query, SQL JOINS

1. INNER JOIN inside joins

Select S.name as student_name,t.name as Teacher_name from students as s inner joins teachers as T on S.teacherid=t.tid; /*2 table take intersection, inner connection */

2. Cross-Connect

SELECT * FROM students cross join teachers; /* Cartesian product, students table contents and teacher table content are combined once/*

3. Left Outer connection

Select S.name as student_name,t.name as Teacher_name from students as s left outer joins teachers as T on S.teacherid=t.tid ;

1.) Left inner connection

Select S.name as student_name,t.name as Teacher_name from students as s left outer joins teachers as T on S.teacherid=t.tid and t.name is null;

4. Right outer connection

Select S.name as student_name,t.name as teacher_name from teachers as T right outer join students as s on S.teacherid=t.ti D

5. Full external connection

Union

MariaDB [hellodb]> Select S.name as student_name,t.name as Teacher_name from students as s left OUTER join teachers as T on S.teacherid=t.tid

Union

Select S.name as student_name,t.name as Teacher_name from students as s right outer join teachers as T on S.teacheri D=t.tid;

5. Self-connect

Select S1.name as EMP, s2.name as leader from students as S1 inner join students as S2 on S1.teacherid=s2.stuid; /* Use an inner connection to get the intersection of 2 tables */

Select S1.name as EMP, s2.name as leader from students as S1 left outer joins students as S2 on S1.teacherid=s2.stuid; /* Use LEFT OUTER join to take 2 table intersection */

6. Sub-view

#查看分数大于平均分的分数

Select Stuid,score from scores where score > (select AVG (score) from scores);

#查询分数大于平均分数, student's name and score

Select St.name,sc.score from students as St inner joins scores as SC on St.stuid=sc.stuid and score > (select AVG (Score) from scores);

MySQL Multi-table query