SQL - Any faster Method ?

[TomCH]

Hi,
I have 2 tables. One table contains one field called
"shortnames".Second table contains many columns including "shortnames". I
want to get all the shortnames from second table which are not present in
the first table.

I have a query like this:
"SELECT T1.shortname FROM TT_Reference_Tree AS T1 WHERE T1.shortname NOT
IN(select distinct shortname from table1);"

But the problem is that the execution time of this is very high. Means more
than one or 2 minutes. Is there any other fast method for the same.

Tomch

 

[RickCole]

Maybe something like this:

Because an outer join includes unmatched rows, you can use it to find rows that violate foreign key constraints. To do so, you create an outer join and then add a search condition to find rows in which the primary key column of the rightmost table is null. For example, the following outer join finds rows in the employee table that do not have corresponding rows in the jobs table:

SELECT employee.emp_id, employee.job_id
FROM employee LEFT OUTER JOIN jobs
ON employee.job_id = jobs.job_id
WHERE (jobs.job_id IS NULL)


Rick.