Re: Help with SQL syntax

> In article <4argvp$djk@soap.news.pipex.net>, deanh@Unipalm.Pipex.Com (Dean hobbs) says: > > > >I am trying to find records in one table that do not have matchin entries > >in another table. I have done this before in Access but cannot get it to > >work with Informix. Here is the syntax I am using. > > > >SELECT account.acc_name, account.password FROM account, OUTER temp WHERE> >account.acc_name = temp.acc_name AND temp.acc_name IS NOT NULL To the extent this could have worked at all, it should of course have contained IS NULL at the end if you want those records from the account table that are NOT in the temp table. But it can't work. As Bernhard Rawein explained this doesn't work as the OUTER will return all records from the account table wether they exist in the temp table or not, and set temp.acc_name to null if they do not exist in the temp table. Even your where condition won't remove these. NB! This is *not* an Informix problem, but behaviour according to the SQL standard. > > > >Can anybody tell me where I am going wrong. In the manual it says that > >there should be a null entry in the 2nd table which is what I would expect > >but I still can't get it to work. > > > > tonytd@ttyrwhit.demon.co.uk (Tony Tyrwhitt-Drake) writes: > How about > > SELECT account.acc_name, account.password FROM account > WHERE account.acc_name NOT IN ( SELECT temp.acc_name FROM temp) ;> > and <100443.2356@compuserve.com> Bernhard Rawein writes: > SELECT ... FROM account WHERE NOT EXISTS > (SELECT * FROM temp WHERE temp.acc_name IS NOT NULL > AND temp.acc_name = account.acc_name) Both of the above will work. (In the last case I don't think the test on temp.acc_name IS NOT NULL is nessecary.) However be aware that both of these alternative may give you very bad performance. What you will be able to achive is dependent on what version of Informix you have (what optimser) and of course the indexing, what statistics you have updated and so on. In some cases we have had real bad performance. These kinds of subselects are hard to optimise. An alternative we often use is to go via a temporary table like this: SELECT account.acc_name, account.password, temp.acc_name tacc_name FROM account, OUTER temp WHERE account.acc_name = temp.acc_name INTO temp tt_1 SELECT account.acc_name, account.password FROM tt_1 WHERE tacc_name is null drop table tt_1 We have found this to be faster in very many cases. In some cases several hundred times faster! Nils.Myklebust@ccmail.telemax.no NM-data, Aasesvei 71, 1300 Sandvika, Norway My opinions are those of my company