this SQL statement :
SELECT agency.card_type FROM agency
WHERE agency.id NOT IN (select issuer from card where person=1)
is equivalent in DAL:
registered_cards=db(db.card.person==1)(db.card.issuer==db.agency.id)._select(db.card.issuer)
unregistered_cards=db((db.agency.is_active==True) &
(~db.agency.id.belongs(registered_cards)))
The following SQL statement is functionally the same as above:
SELECT a.card_type FROM agency AS a
WHERE NOT EXISTS (SELECT issuer FROM card as c WHERE c.issuer=a.id AND
c.person=1)
But according to this page, EXISTS operator (also UNION ALL, LEFT JOIN)
is more efficient and several factors faster than NOT IN:
http://weblogs.sqlteam.com/peterl/archive/2007/09/20/Finding-records-in-one-table-not-present-in-another-table.aspx
I'm curious if there is an EXISTS operator equivalent in DAL?
/r
Nik
