Doychin Bondzhev skriver: > On 23.1.2013 г. 20:18 ч., Alexandre Benson Smith wrote: >> Hi ! >> >> It's known that Firebird transforms this kind of query: >> >> select >> TableA.Foo >> from >> TableA >> where >> TableA.Bar in (select TableB.Bar where X=Y) >> [snip] >> >> I know there are other ways to write the same query, but in some cases I >> could not rewrite the whole query, but just add a WHERE clause... > In such case I usually go with left outer join. > > select * from tableA > left outer join tableb on tableb.bar = tablea.bar and x=y > where tableb.bar is not null > > I'm not sure this will work in your case but this really speeds up the > execution.
I think Alexandre knows this already. :-) Just for ref, the left join will cause duplicates if the referenced table (the lookup table) contains multiple records with a matching Bar value. I think the idea is that the improved optimization for in queries would execute as fast as the left join but without the duplication problem. Kjell -- ------------------------------ Kjell Rilbe DataDIA AB E-post: kjell.ri...@datadia.se Telefon: 08-761 06 55 Mobil: 0733-44 24 64 ------------------------------------------------------------------------------ Master Visual Studio, SharePoint, SQL, ASP.NET, C# 2012, HTML5, CSS, MVC, Windows 8 Apps, JavaScript and much more. Keep your skills current with LearnDevNow - 3,200 step-by-step video tutorials by Microsoft MVPs and experts. ON SALE this month only -- learn more at: http://p.sf.net/sfu/learnnow-d2d Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
