SQL MINUS operator rejected by Jet?

[davidchardonnet]

Hello everybody,

I would like to perform a query like:

SELECT a FROM T1
MINUS
SELECT b FROM T2

I used this syntax correctly on Oracle, but the Jet SQL engine seems to reject it. Does anybody knows a turnaround for this question?

Thank you for your answers

David

 

[Steve101]

I'm not familiar with this syntax. Does it mean to select all of the records from T1 where a matching "a=b" entry does not appear in T2. If so, you could do it this way:

SELECT a
FROM T1
WHERE a NOT IN
(SELECT b
FROM T2)

Hope this is along the lines of what you're looking for. Access SQL does not support the MINUS operator. Cheers,

Steve Lewy
Solutions Developer
steve@lewycomputing.com.au

 

[davidchardonnet]

Thank you Steve, indeed that's what this syntax means, but I tried to use the 'NOT IN' syntax before the minus but i found it extremely looooooong to execute, so i had to kill Access before it had finished executing the request.
I thought that the MINUS operator could solve that...

Thanks again

David

 

[Steve101]

Yes, if you have large tables, this would not be very efficient. Appropriate indexing might help though. You might also be able to do it more efficiently using an appropriate JOIN between the two tables; for example:

SELECT T1.*
FROM Table1 AS T1
LEFT JOIN Table2 AS T2
ON T1.a = T2.b
WHERE T2.b Is Null;

Again, make sure that both fields referenced in the join are indexed. Let me know if this helps.
Cheers,

Steve Lewy
Solutions Developer
steve@lewycomputing.com.au