left outer join

Question

left outer join

juliane26

SSC Eights!

Points: 924
More actions
September 24, 2008 at 4:51 am

#193192

when doing a
select count(*) from A
should that not return the same as
select count(*)
from A left outer join B
on A.x=B.x
I always thought that left outer join is filling up columns as long as they are not null in B, but not adding any additional rows.
What is wrong ?

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

HanShi SSC-Dedicated Points: 33507 More actions · Answer 1

A LEFT OUTER JOIN never returns less rows then the left table (table A in your sample). But if table B is joined to table A in a one-to-many relation, a LEFT OUTER JOIN returns more rows than table A contains, because several rows in table A will be 'copied' to connect to the corresponding rows in table B.

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

juliane26 SSC Eights! Points: 924 More actions · Answer 2

of course I have to add that assuming in B to all key values is only one qualifying row.

What I am trying is rewriting 10 update queries into one SELECT INTO statement, and when the update works I was assuming that it should work the same with a left outer join.

Like now I have

- SELECT c1, cast(null ..) as c2, cast(null..) as c3 FROM A INTO newtable

- UPDATE newtable set c2=B.c2 where newtable.c1 = B.c1

I would like to have

SELECT A.c1, B.c2 from A left outer join B on A.c1=B.c1 INTO newtable

but this is returning a different number of rows adding a few.

So I follow that in table B I have more than one entry for each value in c1. (I will check on this)

If so - why does the update succeed then ?

HanShi SSC-Dedicated Points: 33507 More actions · Answer 3

juliane26 (9/24/2008)
So I follow that in table B I have more than one entry for each value in c1. (I will check on this)
If so - why does the update succeed then ?

I'm sure you'll find more entries for each value in B.c1. The UPDATE (first sample) succeeds, but some rows will be updated multiple times(!) and only keep the last updated value.

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

juliane26 SSC Eights! Points: 924 More actions · Answer 4

HanShi,

thanks for your post:

yes, I found more than one entry for a lot of values.

Oh well - I still have to learn - coming from other DBMS I did not expect the update to go through with multiple entrys 🙂

Thank you very much for the clarification.

juliane26 SSC Eights! Points: 924 More actions · Answer 5

Just thinking about this: having such a 'not unique' update - how often would an update trigger fire ?? Just once or for every qualifying entry?

HanShi SSC-Dedicated Points: 33507 More actions · Answer 6

I quess a UPDATE-trigger would be fired multiple times, but I'm not sure. You can test this by letting the trigger add a row with the updated values to a (audit-)table. Run the query once and see how many records are logged.

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

Don Baun SSC Enthusiast Points: 122 More actions · Answer 7

In general, also remember that a Left Outer join can return FEWER records, if you use a Where clause that restricts what is returned from the additional table. Anything in the Where is applied as if the join, at that moment, was an Inner one. The Where is applied after the join to filter what gets returned.

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 8

HanShi (9/24/2008)
juliane26 (9/24/2008)
So I follow that in table B I have more than one entry for each value in c1. (I will check on this)
If so - why does the update succeed then ?
I'm sure you'll find more entries for each value in B.c1. The UPDATE (first sample) succeeds, but some rows will be updated multiple times(!) and only keep the last updated value.

Actually - no. A row is only updated once in each operation, so if a join might yield the same left rowmultiple times, the row is updated only one time.

Per BOL (in the UPDATE article):

The previous example assumes that only one sale is recorded for a specified salesperson on a specific date and that updates are current. If more than one sale for a specified salesperson can be recorded on the same day, the example shown does not work correctly. The example runs without error, but each SalesYTD value is updated with only one sale, regardless of how many sales actually occurred on that day. This is because a single UPDATE statement never updates the same row two times.

This makes this update non-deterministic, since you can't tell which row was used to perform the update (there's no concept of "first" or "last" row here, and none guaranteed during processing, so you could end up with different results each time.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

HanShi SSC-Dedicated Points: 33507 More actions · Answer 9

Matt Miller (9/26/2008)
Actually - no. A row is only updated once in each operation, so if a join might yield the same left rowmultiple times, the row is updated only one time.

Thanks for the explanation.

I'll keep on learning 😛

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **