MySQL joint table quick query

Time:2021-9-2

Suppose there are the following two tables:

A:

id age
1 11
2 12
3 13
4 14

B:

id name
2 aaa
3 bbb
4 ccc
5 ddd

Inner join

There is only one application scenario, inner join or join, which is equivalent to inner join

select a.*,b.* from a inner join(join) b on a.id = b.id

The results are as follows:

id age id name
2 12 2 aaa
3 13 3 bbb
4 14 4 ccc

MySQL joint table quick query

External connection

1. Left join or left outer join

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

The results are as follows:

id age id name
1 11 null null
2 12 2 aaa
3 13 3 bbb
4 14 4 ccc

MySQL joint table quick query

2.left join + where b.column is null

select a.*,b.* from a left join b on a.id = b.id where b.id is null

The results are as follows:

id age id name
1 11 null null

MySQL joint table quick query

3. Right join or right out join

select a.*,b.* from a right join b on a.id = b.id
id age id name
2 12 2 aaa
3 13 3 bbb
4 14 4 ccc
null null 5 ddd

MySQL joint table quick query

4.left join + where b.column is null

select a.*,b.* from a right join b on a.id = b.id where a.id is null
id age id name
null null 5l ddd

MySQL joint table quick query

5.left join   Union right join (full join is available, but MySQL does not support it)

select a.id aid,a.age,b.id bid,b.name from  a

left join  b

on a.id = b.id

union

select a.id aid,a.age,b.id bid,b.name from  a

right join  b

on a.id = b.id
aid age bid name
1 11 null null
2 12 2 aaa
3 13 3 bbb
4 14 4 ccc
null null 5 ddd

MySQL joint table quick query

6.(left join + is null) union (right join + is null)

select a.id aid,a.age,b.id bid,b.name from  a

left join  b

on a.id = b.id

where b.id is null

union

select a.id aid,a.age,b.id bid,b.name from  a

right join  b

on a.id = b.id

where a.id is null
aid age bid name
1 11 null null
null null 5 ddd

MySQL joint table quick query

Cross join

There is also such a case in practical application. If you want to get the arrangement and combination of a and B records, that is, Cartesian product, it is not easy to express it by sets and elements. Cross join is required.

Cartesian product: Cartesian product refers to the Cartesian product of two sets X and Y in mathematics, also known as direct product, expressed as X × Y. The first object is a member of X and the second object is one of all possible ordered pairs of Y.

select a.id aid,a.age,b.id bid,b.name from tablea a
cross join tableb b
aid age bid name
1 11 2 aaa
2 12 2 aaa
3 13 2 aaa
4 14 2 aaa
1 11 3 bbb
2 12 3 bbb
3 13 3 bbb
4 14 3 bbb
1 11 4 ccc
2 12 4 ccc
3 13 4 ccc
4 14 4 ccc
1 11 5 ddd
2 12 5 ddd
3 13 5 ddd
4 14 5 ddd

You can also specify the where condition

select a.id aid,a.age,b.id bid,b.name from  a
cross join  b
where a.id = b.id
aid age bid name
2 12 2 aaa
3 13 3 bbb
4 14 4 ccc

matters needing attention:

  1. Generally, the where condition is added after cross join, but the use of cross join + on is also interpreted as cross join + where;

  2. Generally, internal connections need to be added with on limiting conditions, such as internal connections; If not, it will be interpreted as cross connection;

  3. If the join table uses a comma, it will be interpreted as a cross join;

This article is taken from:https://blog.csdn.net/Jintao_Ma/article/de…

This work adoptsCC agreement, reprint must indicate the author and the link to this article

Recommended Today

“Self test” stay up late to summarize 50 Vue knowledge points, all of which will make you God!!!

preface Hello everyone, I’m Lin Sanxin. A lot of things have happened these days (I won’t say what’s specific). These things have scared me to treasure my collection these yearsVue knowledge pointsI took out my notes and tried my best to recall them. Finally, I realized these 50Knowledge points(let’s not be too vulgar. It’s not […]