NOT IN (SELECT...) should work as well, possibly a little bit slower. Your mistake was that you try to compare one field of one record set with all fields of another record set.
Code: Select all
SELECT columns
FROM A, B
WHERE A.X = B.Y
... is perfectly equivalent to ...
Code: Select all
SELECT columns
FROM A INNER JOIN B
ON A.X = B.Y
The INNER keyword is optional. A JOIN B means the same.
Code: Select all
SELECT columns
FROM A LEFT OUTER JOIN B
ON A.X = B.Y
... returns the same records as the inner join plus all the non-matching records of the left table with Null values for the columns of the right table.
The OUTER keyword is optional. A LEFT JOIN B means the same.
Code: Select all
SELECT columns
FROM A RIGHT OUTER JOIN B
ON A.X = B.Y
... returns the same records as the inner join plus all the non-matching records of the right table with Null values for the columns of the left table.
The OUTER keyword is optional. A RIGHT JOIN B means the same.
The following is not supported by HSQL 1.8
Code: Select all
SELECT columns
FROM A OUTER JOIN B
ON A.X = B.Y
It should return the same records as the inner join plus all the non-matching records of the right table with Null values for the columns of the right table plus all the non-matching records of the left table with Null values for the columns of the left table.
With HSQL 1.8 you get an OUTER JOIN from this:
Code: Select all
SELECT columns
FROM A LEFT OUTER JOIN B
ON A.X = B.Y
UNION SELECT columns
FROM A RIGHT OUTER JOIN B
ON A.X = B.Y
Of course you knew that already.
P.S.: Tutorial with attachment:
viewtopic.php?f=83&t=96654