Shulou(Shulou.com)05/31 Report--

How to use JOIN in the database? Many novices are not very clear about this. In order to help you solve this problem, the following editor will explain it in detail. People with this need can come and learn. I hope you can gain something.

Join

I believe that when learning the use of the database, we have used Join to connect two or more tables in the database.

Join is divided into:

Internal connection (inner join)

External connection (outer join)

The external connection is divided into:

Left external connection (left outer join)

Right external connection (right outer join)

Full external connection (full outer join)

Description:

1. The "OUTER" keyword of the outer connection can be omitted. two。 The inner connection is used to return records that meet the connection condition, while the outer connection is an extension of the inner connection, which not only satisfies the record of the connection condition, but also returns the record that does not meet the connection condition.

Cartesian product

A situation that will occur when we do multi-table federated queries-- Cartesian product phenomenon

Let's take the following two tables as examples:

Student form (Student)

IDStudentNameStudentClassID1 Xiao Ming 12 Xiao Hong 23 Xiao Lan 34 Xiao Lu 25 Xiao Zi 1

Class table (Class)

ClassIDClassName1 Software Class 1, Class 2, Software Class 3, Software Class 3

When we do the query operation:

Select * from Student,Class

The above situation, that is, the Cartesian phenomenon, there are five records in table Student and three records in table Class, so there are five choices for table Student and three choices for table Class. So there are 5 * 3 = 15 choices, which is the Cartesian product.

Internal connection-inner join

The inner join query returns all records that meet the criteria, and if no join is specified by default, it is an inner join. For example: query xx students to use the above data table in xx class

Select stu.StudentName,cl.ClassName from Student stu inner join Class cl on stu.StudentClassID=cl.ClassID

Query result

Left outer link-left join

The left outer join query returns not only all records that meet the criteria, but also other rows of the table on the left side of the join operator that do not meet the join condition. We added a new student to the original Student table: Xiaomei.

For example: query xx students to use the above data table in xx class

Select stu.StudentName,cl.ClassName from Student stu left join Class cl on stu.StudentClassID=cl.ClassID

Query result

Right external connection-right join

The right outer join query returns not only all records that meet the criteria, but also other rows of the table on the right side of the join operator that do not meet the join condition. We added classes to the original Class table:

Software Class 4

For example: query xx students to use the above data table in xx class

Select stu.StudentName,cl.ClassName from Student stu right join Class cl on stu.StudentClassID=cl.ClassID

Query result

Fully connected-full join

The full join query returns not only all records that meet the criteria, but also other rows that do not meet the join criteria.

Note: full join is not supported by mysql by default.

Is it helpful for you to read the above content? If you want to know more about the relevant knowledge or read more related articles, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.