StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>I recommend you change your approach.</p> <p>Currently, you are trying to select all gifts sent by a user other than #3. In addition, you are using an inner join, meaning <em>only</em> gifts <em>sent</em> by a user other than #3 are included. Gifts which have never been sent by anyone are, therefore, excluded.</p> <p>Instead, you should start with all gifts and then perform an <em>outer</em> join on user #3. This will return all gift columns and user columns <em>if</em> the user has sent the gift. If the user has not sent the gift, then user columns will simply contain NULL, but the row will still be included.</p> <p>Here's an example of an outer join which will check the user columns to determine if a user has sent the gift. Only columns containin NULL are included in the final result, meaning the user has <em>not</em> sent the gift:</p> <pre><code>SELECT * FROM gifts LEFT JOIN users_gifts ON users_gifts.gift_id = gifts.id AND user_gifts.uid = 3 -- Whatever the User's ID is WHERE users_gifts.gift_ID IS NULL -- exclude the gift if it's already been sent </code></pre> <p>This is an alternative approach using an <a href="http://dev.mysql.com/doc/refman/5.0/en/exists-and-not-exists-subqueries.html" rel="nofollow noreferrer">EXISTS</a> subquery:</p> <pre><code>SELECT * FROM gifts WHERE NOT Exists( SELECT 0 FROM users_gifts WHERE users_gifts.gift_id = gifts.id AND user_gifts.uid = 3 -- Whatever the User's ID is ) </code></pre> <p>Which method is faster, or whether one method is faster than the other, depends on your specific circumstances. According to <a href="http://explainextended.com/2009/09/18/not-in-vs-not-exists-vs-left-join-is-null-mysql/" rel="nofollow noreferrer">this article</a>, the <code>NOT EXISTS</code> approach is 30% slower, but <a href="http://planet.mysql.com/entry/?id=24888" rel="nofollow noreferrer">this article</a> suggests that <code>NOT EXISTS</code> is more performant than <code>LEFT JOIN</code> if the column contains NULLs. As <a href="https://stackoverflow.com/questions/19369029/select-all-and-excluding-results-sql-join/19369135#comment28702978_19369097">Darius mentioned</a> in the comments on his answer, "testing is the way to go."</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload