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.
