Small knowledge, big challenge! This article is participating in the creation activity of “Essential Tips for Programmers”.

preface

This topic came to me when I was doing a project by chance, and I had a systematic study due to my poor skills. The purpose of this presentation is to improve myself and solve cartesian product duplication problems in existing projects.

This article will have table fields and SQL statements to help you quickly understand, speak bad or wrong place also need to be pointed out, let me timely correction!

There are many versions of the Sql language, and I use Mysql syntax to describe it. In RDBMS (relational database) SQL is the foundation of the database.

Multi-table join query

Single table is relatively simple to suggest that we search online by ourselves, for basic learning.

Create table A contents:

Create table B:

Inner Join

Inner join can be written in three ways, one with inner and one as where.

# the firstselectThe title*
from a 
    inner join b on a.id = b.rid
Copy the code

# the secondselect 
    *
from a 
    join b on a.id = b.rid
Copy the code

# the thirdselect 
    *
from a,b 
where a.id = b.rid
Copy the code

The above three methods are equivalent, the only difference is that the third is invisible connection, the first is explicit connection, due to the understanding and specification of the third gradually fade out of view, the most used or the first.

The inner join will display the contents that match both tables, and the contents that do not match will be removed directly. The connection results in the above table are as follows:

Left outer Join (Left Join

Left JOIN is short for left Outer Join. A left JOIN is displayed if there is a left join and null if there is no right join.

select * 
from a left join b on a.id = b.rid

Copy the code

The result shows that 3 is the right table with null

Left Join is a very important statement that we use most of the time

Right outer Join (Right Join

Right Join is short for right Outer Join. Right join is displayed on the right and null on the left.

select * 
from a right join b on a.id = b.rid
Copy the code

Because right Join filters the right table, all the right table records are displayed, the left table if there is, display null

You can see from the result that the coordinates are missing one record because the right table has no record corresponding to the left table

Full Join

Full join is special. Oracle database supports full Join but mysql does not. We generally use union to join query results to achieve full join effect.

select * 
from a left join b on a.id = b.rid
union
select * 
from a right join b on a.id = b.rid

Copy the code

Full join will display all data from the left and right tables, null if there is no left table, null if there is no right table

Cross Join

The cross join produces a Cartesian product that forms n x m columns of data

# the first
select * 
from a, b

select * 
from a cross join b
# the second
Copy the code

Both can be cross-connected. Generally, cross-connection is seldom used, and screening conditions are generally used for further screening.

conclusion

1. Full Join is not supported in some databases
2. The nature of the left and right connections is the same, and their properties are opposite