Detailed explanation of MySQL database--multi-table query--inner join, outer join, subquery, correlated subquery

Multi-table query

Use a single select statement to retrieve related query results from multiple tables. Multi-table joins are usually established on parent-child tables that have mutual relationships.

1 Cross Connect

The Cartesian product of all the rows in the first table multiplied by all the rows in the second table creates a table of consumers and customers:
The code is as follows:

-- create table customers(
--id int primary key auto_increment,
-- name VARCHAR(20) not null,
--address VARCHAR(20)not NULL
-- );
-- CREATE table orders(
--order_namre VARCHAR(20) primary key,
-- num char(20) not NULL,
--price int not null,
-- customers_id int,
-- constraint cus_ord_fk FOREIGN key(customers_id) REFERENCES customers(id)
-- )
Just insert the data yourself.
grammar:

Implicit syntax (without using keywords): select * from customers,orders;

The results are as follows:

Explicit syntax (using keywords): select * from customers c INNER JOIN orders o ON c.id=o.customer_id;

The two running results are the same, but the Cartesian product is wrong. The following method can be used to correct it.

2 Inner Join

Because the result set obtained by the cross join is wrong. Therefore, the inner join is based on the cross join and only lists the data rows in the connection table that match the connection conditions. The records that do not match will not be listed.

grammar:

Implicit syntax:

select * from customers,orders where customers.id=orders.customers_id;

Explicit syntax:

select * from customers c INNER JOIN orders o ON c.id=o.customer_id;

The results are as follows

We can also give programs aliases:
The code is as follows:

select * from customers as c,orders o where c.id=o.customers_id;
SELECT * from customers as c inner join orders o on c.id=o.customers_id;

3 Outer Join

The inner connection only lists the information of all users who have purchased the product, and does not list the users who have not purchased the product.
The external link uses one table as the base table, and the information of other tables is spliced. If it exists, it will be spliced ​​in. If not, null will be displayed; external links are divided into left outer joins and right lower joins.
Left outer join: join with the table on the left of the keyword as the base table

grammar:

select * from customers c LEFT JOIN orders o ON c.id=o.customer_id; 

Right outer join: The table to the right of the keyword is the base table

grammar:

select * from orders o RIGHT JOIN customers c ON c.id=o.customer_id; 

4 Subqueries

In some cases, when performing a query, the condition required is the result of another select statement. At this time, a subquery will be used. The query (inner query) that is executed first to provide data for the main query (outer query) is called a subquery; subqueries are divided into nested subqueries and correlated subqueries.

Nested subqueries:

The execution of the inner query is independent of the outer query. The inner query is executed only once, and the result is used as the condition of the outer query after the execution is completed (the subquery statement in the nested subquery can be taken out and run separately.)

Grammar and exercises: Query all students taught by the teacher with id 1.

select * from students where id in(select s_id from teacher_student where t_id=1);

Correlated subqueries:

The execution of the internal query depends on the data of the external query. Each time the external query is executed, the internal query will also be executed. Each time, the outer query is executed first, a tuple in the outer query table is taken out, the data in the current tuple is passed to the inner query, and then the inner query is executed. Based on the result of the internal query execution, determine whether the current tuple meets the where condition in the external query. If so, the current tuple is a record that meets the requirements, otherwise it does not meet the requirements. Then, the external query continues to fetch the next tuple data and executes the above operations until all tuples are processed.
Create three tables Exercise 1. Query all students taught by the teacher with id 1.
– Create a teacher table

create table teacher1(
id int primary key auto_increment,
name char(20) not NULL,
subject char(20) not null
); 

– Create a student table

create table student1(
id int primary key auto_increment,
name char(20) unique not null,
age int null
); 

– Create a third table

create table tea_stu(
id int PRIMARY KEY,
name char(20),
t_id int,
s_id int,
score int not null,
constraint teacher1_id_fk foreign key(t_id) references teacher1(id),
constraint student_id_fk foreign key(s_id) references student1(id)
);

Exercise 1. Query all students taught by the teacher with id 1.

Method 1: Write it out in separate ways:

select s_id from tea_stu where t_id=1;
select * from student1 where id in(2,3);

Method 2:

select * from student1 where id in(select s_id from tea_stu where t_id=1); 

Correlated subqueries:

The execution of the internal query depends on the data of the external query. Each time the external query is executed, the internal query will also be executed. Each time, the outer query is executed first, a tuple in the outer query table is taken out, the data in the current tuple is passed to the inner query, and then the inner query is executed. Based on the result of the internal query execution, determine whether the current tuple meets the where condition in the external query. If so, the current tuple is a record that meets the requirements, otherwise it does not meet the requirements. Then, the external query continues to fetch the next tuple data and executes the above operations until all tuples are processed.
Find: The score of students whose test scores in each subject are greater than the average score.

select * from tea_stu as a where a.score>(select avg(b.score) from tea_stu as b where a.s_id=b.s_id); 

The above is the detailed integration of MySQL multi-table query introduced by the editor. I hope it will be helpful to everyone. If you have any questions, please leave me a message and the editor will reply to you in time. I would also like to thank everyone for their support of the 123WORDPRESS.COM website!