SQL Help please

[DimPrawn]

Originally Posted by richard-af

9 times out of 10? Unnecessary table scans??!!!

I weep for you both. Amateurs.

If you say so. You's the daddy.

[scotspine]

Originally Posted by DimPrawn

SP, if anyone else disagrees with me, can you please ban them?

sure thing dp. us amateurs need to stick together.

[lilelvis2000]

Originally Posted by DimPrawn

Don't need to. 9 times out of 10 a join on two columns with appropriate indexes will generate a better execution plan than using IN, which ends up using a hash/match algorithm.

Possibly. depends if the indexes are used or not. Exists might work better. Who knows? Only can tell when you see the execution plan. For inner joins I agree the indexes will speed things along, I'm not certain a left outer join where the right side is filtered to be empty will be faster than a Exists. Then, if he's using mySQL the Left join AFAIK does something unlike a left outer join.

[richard-af]

Originally Posted by lilelvis2000

Possibly. depends if the indexes are used or not. Exists might work better. Who knows? Only can tell when you see the execution plan. For inner joins I agree the indexes will speed things along, I'm not certain a left outer join where the right side is filtered to be empty will be faster than a Exists. Then, if he's using mySQL the Left join AFAIK does something unlike a left outer join.

Nice try - but I mentioned Execution Plan first. There can be only one, etc.

[thunderlizard]

chaps,
Performance isn't a problem unless it's a problem.


anyway, I quite fancy
SELECT customerid FROM customer
MINUS
SELECT customerid FROM order;

[King Cnvt]

SQL Server 2005 has an EXCEPT clause which does the same as MINUS.

[richard-af]

Originally Posted by thunderlizard

chaps,
Performance isn't a problem unless it's a problem.


anyway, I quite fancy
SELECT customerid FROM customer
MINUS
SELECT customerid FROM order;

Performance: ... er, and?

That SQL: Hmmm... 2 potentially big sets of data operated on, battering the DB nicely. Where do you work? I could make a killing, even if I just stopped the lights dimming every time you run your lovely SQL!

[thunderlizard]

you're inventing your own nonfunctional requirements there.

When you go down the shops, do you always drive a 54-seater coach because you could "potentially" meet 53 mates wanting a lift along the way?

[richard-af]

Originally Posted by thunderlizard

you're inventing your own nonfunctional requirements there.

When you go down the shops, do you always drive a 54-seater coach because you could "potentially" meet 53 mates wanting a lift along the way?

Size of the data sets was not mentioned, so fair to assume could be large. I win... again.

[King Cnvt]

Couldn't you just ring up all the customers and ask them if they have any orders? FFS, kids of today....