SQL query--sql left join/sql right JOIN

Brief introduction

In the actual situation, for example in a university, there are many teachers, teachers have their own research direction and title. And, perhaps not every teacher has a graduate student, if a new teacher, may not have brought graduate students. So, if the leader asks to find out all the teachers with the number of graduate students (no graduate students with 0), then you need to use the left/right outer connection of the query.

In this paper, we will analyze and give a practical solution to the similar situation.

The table structure of this article:

• Graduate student table structure:

CREATE TABLE ' g_student ' (
' GID ' int (one) not NULL auto_increment,
' Name ' varchar (DEFAULT NULL),
' Gender ' char (1) DEFAULT NULL,
' Start_time ' Date DEFAULT NULL,
' Tid ' int (one) DEFAULT NULL,
PRIMARY KEY (' gid '),
KEY ' Fk_student_teacher ' (' tid '),
CONSTRAINT ' Fk_student_teacher ' FOREIGN KEY (' tid ') REFERENCES ' teacher ' (' tid ')
) Engine=innodb auto_increment=5 DEFAULT Charset=utf8;

• TUTOR Table Structure:

CREATE TABLE ' Teacher ' (
' Tid ' int (one) not NULL auto_increment,
' Name ' varchar (DEFAULT NULL),
' job_title ' varchar DEFAULT NULL,
' Major ' varchar DEFAULT NULL,
PRIMARY KEY (' tid ')
) Engine=innodb auto_increment=6 DEFAULT Charset=utf8;

• Tutor Table Data

Insert INTO ' teacher ' (' tid ', ' name ', ' Job_title ', ' major ') values

(1, ' Shen ', ' Professor ', ' image '),

(2, ' Liu ', ' Professor ', ' database '),

(3, ' Yellow ', ' associate Professor ', ' machine learning '),

(4, ' Jarvis ', ' Lecturer ', ' Multimedia '),

(5, ' King ', ' associate Professor ', ' machine learning ');

• Postgraduate table data:

Insert INTO ' g_student ' (' gid ', ' name ', ' Gender ', ' start_time ', ' tid ') values

(1, ' A ', ' Male ', ' 2015-01-01 ', 1),

(2, ' B ', ' Male ', ' 2015-09-01 ', 2),

(3, ' C ', ' Male ', ' 2015-09-01 ', 3),

(4, ' D ', ' female ', ' 2015-09-01 ', 1);

Querying items

• Please find out the number of graduate students each tutor has.

Analysis:

Not every teacher has a graduate student, so the number of graduate students without a graduate student should be 0. External connections are therefore required

SELECT COUNT (gs.name), T. ' Name ' from G_student GS right JOIN teacher T
On GS. ' Tid ' =t. ' Tid '
GROUP by GS. ' Tid ';

Here, if you want to use a LEFT join, then the positions of g_student and teacher need to be exchanged

SQL query--sql left join/sql right JOIN