Alternative to ratty sollution?

bubblenut

Hi,

DBM: MySQL 4.1

Quite often I find I need to only show results in table a when they do no appear in table b. I use the following method.

SELECT tablea.*
FROM tablea
LEFT JOIN tableb ON (tablea.field=tableb.field)
WHERE tableb.field IS NULL

This seems like a really ratty sollution and I was just wondering if anyone knows of a tidier way of doing it?

Roger_Ramjet

Nothing 'ratty' about that - standard solution. Any other approach is just going to take more processing and be error prone. This standard solution works solely through the indexes and does not require table access to find the data, only to return the results.

It does what it sez on the packet: find all the records in table A that are not in table B

bubblenut

Yea, that's what everyone here in the office is telling me. One guy put it quite well and kind of summed up what I had in mind.

What, like an anti-join?

So maybe LEFT NOT JOIN tableb ON (... would certainly look tidier I think.

Just a thought.

Roger_Ramjet

So get involved in the SQL standards definition process and try to make it happen.

I know what you mean though, not an obvious construct for a beginner. But I doubt any db publisher is going to want to overload their design with additional functionality when existing functionality works so well.

Look, SQL is all Set Theory and for those people with that turn of mind it is as beautifull as Monet's Waterlillies, for everyone else it is the Manchester Ship Canal - a stagnant cesspool full of supermarket trolleys and plastic bags.