SQL table join query (inner join, full join, left join, right join)

SQL table join query (inner join, full join, left join, right join)

 

Prerequisites: Assume that there are two tables, one is the student table and the other is the student renewal table.

 

 

 

 

 

Table data includes:

 

 

 

 

 

 

 

 

 

I. inner connection-inner jion:

The most common connection query is as follows:

select s.name,m.mark from student s,mark m where s.id=m.studentid

The above is our most common inner join, that is, the inner join, which selects the tuples that meet the student. ID = mark. studentid condition. You can also write them as follows:

select s.name,m.mark from student s inner join mark m on s.id=m.studentid

There are only two results that meet the conditions. The result is:

 

2. Left join:

The left join Selects all the tuples in the left table:

select s.name,m.mark from student s left join mark m on s.id=m.studentid

The preceding statement Selects all the tuples in the table on the left, that is, the student table. Although some score tables do not have data, they are also selected. The result is:

 

3. Right join:

The right join is to retrieve all the data in the right table, regardless of whether the left table has matched data:

select s.name,m.mark from student s right join mark m on s.id=m.studentid

The preceding statement extracts all the data in the mark score table, regardless of whether the student table matches data, as shown in:

 

4. Full join:

Extract the data from both the left and right tables, whether or not they match:

select s.name,m.mark from student s full join mark m on s.id=m.studentid

As shown in, the data in the student table and student table are retrieved:

 

The first method is commonly used, internal join, and is written in this way:

select s.name,m.mark from student s,mark m where s.id=m.studentid