SQL Help please

[DimPrawn]

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

I weep for you both. Amateurs.

If you say so. You's the daddy.

[scotspine]

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

sure thing dp. us amateurs need to stick together.

[lilelvis2000]

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]

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]

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]

potentially big

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]

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....