Mysql empty table join fails

mysql left join
truncate table mysql w3schools
mysql truncate table example
mysql truncate multiple tables
truncate table mysql php
mysql truncate table with foreign key
mysql truncate function
mysql truncate table if exists

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.

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.

MySQL Bugs: #9614: LEFT JOIN returns empty set when you use , Bug #9614, LEFT JOIN returns empty set when you use second table in Where clause will be applyied first and it succeeds but ON fails, so it  SELECT X.a1, X.a2, Y.b1, Y.b2 FROM X LEFT JOIN Y ON (X.a1 = Y.b1) WHERE Y.b3 = 'something' The above SQL will return empty result set. You may need to modify your SQL into the following format, by bring up the problematic where clause to LEFT JOIN ON clause. SELECT X.a1, X.a2, Y.b1, Y.b2 FROM X LEFT JOIN Y ON (X.a1 = Y.b1 and Y.b3 = 'something')

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

Apply INNER JOIN only if there is a row available otherwise use NULL, I have above table with data. I an using below query to get data from the table. SELECT `d0`.`  However as session boat is still empty at this point, the Join table does not display and so the ID number cannot be found! I have already tried using Left Joins and Outer Left Joins and anything else I can think of to Join Session and Session Boat but nothing has worked so far

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 TRUNCATE TABLE By Practical Examples, If there is any FOREIGN KEY constraints from other tables which reference the table that you truncate, the TRUNCATE TABLE statement will fail. Because a  Introduction to the MySQL TRUNCATE TABLE statement. The MySQL TRUNCATE TABLE statement allows you to delete all data in a table. Logically, the TRUNCATE TABLE statement is like a DELETE statement without a WHERE clause that deletes all rows from a table, or a sequence of DROP TABLE and CREATE TABLE statements. However, the TRUNCATE TABLE statement is more efficient than the DELETE statement because it drops and recreates the table instead of deleting rows one by one. Here is the basic syntax

MySQL Tutorial - MySQL By Examples for Beginners, For detailed syntax, check MySQL manual "SQL Statement Syntax" Modify a table, e.g., ADD COLUMN and DROP COLUMN ALTER TABLE tableName ADD SELECT command can be used to query and join data from two related tables. Cannot delete or update a parent row: a foreign key constraint fails (`southwind​`. Truncation operations cannot be performed if the session holds an active table lock. TRUNCATE TABLE fails for an InnoDB table or NDB table if there are any FOREIGN KEY constraints from other tables that reference the table. Foreign key constraints between columns of the same table are permitted.

More dangerous subtleties of JOINs in SQL, Let's say I want to join the `products` table to our log of sales data (the `sales` table) in order to calculate total revenue, being careful not to drop any sales. fails, you need some selection mechanism from the right table in  5 Answers 5. Using two tables in the from clause is functionally equivalent to a cross join: This returns a row of A for every row in B. When B is empty, the result is empty too. You can fix that by using a left join. With a left join, you can return rows even if one of the tables is empty.

How to Simulate FULL OUTER JOIN in MySQL, A standard SQL FULL OUTER join is like a LEFT or RIGHT join, except that it includes all rows from both tables, matching them where possible  left join where not_null_datetime is null fails View as plain text >Description: When performing a left join with a table including a non-null datetime column, saying "where a.non_null_datetime is null" always returns the empty set. It works if the column is either not non-null or not datetime.

Comments
  • Never use commas in the FROM clause. Always use proper explicit JOIN syntax.
  • 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.
  • Move that line to the ON clause
  • Hi. minimal reproducible example 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: 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.
  • I think the part about explicit JOINs is important too, because of the order in which implicit joins are evaluated
  • And note that LIMIT without ORDER BY is fairly meaningless