MySQL multi table query

Mysql Multi-table query

The foundation of multi table query is based on Cartesian product ！, To understand the execution process of Cartesian product ！

• The drive table is scanned only once , The driven table may be queried many times

One 、 Internal connection

Internal connection ： Find the intersection of two tables （ Common ）

select * from  surface 1 t,  Table two  c where t.id = ci.d;（ Implicit inner join ）
select * from  surface 1 t inner join  Table two  c on t.id = ci.d;（ Implicit inner join ）
select * from  surface 1 t join  Table two  c on t.id = ci.d;（ Explicit inner connection ）

• In connection ,【 The driver table 】 It is automatically selected after system optimization , Will implement the plan 【 Less scanning 】 Table selection of 【 The driver table 】

Two 、 External connection

The difference between internal and external connections ：

• about 【 Internal connection 】 Two watches in , if 【 The driver table 】 The record in 【 Was the driver table 】 No matching record found in , Then the record will not be added to the final result set .
• about 【 External connection 】 Two watches in , Even if 【 The driver table 】 The record in 【 Was the driver table 】 No matching record found in , Also add this record to the final result set , For different 【 Drive table selection 】, External connections can also be divided into 【 The left outer join 】 and 【 Right connection 】.

Left lateral

The left table is the driver table ：

select * from  Table 1  t left join  Table two  c on t.id = c.id;

Right outside

The right table is the drive table ：

select * from  Table 1  t right join  Table two  c on t.id = c.id;

3、 ... and 、 Full connection

Although my MySQL This method is not supported , It can be solved in other ways , as follows .

SELECT * from teacher t right outer join course c on c.t_id = t.id
union
SELECT * from teacher t left outer join course c on c.t_id = t.id

In this result , Whether it's the left table or the right table , All the data is included in the result set ：( duplicate removal )

Four 、 Subquery

• Environmental preparation ：

DROP TABLE IF EXISTS emp;
DROP TABLE IF EXISTS dept;


#  Create department table 
	CREATE TABLE dept(
        did INT PRIMARY KEY AUTO_INCREMENT,
        dname VARCHAR(20)
    );

	#  Create an employee table 
	CREATE TABLE emp (
        id INT PRIMARY KEY AUTO_INCREMENT,
        NAME VARCHAR(10),
        gender CHAR(1), --  Gender 
        salary DOUBLE, --  Wages 
        join_date DATE, --  Date of entry 
        dep_id INT,
        FOREIGN KEY (dep_id) REFERENCES dept(did) --  Foreign keys , Related department table ( The primary key of the Department table )
    );
	--  Add Department data 
	INSERT INTO dept (dNAME) VALUES (' R & D department '),(' The Marketing Department '),(' Finance Department '),(' The sales department ');
	--  Add employee data 
	INSERT INTO emp(NAME,gender,salary,join_date,dep_id) VALUES
	(' The Monkey King ',' male ',7200,'2013-02-24',1),
	(' Pig eight quit ',' male ',3600,'2010-12-02',2),
	(' Tang's monk ',' male ',9000,'2008-08-08',2),
	(' Bones jing ',' Woman ',5000,'2015-10-07',3),
	(' Spider essence ',' Woman ',4500,'2011-03-14',1),
	(' Small white dragon ',' male ',2500,'2011-02-14',null);	

• Concept

Nested query in query , A nested query is called a subquery .

What is nested query in query ？ Let's take an example ：

demand ： Query the information of employees whose salary is higher than Zhu Bajie .

To fulfill this requirement , We can achieve... In two steps , First step ： Find out first Pig Bajie's salary

select salary from emp where name = ' Pig eight quit '

The second step ： Query the information of employees whose salary is higher than Zhu Bajie

select * from emp where salary > 3600;

In the second step 3600 You can go through the first step sql Find out , So will 3600 Use the first step sql Statement to replace

select * from emp where salary > (select salary from emp where name = ' Pig eight quit ');

This is the nested query statement in the query statement .

Sub queries differ according to the query results , The effect is different

• The result of subquery statement is single row and single column , Subquery statement As a condition value , Use = != > < Wait for conditional judgment
• The result of subquery statement is multiple rows and single column , Subquery statement As a condition value , Use in And other keywords for conditional judgment
• The result of subquery statement is multi row and multi column , Subquery statement As a virtual table

** Case study ：** Inquire about ‘ Finance Department ’ and ‘ The Marketing Department ’ All employee information

SELECT * from dept where dname = ' Finance Department ';
-- In fact, we only need Department id that will do , namely ：
SELECT did from dept where dname = ' Finance Department ';

SELECT did from dept where dname = ' The Marketing Department ';

-- To sum up, two sentences ：
SELECT did from dept where dname = ' Finance Department ' or dname = ' The Marketing Department ';
-- The result is multiple rows and single column ！

Subquery statement ： According to the subquery results id To nest

--  Inquire about  ' Finance Department '  perhaps  ' The Marketing Department '  The Department of all employees did
select did from dept where dname = ' Finance Department ' or dname = ' The Marketing Department ';

select * from emp where dep_id in (select did from dept where dname = ' Finance Department ' or dname = ' The Marketing Department ');

Case study ： The date of employment inquiry is ‘2011-11-11’ After that, employee information and department information

-- The date of employment inquiry is '2011-11-11'  Later employee information 
select * from emp where join_date > '2011-11-11';
-- The query result is a multi row and multi column table ！( Virtual table )
-- Connect and query the virtual table with the Department information table 
select * from (select * from emp where join_date > '2011-11-11') t1 join dept on t1.dep_id = dept.did;