Now I'll share with you some of the things I've learned. Because I've made progress before, I'll share them with you first. After learning, I'll take time to summarize the knowledge (^ - ^). If you find any problems, you are welcome to criticize and guide. I will reply at the first time.

Get to the point:

1, Multi table query:
In actual development, it is often necessary to operate on two or more data tables, which need to be associated with primary keys and foreign keys, and then use join query to query the data records that meet the requirements in multiple tables.

One SQL statement queries multiple tables to get a result that contains the data of multiple tables. efficient. In SQL99, join queries need to be implemented using the join keyword.

It provides a variety of connection query types: cross ， natural ， using ， on

1. cross join

select * 
from emp
join dept;-- cross It can be omitted, mysql Yes, orcale Not in

The above code cross connects the emp table and dept table, and the number of query results is calculated in the form of Cartesian product,

This will lead to a lot of errors in the query results. Useless data is generally not used. It has no practical significance, but it has theoretical significance. Just understand it.

2. In order to solve the problems caused by cross connection, we introduce natural join learning

select * 
from emp
natural join dept;

Advantages: automatically match all columns with the same name. The columns with the same name are displayed only once, which is simple

Disadvantages: 1 When we query fields, because no specific table name is specified, the query efficiency will be low when there are many connected tables

Solution: of course, specify the table name. Ha ha

select emp.empno,emp.ename,emp.deptno,dept.dname,emp.sal,dept.loc
from emp
natural join dept;

There are still some problems in the above figure. If the table name is too long, it will be troublesome. Therefore, in from emp (alias can be used when specifying the table) - > > > from emp e ---- the above emp You can change it to E

Disadvantages: 2 Using natural query, all columns with the same name will be matched, so it is very difficult when we only want to match some columns with the same name.

3. Solution: using sentence

select * from emp e join dept d 
using(deptno)--using You can specify the column with the same name you want to match in the parentheses 

The above code is no longer a natural connection. It is an inner connection. Omitting the inner --- inner join --- > > inner connection before the join refers to the intersection after the connection of table a and table b.

using also has obvious disadvantages

--Disadvantages of using: associated fields must have the same name, but there are many fields that express one meaning, but the field names are different, which is very common
4. -- solve the - on clause of inner connection:

select *
from emp e
inner join dept d
on(e.deptno1 = d.deptno2);

Use the on statement to associate different name segments. Of course, you can continue to add filter conditions after the on statement, such as where and so on

Here are some points to note:

--Conditions:
-- 1. Filter criteria: where having
-- 2. Connection condition on,using,natural
--SQL99 syntax: filter conditions and join conditions are separated

5. External connection

First of all, it is clear that the inner connection is the intersection, and the outer connection is naturally the union

--inner join - on clause: displays all matching information

However, we know that there are not only matching information in different tables, but also a lot of mismatched information. At this time, we need to use external connection - external connection: in addition to displaying matching data, we can also display mismatched data

-- Left outer connection: left outer join   -- The information of the table on the left can be checked even if it does not match
select *
from emp e
left outer join dept d
on e.deptno1 = d.deptno2;

-- Right outer connection: right outer join   -- The information of the table on the right can be checked even if it does not match

select *
from emp e
right outer join dept d
on e.deptno1 = d.deptno2;


-- Total external connection  full outer join -- This grammar is in mysql Not supported in, in oracle Medium support 
-- Display all unmatched data in the left and right tables 

select * 
from emp e
full outer join dept d
on e.deptno1 = d.deptno2;

How do we solve the problem of full connection in MySQL?

-- solve mysql The problem that global external connection is not supported in:

select *
from emp e
right outer join dept d
on e.deptno1 = d.deptno2;
union  -- Union de duplication
select * 
from emp e
left outer join dept d
on e.deptno = d.deptno;


select *
from emp e
right outer join dept d
on e.deptno1 = d.deptno2;
union  all-- Union without duplication and high efficiency**
select * 
from emp e
left outer join dept d
on e.deptno1 = d.deptno2;

-- mysql The support for sets in is weak. Only union operations, intersection and difference sets are supported( oracle (supported in)
-- outer It can be omitted

The next chapter is for you to share, self join and multi table query.

Will you and I pay attention to each other and make progress together?

--- little white rabbit