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: Suppose there are two tables, one is the student table and the other is the Student score table.

The table data are:

One, internal connection-inner jion:

The most common connection query may be that of identifying the student's name and score:

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, within the connection, the student.id=mark.studentid conditions of the tuple is selected, can also be written as:

Select S.name,m.mark from student s inner join Mark M on S.id=m.studentid

Only two results were met, and the results were as follows:

Second, left join connect-left join:

Left join is to select all the tuples from the left table:

Select S.name,m.mark from student s left join Mark M on S.id=m.studentid

The above statement is the left side of the table, that is, the tuple in the student table is all selected, although some of the score table is not data, also selected, the results are:

Third, right connection-right join:

The right connection is to remove all data from the right table, regardless of whether the table on the left has matching data:

Select S.name,m.mark from student s right join Mark M on S.id=m.studentid

The above statement takes all the data from the Mark score table, regardless of whether there is a data match in the student table, as shown in:

Iv. fully connected-full join:

Remove the data from the left and right two 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, the data for the student and score tables are taken out:

More commonly used is the first, the inner connection, and is used in this way:

Select S.name,m.mark from student S,mark m where S.id=m.studentid

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