a moment of googling, INTERSECT is when you'd like to find the exact intersection of rows, including NULLs being compared:
http://sqltips.wordpress.com/2007/08/15/difference-between-inner-join-and-intersect/ INTERSECT is an uncommon operator. Eduardo wrote: > Then what is the purpose of the intersection method? It looks to me as > a (bad) alternative to chained filtering!! Can you think of any case > when intersection is better choice than filters? > > On Jun 23, 4:08 am, Mike Conley <mconl...@gmail.com> wrote: >> On Tue, Jun 21, 2011 at 6:05 AM, Eduardo <ruche...@googlemail.com> >> wrote: >> > What is the best practice: to chain filters or to collect queries in a >> > list and then apply intersect_all()? >> >> Overall efficiency will depend on the underlying database engine, but I >> can't help but expect that most databases will be more efficient with >> the >> chained filters query. It would take a really smart optimizer to make >> the >> intersect method as efficient as the chained filter. >> >> Using an unrealistic set of queries, but it shows the principle. >> >> Using intersect_all will generate SQL like this: >> q1 = sess.query(Book).filter(Book.title=='A') >> q2 = sess.query(Book).filter(Book.title=='B') >> q3 = sess.query(Book).filter(Book.title=='C') >> q4 = sess.query(Book).filter(Book.title=='D') >> q5 = q1.intersect_all(q2,q3,q4) >> >> SELECT anon_1.book_bookid AS anon_1_book_bookid, anon_1.book_title AS >> anon_1_book_title, anon_1.book_authorid AS anon_1_book_authorid >> FROM (SELECT book.bookid AS book_bookid, book.title AS book_title, >> book.authorid AS book_authorid >> FROM book >> WHERE book.title = ? INTERSECT ALL SELECT book.bookid AS book_bookid, >> book.title AS book_title, book.authorid AS book_authorid >> FROM book >> WHERE book.title = ? INTERSECT ALL SELECT book.bookid AS book_bookid, >> book.title AS book_title, book.authorid AS book_authorid >> FROM book >> WHERE book.title = ? INTERSECT ALL SELECT book.bookid AS book_bookid, >> book.title AS book_title, book.authorid AS book_authorid >> FROM book >> WHERE book.title = ?) AS anon_1 >> >> Chaining filters generates this SQL: >> q7 = sess.query(Book).filter(Book.title=='A') >> q7 = q7.filter(Book.title=='B') >> q7 = q7.filter(Book.title=='C') >> q7 = q7.filter(Book.title=='D') >> >> SELECT book.bookid AS book_bookid, book.title AS book_title, >> book.authorid >> AS book_authorid >> FROM book >> WHERE book.title = ? AND book.title = ? AND book.title = ? AND >> book.title = >> ? >> >> -- >> Mike Conley > > -- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To post to this group, send email to sqlalchemy@googlegroups.com. > To unsubscribe from this group, send email to > sqlalchemy+unsubscr...@googlegroups.com. > For more options, visit this group at > http://groups.google.com/group/sqlalchemy?hl=en. > > -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
