Difference between leftjoin and fulljoin in MySql and SqlServer

I just wrote a select statement using mysql and used some connection functions in the middle. I found that there are still many differences between the two. First, let's explain the environment: OS: windows2003MySql: Serverversion5.0.37SqlServer: 2000 + sp4. The simplified question is as follows: 3 tables st, rk, ck, st (idint) id1234

I just wrote a select statement using mysql and used some connection functions in the middle. I found that there are still many differences between the two. First, let's explain the environment: OS: windows 2003 MySql: Server version 5.0.37 SqlServer: 2000 + sp4. The simplified question is as follows: 3 tables st, rk, ck, st (id int) id 1 2 3 4

I just wrote a select statement using mysql and used some connection functions in the middle. I found that there are still many differences between the two.

First, explain the environment:

OS: windows 2003

MySql: Server version 5.0.37

SqlServer: 2000 + sp4

The simplified question is as follows:

3 tables st, rk, ck,

St (id int)

Id

1

2

3

4

5

Rk (id int fk st. id, rk int)

Id	Rk
1	4
2	5
3	6

Ck (id int fk st. id, ck int)

Id	Ck
2	3
3	4
4	1

Now we need to use a select statement to achieve this output.

Id	Rk	Ck
1	4	0
2	5	3
3	6	4
4	0	1

Because the MySql editor I used is not very convenient, I 'd like to simulate it in SqlServer.

Using full join, I quickly realized the desired result. The statement is as follows:

Select isnull (rk. id, ck. id) id, isnull (rk, 0) rk, isnull (ck, 0) ck
From rk
Full join ck
On rk. id = ck. id

Then put the statement in MySql, and the problem arises. An error is reported directly, prompting that the full join error is absent.

I suspect that MySql does not support full join. After reading it on google, I can see that many of them are directly done using full join, that is

Select ifnull (rk. id, ck. id) id, ifnull (rk, 0) rk, ifnull (ck, 0) ck
From rk
Full join ck
On rk. id = ck. id

Here, the ifnull function of MySql is equivalent to the isnull function of SqlServer, that is, if arg1 is null, arg2 is returned; otherwise, arg1 is returned.

However, I cannot make any adjustments.

I checked it on the official website. I didn't find any details. Later I saw in an article that the original version 5.1 + had full join. OMG ~~~~~~

Previously, we used left join and right join to simulate the combination.

I also wanted to open it here. after hitting the wall, I did not turn back, but crossed the past.

With left and left, the above results are achieved:

Select st. id, ifnull (rk. rk, 0) as rk, ifnull (ck. ck, 0) as ck
From st
Left join rk on (st. id = rk. id)
Left join ck on (st. id = ck. id)
Where rk! = 0 or ck! = 0

Of course, you can also follow the method described in the previous article to use left union right

Select * from rk
Left join ck on rk. id = ck. id
Union
Select * from rk
Right join ck on rk. id = ck. id

It seems that it is easier to understand and more universal. It is estimated that SqlServer is implemented in this way. Full join is the standard of sql92.

There is another difference:

MySql supports this join syntax, which is not supported by SqlServer

Select *
From st
Left join (ck, rk)
On (rk. id = st. id and ck. id = st. id)

The result is:

Id	Rk	Ck
1
2	5	3
3	4	6
4
5

As you can see, it is not what I want above. The specific syntax is not detailed. I feel that it is the first join in the join brackets.

Or left join (A, B) is A "join and" statement, while left join a xxxxxx left join B XXXXXX is "join or ".

It is estimated that the former is not sql92.

MySql, full join, left join