sqlite3 - SQL WHERE statements, where column may be value or may be NULL -

i have sql table (in sqlite3) in trying aggregate information several other tables, , records in 1 table may or may not have corresponding record in table. query supposed include in aggregate table both records , without linked information. example:

            create table all_households                 select pop.uid pop_uid,                        pop.surname,                        pop.given,                        pop.age,                        pop.real_property,                         farm.uid farm_uid,                        farm.improved_acres,                        farm.unimproved_acres,                        farm.cash_value,                        farm.corn,                        farm.cotton                         pop, farm                        pop.farm_id = farm.uid; 

this looking @ data census schedules. in census have basic pop information -- surname, given name, value of real property -- not has farm. individuals have value in farm_id column on pop, corresponding record of person's farm on farm; otherwise farm_id null.

but naturally, above query fetch individuals whom pop.farm_id = farm.uid -- is, have farms, , have values farm_id. farmless individuals excluded, , want include them, empty values relevant farm columns in all_households.

now, know solve this, , have far, separate select statements each linked column, so:

            create table all_households                 select uid pop_uid,                        surname,                        given,                        age,                        real_property,                         (select uid farm pop.farm_id = farm.uid) farm_uid,                        (select improved_acres farm pop.farm_id = farm.uid) improved_acres,                        (select unimproved_acres farm pop.farm_id = farm.uid) unimproved_acres,                        (select cash_value farm pop.farm_id = farm.uid) cash_value,                        (select corn farm pop.farm_id = farm.uid) corn,                        (select cotton farm pop.farm_id = farm.uid) cotton                         pop; 

but seems terribly clunky , inelegant. so, wondered if there way make first query above pick entries pop farm_id null:

            pop.farm_id = farm.uid or pop.farm_id null; 

but things went haywire, , i'm not sure why. in real, unsimplified query, i'm dealing 4 tables, each column on pop may value or may null, , though first query above written took seconds, query hung. forever. , when came back, had died error "database or disk full." whatever did, seem have elicited kind of endless loop. tried alternately:

            (case when pop.farm_id not null pop.farm_id = farm.uid else 1 end); 

but had same result before. can shed light on i'm doing wrong, or might better? thanks.

your attempt use farm_id null slow because database attempted give combination of each farm record each pop record null value. furthermore, optimizing constraints or not easy , done temporary table.

to matched/joined records, , records first table no corresponding farm, combine 2 queries union all:

select pop. ..., farm. ... pop join farm on pop.farm_id = farm.uid  union  select pop. ..., null, null, ... pop pop.farm_id null 

this construct called outer join , supported directly in sql databases (sqlite supports left joins, want here):

select pop. ..., farm. ... pop left outer join farm on pop.farm_id = farm.uid 

please note outer join returns unmatched records, return pop records invalid farm_id.