Are these two SQL queries the same?-CodePudding

Is this sql query:

update x
        set Amount = Amount * -1
from XTable x
        join WTable w ON w.ID = x.id
        join YTable y ON y.id = x.id
where y.negative = 1
    
update x
        set Amount = Amount * -1
from XTable x
        join ZTable z ON z.id = x.id
        join YTable y ON y.id = x.id
where y.negative = 1

.... the same as this?

update x
    set Amount = Amount * -1
from XTable x
        join WTable w ON ON w.id = x.id
        join YTable y ON y.id = x.id
    Full Outer Join ZTable z ON z.id = x.id
where y.negative = 1

Will they always return the same table in every single case?

CodePudding user response：

Indeed they are... full join operates over the join between x, y and w. So it gets this subset, plus z, with all the records from this table, joined or not. After, the y.negative = 1 filter applies on the full data set, so all records from z not joined are rejected because they have a y.negative = null.

I would use this one to do it with just one query, however, just for a better understanding (and probably performs better than full join):

update x
  set Amount = Amount * -1
from XTable x
  join YTable y ON y.id = x.id
  left join WTable w ON w.ID = x.id
  left join ZTable z ON z.id = x.id         
where y.negative = 1
  and (z.id is not null or w.id is not null) -- non sargable version --> isnull(z.id, w.id) is not null

This way you filter y.negative = 1 and update x just when w or z are joined (maybe some y.negative = 1 has a null,null for these tables...)

CodePudding user response：

They are not the same.

In a simplified test there are differences in the result.

A) the first (2 updates)

id	Amount	negative	x	w	y	z
1	4	True	1	1	1	1
2	4	False	2	2	2	2
3	2	null	3	3	null	null
4	2	True	4	null	4	null
5	2	null	5	null	null	5
6	-3	True	6	6	6	null
7	-3	True	7	null	7	7
9	1	null	9	null	null	null

B) the second (single update)

id	Amount	negative	x	w	y	z
1	-4	True	1	1	1	1
2	4	False	2	2	2	2
3	2	null	3	3	null	null
4	2	True	4	null	4	null
5	2	null	5	null	null	5
6	-3	True	6	6	6	null
7	3	True	7	null	7	7
9	1	null	9	null	null	null

So there's a difference in id 1 & 7

Here's a single update that gives the same results as the first double update.

-- Extra
update x
    set Amount = Amount * -1
from XTable x
join YTable y ON y.id = x.id and y.negative = 1
left join  WTable w ON w.id = x.id
left join  ZTable z ON z.id = x.id 
where ((w.id is null and z.id is not null)
    or (w.id is not null and z.id is null))

But I assume you want something like this

-- Extra 2
update x
    set Amount = Amount * -1
from XTable x
join YTable y ON y.id = x.id and y.negative = 1
left join  WTable w ON w.id = x.id
left join  ZTable z ON z.id = x.id 
where (w.id is not null or z.id is not null)

id	Amount	negative	x	w	y	z
1	-4	True	1	1	1	1
2	4	False	2	2	2	2
3	2	null	3	3	null	null
4	2	True	4	null	4	null
5	2	null	5	null	null	5
6	-3	True	6	6	6	null
7	-3	True	7	null	7	7
9	1	null	9	null	null	null

Test on db<>fiddle here