Simple Join Taking a Long Time

#11

On Feb 21, 8:59 pm, "Ed Prochak" <edproc... (AT) gmail (DOT) com> wrote:

Quote:

On Feb 20, 1:21 pm, "Lennart" <Erik.Lennart.Jons... (AT) gmail (DOT) com> wrote:

On Feb 20, 5:59 pm, "greg.fenton" <greg.fen... (AT) gmail (DOT) com> wrote:

On Feb 19, 4:04 pm, "Lennart" <Erik.Lennart.Jons... (AT) gmail (DOT) com> wrote:

I might be wrong, but since:

AND cs.skill_id IN (5, 15)

is part of the join condition, isn't all candidates returned, with or
without skills? I.e. the way the query is expressed I don't think it
will produce the same result as an inner join.

No, that would be the case if you wrote:

AND ( cs.skill_id IN (5, 15) OR cs.skill_id IS NULL )

greg.fenton

can you provide an example where:

OR cs.skill_id IS NULL )

makes a difference?

/Lennart

assume the result set before applying the AND clause has two rws:
JOE 15
BILL NULL (ie Bill has no skills)

then using this:
AND ( cs.skill_id IN (5, 15) OR cs.skill_id IS NULL )
the final result set still has both rows.

BUT the original query had this:
AND ( cs.skill_id = 5 OR cs.skill_id = 15)
BILL fails the condition (NULL does not match either 5 or 15)

IOW the original query in this thread returns the same result as an
inner join.

If you don't believe me, try it.
Ed

[lelle@53dbd181 lelle]$ db2 -tvf example.sql
DROP table candidates
DB20000I The SQL command completed successfully.

CREATE TABLE candidates ( id integer NOT NULL, first_name varchar
(127), surname varchar (127), PRIMARY KEY (id) )
DB20000I The SQL command completed successfully.

DROP table candidates_skills
DB20000I The SQL command completed successfully.

CREATE TABLE candidates_skills ( id integer NOT NULL, candidate_id
integer, skill_id integer, PRIMARY KEY (id) )
DB20000I The SQL command completed successfully.

insert into candidates (id,first_name) values (1,'Joe'),(2,'Bill')
DB20000I The SQL command completed successfully.

insert into candidates_skills (id, candidate_id, skill_id) values
(1,1,15)
DB20000I The SQL command completed successfully.

SELECT c.id, cs.id, cs.skill_id FROM candidates AS c LEFT OUTER JOIN
candidates_skills AS cs ON c.id = cs.candidate_id

ID ID SKILL_ID
----------- ----------- -----------
1 1 15
2 - -

2 record(s) selected.

SELECT c.id, cs.id, cs.skill_id FROM candidates AS c LEFT OUTER JOIN
candidates_skills AS cs ON c.id = cs.candidate_id AND ( cs.skill_id
IN (5, 15) )

ID ID SKILL_ID
----------- ----------- -----------
1 1 15
2 - -

2 record(s) selected.

SELECT c.id, cs.id, cs.skill_id FROM candidates AS c LEFT OUTER JOIN
candidates_skills AS cs ON c.id = cs.candidate_id AND ( cs.skill_id
IN (5, 15) OR cs.skill_id IS NULL)

ID ID SKILL_ID
----------- ----------- -----------
1 1 15
2 - -

2 record(s) selected.

/Lennart

#12

On Feb 21, 8:59 pm, "Ed Prochak" <edproc... (AT) gmail (DOT) com> wrote:

Quote:

On Feb 20, 1:21 pm, "Lennart" <Erik.Lennart.Jons... (AT) gmail (DOT) com> wrote:

On Feb 20, 5:59 pm, "greg.fenton" <greg.fen... (AT) gmail (DOT) com> wrote:

On Feb 19, 4:04 pm, "Lennart" <Erik.Lennart.Jons... (AT) gmail (DOT) com> wrote:

I might be wrong, but since:

AND cs.skill_id IN (5, 15)

is part of the join condition, isn't all candidates returned, with or
without skills? I.e. the way the query is expressed I don't think it
will produce the same result as an inner join.

No, that would be the case if you wrote:

AND ( cs.skill_id IN (5, 15) OR cs.skill_id IS NULL )

greg.fenton

can you provide an example where:

OR cs.skill_id IS NULL )

makes a difference?

/Lennart

assume the result set before applying the AND clause has two rws:
JOE 15
BILL NULL (ie Bill has no skills)

then using this:
AND ( cs.skill_id IN (5, 15) OR cs.skill_id IS NULL )
the final result set still has both rows.

