On 2018/08/28 7:18 PM, Jay Kreibich wrote:
On Aug 28, 2018, at 11:30 AM, Joe <[email protected]> wrote:
A (perhaps silly ) beginners question:
My sqlite database contains several tables, two of them, table A and table B,
have text colums called 'nam'. The tables have about 2 millions lines.
What's the most efficient way to select all lines from table A with nam values,
which are not present in the nam values of table B?
Thanks — Joe
Not sure about performance vs other suggestions, but this is a common way of
doing that:
SELECT a.* FROM a LEFT JOIN b USING (nam) WHERE b.nam IS NULL
I have nothing to add in terms of the query - personally I prefer this
version Jay offered (from a point of my internal clarity only). Simon
or Richard's versions will all work equally well in semantic terms
(perhaps one is slightly better due to specific working of the sqlite
internals - test it!), but in all cases one thing is paramount: Ensure
you have an Index on nam in at least the b table (but preferably in both).
The efficiency/speed gain in that will vastly overshadow any choice in
lookup method.
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
