Detailed explanation of MySQL multi-table join query

Multi-table join query

The connection between tables is divided into inner connection and outer connection

• Inner join: select only the records that match each other in two tables
• Outer join: includes both matching records and unmatched records in two tables. Outer join is divided into left outer join (left join) and right outer join (right join)

Inner Join

First, prepare two tables

Student table

Score table

Inner join: Find the common records that meet the conditions in each table. Query the student names and scores in the student table.

The first way to write: only use where

select a.s_name, b.s_score from student a,score b where a.s_id = b.s_id;

The second way to write it: join ... on ...

select a.s_name, b.s_score from student a join score b on a.s_id = b.s_id

The third way to write it: inner join … on…

select a.s_name, b.s_score from student a inner join score b on a.s_id = b.s_id

Left Join

Left join: According to the records in the left table, find the records that meet the conditions in the connected right table to match them. If no records matching the left table are found, null is used to represent them.

The first way to write it: left join … on …

select a.s_name,b.s_score from student a left join score b on a.s_id = b.s_id

The second way of writing: left outer join … on …

select a.s_name,b.s_score from student a left outer join score b on a.s_id = b.s_id

Right Join

**Right join: **According to the records in the right table, find the records that meet the conditions in the connected left table to match them. If no match is found, it is represented by null.

The first way to write it: right join … on …

select a.s_name,b.s_score from student a right join score b on a.s_id = b.s_id;

The second way of writing: right outer join … on …

select a.s_name,b.s_score from student a right outer join score b on a.s_id = b.s_id;

Subqueries

Subquery : It is a way to implement multi-table join query. Another select statement is nested in the from clause or where clause of a select statement. The outer select query statement becomes the main query. In other words , the query statement in WHERE or FORM is called a subquery.

Subquery in WHERE clause: The value returned by the subquery is used as the query condition of the main query

Subquery in the FROM clause: The subquery returns a virtual table, and the main query finds the conditions that meet the conditions from the temporary table.

Summarize

This article ends here. I hope it can be helpful to you. I also hope that you can pay more attention to more content on 123WORDPRESS.COM!