Mysql empty table join fails

Question

Background, I have a cron job that queries keywords associated with posts, if that keyword is found, a user is emailed about the post.

The cron job runs every minute so once a user has been emailed that notification should not happen again. The following query runs:

SELECT DISTINCT p.id AS post_id, u.display_name, u.id AS user_id, u.email, k.keyword, k.id AS keyword_id
FROM posts p, user_subscriptions us, keywords_users ku, keywords k, users u
LEFT JOIN keyword_subscription_sent kss ON kss.user_id = u.id
LEFT JOIN keywords_posts kp ON kss.post_id = kp.post_id
WHERE us.keywords = 1
AND ku.user_id = u.id
AND ku.keyword_id = k.id
AND kp.keyword_id = k.id
#AND kss.user_id IS NULL AND kss.post_id IS NULL
LIMIT 0, 200

The query works as expected when the table keywords_posts kp is populated.

If the table keywords_posts kp is empty the query will never return anything. I attempted to fix this by adding to the WHERE clause:

AND kss.user_id IS NULL AND kss.post_id IS NULL

But adding this to the WHERE clause will cause the query to never return anything at all at any time.

The desired result is:

The query will return all rows except listed in the keywords_posts kp table.

*Never* use commas in the `FROM` clause. *Always* use proper explicit `JOIN` syntax. — Gordon Linoff, May 05 '18 at 03:16
If keyword_posts is empty surely this part of your WHERE clause: `AND kp.keyword_id = k.id` will always fail, giving you no results? I think you probably want `kp.keyword_id IS NULL` instead. — Nick, May 05 '18 at 03:27
Hi. [mcve] please.Your "desired" description is unclear. All rows of what? Please use enough words, phrases & sentences to say what you mean. Don't try to cram just some fragments of that into one sentence. Comma binds looser than explicit joins; so the commas are being executed after the left joins. [Learn what left join on returns](https://stackoverflow.com/a/46091641/3404097): inner join on rows plus unmatched left table rows extended by nulls. Why are you even left joining?--your desired result as a function of input is unclear but it doesn't seem to involve null-extended rows. — philipxy, May 06 '18 at 07:24

spencer7593 · Answer 1 · 2018-05-05T15:06:52.733

Suggestions:

Relocate the conditions that require a column to be non-NULL from the WHERE clause up to the appropriate ON clause of the outer join.

Don't mix the old-school comma syntax (for the join operation) with the JOIN keyword syntax. Ditch the comma syntax and just use JOIN. And move join conditions to the ON clause.

Moving the condition to the ON clause of the outer join will allow NULL values to be returned.

If we do it the WHERE clause, note that the condition

WHERE ( col = some_non_null_val AND col IS NULL )

is guaranteed to never evaluate to TRUE. (The condition is checked for each individual row.) Only rows where the condition evaluate to TRUE will be returned. Likely you meant to use OR in place of AND.

But we can avoid that OR if we just relocate the condition

         col = some_non_null_val

to the ON clause of the outer join.

I hesitate to provide an example query as a demonstration. The original query is producing a semi-Cartesian product; for example what appears to be a cross join between users and user_subscriptions... the only criteria for matching to user_subscriptions is keywords=1. So I'm not understanding the specification.

OP reports that query is returning a correct result, but I'm suspicious that the query doesn't actually satisfy the intended specification. I suspect that the matching between users and user_subscriptions should include some additional criteria.

Without a more precise specification, including sample data and an example of expected output, I cannot in good conscience provide example SQL for OP use-case.

score 0 · Answer 2 · answered May 05 '18 at 04:11

0

i think kp_keyword_id=k.id from your WHERE condition cause the query to return nothing where kp is empty,try to put that in JOIN instead so it become like this

SELECT DISTINCT p.id AS post_id, u.display_name, u.id AS user_id, u.email, k.keyword, k.id AS keyword_id
FROM posts p, user_subscriptions us, keywords_users ku, keywords k, users u
LEFT JOIN keyword_subscription_sent kss ON kss.user_id = u.id
LEFT JOIN keywords_posts kp ON kss.post_id = kp.post_id AND kp.keyword_id = k.id 
WHERE us.keywords = 1
AND ku.user_id = u.id
AND ku.keyword_id = k.id
LIMIT 0, 200

answered May 05 '18 at 04:11

Jeffry Evan

317
1
7

1

I think the part about explicit JOINs is important too, because of the order in which implicit joins are evaluated – Strawberry May 05 '18 at 06:02
1

And note that LIMIT without ORDER BY is fairly meaningless – Strawberry May 05 '18 at 06:02

score 0 · Accepted Answer · answered May 07 '18 at 14:42

Hello vveryone and thanks for you input.

The following worked:

SELECT * FROM (SELECT u.id as user_id, u.email, u.display_name, k.id as keyword_id, k.keyword, p.id AS post_id, kp.post_id AS kp_post_id, kp.keyword_id AS kp_keyword_id,
ku.user_id AS ku_user_id, ku.keyword_id AS ku_keyword_id
FROM users u, keywords k, posts p, keywords_posts kp, user_subscriptions us, keywords_users ku
WHERE kp.post_id = p.id AND us.keywords = 1 AND kp.keyword_id = k.id AND ku.keyword_id = k.id AND u.id = ku.user_id
GROUP BY kp.post_id, u.id) AS ukp LEFT JOIN keyword_subscription_sent kss ON ukp.user_id = kss.user_id AND kss.post_id = ukp.post_id WHERE kss.post_id IS NULL

Mysql empty table join fails

3 Answers3