Hello firebird-support@yahoogroups.com!

CREATE TABLE AAA_TABLE_A
(ID D_INTEGER);

CREATE TABLE AAA_TABLE_B
(ID D_INTEGER, AAA_TABLE_A D_INTEGER, MY_VALUE D_INTEGER, MY_VALUE2 D_INTEGER);

DELETE FROM AAA_TABLE_A;
INSERT INTO AAA_TABLE_A (ID) VALUES (1);
INSERT INTO AAA_TABLE_A (ID) VALUES (2);
INSERT INTO AAA_TABLE_A (ID) VALUES (3);
INSERT INTO AAA_TABLE_A (ID) VALUES (4);
INSERT INTO AAA_TABLE_A (ID) VALUES (5);

DELETE FROM AAA_TABLE_B;
INSERT INTO AAA_TABLE_B (ID, AAA_TABLE_A, MY_VALUE, MY_VALUE2) VALUES (11, 1, 333, 666);
INSERT INTO AAA_TABLE_B (ID, AAA_TABLE_A, MY_VALUE, MY_VALUE2) VALUES (22, 2, 444, 888);
INSERT INTO AAA_TABLE_B (ID, AAA_TABLE_A, MY_VALUE, MY_VALUE2) VALUES (33, 4, 333, 666);
INSERT INTO AAA_TABLE_B (ID, AAA_TABLE_A, MY_VALUE, MY_VALUE2) VALUES (44, 4, 444, 888);
INSERT INTO AAA_TABLE_B (ID, AAA_TABLE_A, MY_VALUE, MY_VALUE2) VALUES (55, 4, 222, 444);
INSERT INTO AAA_TABLE_B (ID, AAA_TABLE_A, MY_VALUE, MY_VALUE2) VALUES (66, 5, 111, 222);

SELECT A.ID AS TABLE_A
FROM AAA_TABLE_A A
WHERE A.ID NOT IN (SELECT AAA_TABLE_A
FROM AAA_TABLE_B BB
INNER JOIN AAA_TABLE_A AA ON AA.ID=BB.AAA_TABLE_A
WHERE BB.MY_VALUE = 444 AND BB.MY_VALUE2 = 888)
;

SELECT A.ID AS TABLE_A, B.ID AS TABLE_B, B.MY_VALUE, B.MY_VALUE2
FROM AAA_TABLE_A A
LEFT JOIN AAA_TABLE_B B ON B.AAA_TABLE_A = A.ID
WHERE NOT (B.ID IS NOT NULL AND B.MY_VALUE = 444 AND B.MY_VALUE2 = 888)
;

I have created very simple test to check NOT IN vs. JOINs where NOT IN
works file, but it is much slower than JOINs. Of course I didn't
create here any index, but for few records it doesn't make sense.

I need to get very often a set of record based on subselect which is
very easy to be solved by JOINs only in case I don't need NOT IN.

Does anybody have an idea who to adapt my JOIN version of SELECT where
no record with AAA_TABLE_A.ID = 4. Records 1, 3 and 5 are correct
only.

Thanks in advance.


--
Best regards, TRoland
http://www.rotursoft.sk
http://exekutor.rotursoft.sk