'SQL performance difference JOIN vs UNIONALL
I'm trying to improve a SQL query of mine, where I have to join two tables Post & PostItems. I've been able to figure out that the problem was caused due to an OR clause in my JOIN statement. My original query looks like this:
SELECT P.ID, IT.Total
FROM Post P
JOIN PostItem IT ON IT.PostID = P.ReferencePostID OR IT.PostID = P.ID
It takes this query about 6 minutes to spit out about 5k lines. If I split this query up like so:
SELECT P.ID, IT.Total
FROM Post P
JOIN PostItem IT ON P.ID = IT.PostID
UNION ALL
SELECT P.ID, IT.Total
FROM Post P
JOIN PostItem IT ON P.ReferencePostID = IT.PostID
It'll spit out the exact same result, but it'll take less than a second. Any idea how this occurs?
For some more context, ID is the primary key of the Post table. PostID is a foreign key to ID. ReferencePostID has no constraint whatsoever.
Thanks in advance!
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