BUT the original query had this:
AND ( cs.skill_id = 5 OR cs.skill_id = 15)
BILL fails the condition (NULL does not match either 5 or 15)

IOW the original query in this thread returns the same result as an
inner join.

If you don't believe me, try it.
Ed

In addition to my other post, do you agree on this (assume the same
tables and content)

SELECT c.id, cs.id, cs.skill_id FROM candidates AS c LEFT OUTER JOIN
candidates_skills AS cs ON 1=0

ID ID SKILL_ID
----------- ----------- -----------
1 - -
2 - -

If you do that, do you still think that adding:

AND cs.skill_id IN (5, 15)

changes the result? And that

(AND cs.skill_id IN (5, 15) OR cs.skill_id IS NULL)

change it back?

/Lennart

#13

On Feb 21, 2:15 pm, "Lennart" <Erik.Lennart.Jons... (AT) gmail (DOT) com> wrote:

Quote:

On Feb 21, 8:59 pm, "Ed Prochak" <edproc... (AT) gmail (DOT) com> wrote:

On Feb 20, 1:21 pm, "Lennart" <Erik.Lennart.Jons... (AT) gmail (DOT) com> wrote:

On Feb 20, 5:59 pm, "greg.fenton" <greg.fen... (AT) gmail (DOT) com> wrote:

On Feb 19, 4:04 pm, "Lennart" <Erik.Lennart.Jons... (AT) gmail (DOT) com> wrote:

I might be wrong, but since:

AND cs.skill_id IN (5, 15)

is part of the join condition, isn't all candidates returned, with or
without skills? I.e. the way the query is expressed I don't think it
will produce the same result as an inner join.

No, that would be the case if you wrote:

AND ( cs.skill_id IN (5, 15) OR cs.skill_id IS NULL )

greg.fenton

can you provide an example where:

OR cs.skill_id IS NULL )

makes a difference?

/Lennart

assume the result set before applying the AND clause has two rws:
JOE 15
BILL NULL (ie Bill has no skills)

then using this:
AND ( cs.skill_id IN (5, 15) OR cs.skill_id IS NULL )
the final result set still has both rows.

BUT the original query had this:
AND ( cs.skill_id = 5 OR cs.skill_id = 15)
BILL fails the condition (NULL does not match either 5 or 15)
[creates/inserts deleted]

SELECT c.id, cs.id, cs.skill_id FROM candidates AS c LEFT OUTER JOIN
candidates_skills AS cs ON c.id = cs.candidate_id

ID ID SKILL_ID
----------- ----------- -----------
1 1 15
2 - -

2 record(s) selected.

SELECT c.id, cs.id, cs.skill_id FROM candidates AS c LEFT OUTER JOIN
candidates_skills AS cs ON c.id = cs.candidate_id AND ( cs.skill_id
IN (5, 15) )

ID ID SKILL_ID
----------- ----------- -----------
1 1 15
2 - -

2 record(s) selected.

[]

/Lennart- Hide quoted text -

- Show quoted text -

Hmm..

I see where I misinterpreted this. I was thinking the AND claues was
part of a WHERE clause, like this:

SELECT c.id, cs.id, cs.skill_id FROM candidates AS c LEFT OUTER JOIN
candidates_skills AS cs ON c.id = cs.candidate_id
WHERE ( cs.skill_id IN (5, 15) )

My mistake. Sorry for the confusion I caused.

Ed.
I guess I better use the newer JOIN syntax more often.

#14

On Feb 19, 2:19 pm, "Walt" <wami... (AT) verizon (DOT) net> wrote:

Quote:

"greg.fenton" <greg.fen... (AT) gmail (DOT) com> wrote:

Yep, using a LEFT OUTER JOIN instead of a simple inner join is a waste
in this case, though a good RDBMS optimizer should recognize that the
criteria exclude NULL rows from the right table and automatically
rewrite the join...

Will you name one RDBMS optimizer that will actually perform the action you
describe above?

I know a few optimizers, and would be very surprised if any of them got this
one.

OMG, that must have been the morning that our coffee machine was
completely on the blink. Re-reading this thread, I haven't a clue
what I was thinking...or if I was...

Sorry for the misdirection.

greg.fenton

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Simple Join Taking a Long Time

comp.databases comp.databases

Re: Simple Join Taking a Long Time - 02-21-2007 , 01:15 PM

Re: Simple Join Taking a Long Time - 02-21-2007 , 01:32 PM

Re: Simple Join Taking a Long Time - 02-21-2007 , 02:51 PM

Re: Simple Join Taking a Long Time - 02-26-2007 , 10:45 AM