The tenth lesson of the MySQL database ——— the continuation of the join connection — strong chain

Foreword by the author

Welcome little cuties to come and learn from my gtiee Qin Boss (qin-laoda) – Gitee.com

____________________________________________________________

Table of Contents

join

  • Inner joins

  • Left Join

  • Right join

  • Outer join

  • Other connections

________________________________________________________________

Author vignette

There are a lot of school courses recently, and I can’t update them in time. I hope you can include a lot of cute ones. Your three links are my motivation.

Interstitial knowledge

1. When we splicing, if the number of fields in some tables is not enough, use null to make up

  • Inner joins

select
*
from
(select * from new_employees limit 10) as ne
join
(select * from employees limit 10) as em
on ne.emp_no=em.emp_no;

  • Left Join

select
*
from
(select * from new_employees limit 20) as ne
left join
(select * from employees limit 10) as em
on ne.emp_no=em.emp_no;

  • Right join

select
*
from
(select * from new_employees limit 10) as ne
right join
(select * from employees limit 20) as em
on ne.emp_no=em.emp_no;

  • Outer join

select
*
from
(select * from new_employees limit 20) as ne
left join
(select * from employees limit 10) as em
on ne.emp_no=em.emp_no
union
select
*
from
(select * from new_employees limit 10) as ne
right join
(select * from employees limit 20) as em
on ne.emp_no=em.emp_no;

I will not use pictures to represent the code here. It is very clear about jion in the previous article. Now let’s continue with the following

Other connections

Other connection one:

It can be seen that this connection is the matching null part of the left connection. Our idea is to filter out after the left connection.

select
ne.emp_no,

ne. `age`,
ne.`gender`,
em. `birth_date`,
em.`first_name`,
em. `last_name`,
em.`gender`,
em. `hire_date`
from
(select * from new_employees limit 20) as ne
left join
(select * from employees limit 10) as em
on ne.emp_no=em.emp_no
where em.`emp_no` is null;

result:

in the same way

Other connection two:

select
ne.emp_no,
ne. `age`,
ne.`gender`,
em. `birth_date`,
em. `first_name`,
em. `last_name`,
em.`gender`,
em. `hire_date`
from
(select * from new_employees limit 10) as ne
right join
(select * from employees limit 20) as em
on ne.emp_no=em.emp_no
where ne.`emp_no` is null;

Other connection 3:

The idea is that the outer join takes the part containing null, first join the table left, then filter out the null part, then connect right, filter out the null part, and then use union distinct to splice the table,

select
*
from
(select * from new_employees limit 20) as ne
left join
(select * from employees limit 10) as em
on ne.emp_no=em.emp_no
where em.emp_no is null
union
select
*
from
(select * from new_employees limit 10) as ne
right join
(select * from employees limit 20) as em
on ne.emp_no=em.emp_no
where ne.emp_no is null;

Note that both left joins and right joins must be conditionally filtered

Self-Association

convert vertical to horizontal

Simply put, connect yourself with yourself

content of this table

This table is self-joining

select * from
city as a
join
city as b
on a.`id` = b.pid;

Subquery

in a
select
statement, embedded in another
select
Statement, I used it when I wrote it earlier

select
*
from
(select * from city limit 10) as a;

-- scalar subquery
-- Query employees who are greater than or equal to the company's average age
select * from table
where age >= (select avg(age) from table);
-- column-level subquery
select * from table1
where id in (select id from table2);
-- row-level subquery
select * from table
where (height,age) = (select max(height),max(age) from table);

The above writing method is no problem, but it is not very good to write in this way. Generally, we can use join to connect

 select
 *
 from new_employees as b
join
 (
    select avg(age) as av from new_employees
 )as a
 on b.age>=a.av
 limit 10;

Summarize:

The order of execution is:

from
Table Name

join

where

group by

select distinct *

having

order by

limit start, count

The on in join on is to filter the main table and the attached table, and then open up a certain space of memory, store it in the memory, where is to filter the data after join on,

After filtering the grouping and grouping, it can be said that the field is basically finalized

The above three sub-queries may also squeeze the space opened up by the first select in the data storage of the second select, causing data confusion. The data can only be reduced, not increased.

It can be understood as circle the range first and then calculate the data

Summary:

The join connection is over here, if you don’t understand the cutie, you can chat with me privately,