$20
Why would this LEFT JOIN not get records lacking an author?

I am not sure how much background info you might need to answer this, but I'm baffled by this one. We need records regardless of whether there is an author. We use a LEFT JOIN. But we only get records that have an author. The records without an author do not get retrieved. We are using a database view, and then doing selects against the view, but there is nothing in our WHERE clause about authors, certainly nothing to screen them out. Any suggestions about where I should look for the problem?

 CREATE VIEW author_content_issue_blogs AS

 SELECT

  c.id, c.textid, c.title, c.title_link, c.content_date, c.issue_date,

  c.protected AS protect, c.allow_comments, c.show_comments, c.content_hint,

  c.author1, c.author2, c.author3, c.author4, c.author_str,

  c.subtitle, c.abstract AS abstract, c.direct_url,

  c.imgpath1, c.imgpath2, c.imgpath3, c.imgpath4, c.imgpath5,

  c.imgcaption1, c.imgcaption2, c.imgcaption3, c.imgcaption4, c.imgcaption5,

  c.content_type, c.live_date, c.issue_toc_id,

  c.reference_link_1, c.alt_info_1,

  c.toc_title, c.toc_subtitle,

  c.promo_text_1, c.promo_text_2,

  c.parent_content_id, c.topic_ids,

  b.blog_name, b.blog_id,

  a.author_id, a.state, a.content_type AS content_t,

  it.issue_text_date,

  d.literal, d.alias, d.cdaobject, d.cdaaction, d.channel, d.adsite, d.adzone, d.indexable

  FROM content c 

  LEFT JOIN issue it on c.issue_date = it.issue_date 

  LEFT JOIN blogs b on c.reference_link_1 = b.blog_id

  LEFT JOIN content_type d on c.content_type=d.id

  LEFT JOIN author_xref a on (c.id = a.content_id  and c.state = a.state)

UPDATE:

By the way, the actual SQL that Symfony/Propel sends to the server looks like this:

SELECT author_content_issue_blogs.ID, author_content_issue_blogs.TEXTID, author_content_issue_blogs.TITLE, author_content_issue_blogs.TITLE_LINK, author_content_issue_blogs.CONTENT_DATE, author_content_issue_blogs.ISSUE_DATE, author_content_issue_blogs.PROTECT, author_content_issue_blogs.ALLOW_COMMENTS, author_content_issue_blogs.SHOW_COMMENTS, author_content_issue_blogs.CONTENT_HINT, author_content_issue_blogs.AUTHOR1, author_content_issue_blogs.AUTHOR2, author_content_issue_blogs.AUTHOR3, author_content_issue_blogs.AUTHOR4, author_content_issue_blogs.AUTHOR_STR, author_content_issue_blogs.SUBTITLE, author_content_issue_blogs.CAO_ABSTRACT, author_content_issue_blogs.DIRECT_URL, author_content_issue_blogs.IMGPATH1, author_content_issue_blogs.IMGPATH2, author_content_issue_blogs.IMGPATH3, author_content_issue_blogs.IMGPATH4, author_content_issue_blogs.IMGPATH5, author_content_issue_blogs.IMGCAPTION1, author_content_issue_blogs.IMGCAPTION2, author_content_issue_blogs.IMGCAPTION3, author_content_issue_blogs.IMGCAPTION4, author_content_issue_blogs.IMGCAPTION5, author_content_issue_blogs.CONTENT_TYPE, author_content_issue_blogs.LIVE_DATE, author_content_issue_blogs.ISSUE_TOC_ID, author_content_issue_blogs.REFERENCE_LINK_1, author_content_issue_blogs.ALT_INFO_1, author_content_issue_blogs.TOC_TITLE, author_content_issue_blogs.TOC_SUBTITLE, author_content_issue_blogs.PROMO_TEXT_1, author_content_issue_blogs.PROMO_TEXT_2, author_content_issue_blogs.PARENT_CONTENT_ID, author_content_issue_blogs.TOPIC_IDS, author_content_issue_blogs.BLOG_NAME, author_content_issue_blogs.BLOG_ID, author_content_issue_blogs.AUTHOR_ID, author_content_issue_blogs.STATE, author_content_issue_blogs.CONTENT_T, author_content_issue_blogs.ISSUE_TEXT_DATE, author_content_issue_blogs.LITERAL, author_content_issue_blogs.ALIAS, author_content_issue_blogs.CDAOBJECT, author_content_issue_blogs.CDAACTION, author_content_issue_blogs.CHANNEL, author_content_issue_blogs.ADSITE, author_content_issue_blogs.ADZONE, author_content_issue_blogs.INDEXABLE FROM author_content_issue_blogs WHERE author_content_issue_blogs.CONTENT_T=34 AND author_content_issue_blogs.STATE='W' ORDER BY author_content_issue_blogs.CONTENT_DATE DESC LIMIT 12

The problem arises when AUTHOR1 and AUTHOR_STR are empty.

UPDATE:

I increased the prize to $20. I initially thought this would be an easy question, but perhaps it is more complicated than I thought.

Lawrence Krubner | 07/20/10 at 7:04pm | Edit

joins, left join

(3) Possible Answers Submitted...

Last edited:
07/21/10
12:15pm
Jarret Minkler says:
you want LEFT OUTER JOIN to get records that are null in the right table

and you are right, after you left outer join, you cannot filter with the right table fields, or the join reverts to a left join. (since the criteria filters out the nulls)

Previous versions of this answer: 07/20/10 at 7:26pm
- 07/20/10 7:23pm
  
  Jarret Minkler says:
  http://en.wikipedia.org/wiki/Join_%28SQL%29#Outer_joins
- 07/21/10 10:06am
  
  Lawrence Krubner says:
  Jarret, you are maybe thinking of Oracle. MySql does not support LEFT OUTER JOINs. Or rather, all LEFT JOINs in MySql are basically LEFT OUTER JOINs. MySql only supports the LEFT OUTER JOIN syntax so as to support ODBC compliance. Whether you use "LEFT JOIN" or "LEFT OUTER JOIN", in MySql, you will get exactly the same results.
- 07/21/10 10:10am
  
  Lawrence Krubner says:
  Jarret, for instance, look here:
  
  The { OJ ... LEFT OUTER JOIN ...} syntax shown in the join syntax description exists only for compatibility with ODBC.
- 07/21/10 10:13am
  
  Jarret Minkler says:
  Lawrence,
  
  You are thinking of INNER join.
  
  http://dev.mysql.com/doc/refman/4.1/en/join.html
  
  Outer join works just fine in mysql ..
- 07/21/10 10:14am
  
  Jarret Minkler says:
  The { OJ ... LEFT OUTER JOIN ...} syntax shown in the join syntax description exists only for compatibility with ODBC. This is in reference to OJ not OUTER JOIN
- 07/21/10 10:16am
  
  Jarret Minkler says:
  table_references:
  table_reference, table_reference
  | table_reference [INNER | CROSS] JOIN table_reference [join_condition]
  | table_reference STRAIGHT_JOIN table_reference
  
  -->>> | table_reference {LEFT|RIGHT} [OUTER] JOIN table_reference join_condition
  
  | table_reference NATURAL [{LEFT|RIGHT} [OUTER]] JOIN table_reference
  | { OJ table_reference LEFT OUTER JOIN table_reference
  ON conditional_expr }
- 07/21/10 10:17am
  
  Jarret Minkler says:
  Steven, no it won't he doesn't have any where criteria on the admin table
- 07/21/10 10:18am
  
  Jarret Minkler says:
  author table rather
- 07/21/10 10:21am
  
  Jarret Minkler says:
  SELECT *
  FROM tags t
  LEFT OUTER JOIN tweets w ON t.id = w.id
  LIMIT 0 , 30
  
  id tag id name long_url short_url text
  1 php NULL NULL NULL NULL NULL
  2 coupons NULL NULL NULL NULL NULL
  3 eve 3 Simple PHP bit.ly bit.ly Learn PHP
  4 eveonline 4 MyDigitalDeals.com - Shop Cheap mydigitaldeals.com mydigitaldeals.com MyDigitalDeals.com - Shop Cheap
  
  From my phpMyAdmin ...
- 07/21/10 10:24am
  
  Lawrence Krubner says:
  Jarret, you misunderstand me. I didn't say OUTER JOIN did not work, I said all LEFT JOINs in MySql are LEFT OUTER JOINs. Look at the material that you quote:
  
  | table_reference {LEFT|RIGHT} [OUTER] JOIN table_reference join_condition
  
  The square brackets around "OUTER" mean that its optional. You do not need it. "LEFT JOIN" gives you the same thing as "LEFT OUTER JOIN".
- 07/21/10 10:26am
  
  Lawrence Krubner says:
  Jarret, you write:
```
SELECT *

FROM tags t

LEFT OUTER JOIN tweets w ON t.id = w.id

LIMIT 0 , 30
```
  now if you write:
```
SELECT *

FROM tags t

LEFT JOIN tweets w ON t.id = w.id

LIMIT 0 , 30
```
  what do you get?
- 07/21/10 10:26am
  
  Jarret Minkler says:
  No LEFT JOIN gives you the same as left INNER join
  
  Yes, OUTER is an option - see my example
- 07/21/10 10:29am
  
  Jarret Minkler says:
  At any rate, if your not getting back your null rows it means there are no corresponding rows in the content table, correct?
- 07/21/10 10:34am
  
  Jarret Minkler says:
  Yes leaving the LEFT there is the same. In most cases though you would just use JOIN, as not to bloat the query for performance.
  
  Your case is different.
  
  If you are not getting the null rows back then the previous joins have removed them. Is there a reason for all joins being LEFT JOINS?
- 07/21/10 10:35am
  
  Lawrence Krubner says:
  Jarret, where do you see text that says a LEFT JOIN is the same as a LEFT INNER JOIN? I am following the URLs that you've put up but I can not find the text you seem to be thinking of. Can you quote it here?
- 07/21/10 10:38am
  
  Jarret Minkler says:
  and c.state = a.state This is the criteria that is killing it, remove this and see if you get the nulls
- 07/21/10 10:39am
  
  Lawrence Krubner says:
  Jarret, you write:
  
  If you are not getting the null rows back then the previous joins have removed them.
  
  That is an interesting idea. I didn't even realize that was possible, especially with LEFT JOINs.
  
  There are rows in the database with articles that have no authors. We want them to come back in the return set. But we seem unable to get them.
  
  We are trying to get all the data related to articles that are related to some category.
  
  The reason all the JOINs are LEFT JOINs is that we want all the info, regardless of whether there is a match. There will be a match for some articles but not others.
  
  You can see my comments to Steven to get my opinion about the wisdom here.
- 07/21/10 10:41am
  
  Jarret Minkler says:
  I meant to say, JOIN is the same is LEFT INNER .. getting confused here :)
  
  LEFT JOIN = LEFT OUTER JOIN
  JOIN = LEFT INNER JOIN
  
  I never ever use "left" except maybe to do a FULL JOIN replication in sql ..
- 07/21/10 10:43am
  
  Jarret Minkler says:
  You do have criteria on the author table :)
  
  ^^ and c.state = a.state This is the criteria that is killing it, remove this and see if you get the nulls
  
  This criteria is killing the outer left join ..
- 07/21/10 10:58am
  
  Lawrence Krubner says:
  You write:
  
  LEFT JOIN = LEFT OUTER JOIN
  JOIN = LEFT INNER JOIN
  
  Good. Now we are in agreement.
- 07/21/10 10:59am
  
  Lawrence Krubner says:
  c.state = a.state
  
  jarret, thanks, I will try this.
- 07/21/10 11:04am
  
  Lawrence Krubner says:
  Jarret, how might I re-write this line?
```
 LEFT JOIN author_xref a on (c.id = a.content_id  and c.state = a.state)
```
  When an article is a draft, c.state equals "D". For an article to be published, then c.state should equal "P". If I remove this:
  
  c.state = a.state
  
  Then I worry the LEFT JOINs will return some data that has not yet been published.
- 07/21/10 11:20am
  
  Jarret Minkler says:
  As long as you only have criteria in the content table you are fine.
  
  Where c.state = 'P'
  
  You could also do a having
  
  Having c.state = 'P'
- 07/21/10 11:52am
  
  Lawrence Krubner says:
  Jarret,
  
  I recreated the view. I re-wrote this line:
```
  LEFT JOIN author_xref a on (c.id = a.content_id  and c.state = a.state)
```
  as:
```
  LEFT JOIN author_xref a on (c.id = a.content_id )
```
  But the problem remains.
  
  The problem comes up mostly when I go to a page that should have a particular article. The URL is sort of like this:
  
  http://www.mydomain.com/article?id=34
  
  If that article has no author, it does not appear. But if it has an author, it does appear.
- 07/21/10 12:15pm
  
  Lawrence Krubner says:
  No, sorry, you were right. That line was causing trouble.
Last edited:
07/20/10
10:18pm
Steven Siebert says:
@Jarret - True, a LEFT OUTER JOIN will give you the null - but only the ones that are null. I don't think this is quite what the op is looking for.

Lawrence, this is quite a complicated query. Are you sure you want to do all this in one query? I think, perhaps, for both overall performance (joins cost a lot, especially when indexes are either not set appropriately or not used) as well as the dba/programmers behind you, you might want to consider doing two queries.

The first query would be to retrieve the content information (I believe an inner join between content/blog and content/content_type should be fine). The second query will take the author ID's that are returned and retrieve the author information for them. You can get the list of author ID's you need by either iterating over the results from the first query in application code and then doing another query (probably the easiest) or using a MySQL stored procedure to contain everything on the db server side (still tricky).

If you still wish to go the view-with-complex-joins route, let me know and I'll help troubleshoot the issue =)...but I do expect this query to take a long time and possibly even return duplicate values (do to the need of cartesian joins) that would need to be filtered out in application code or similar.

Regards,

Steve
- 07/21/10 10:07am
  
  Lawrence Krubner says:
  Steven, on some level I think queries like this are a crime, but we do not currently have wide options. We are working with an old system. At some point I'd like to redo the entire database schema, but at the current moment we lack the resources and time to do a full rewrite.
- 07/21/10 10:22am
  
  Lawrence Krubner says:
  The other problem with the current schema is the high level of denormalization. There are hard-coded author1, author2, author3, and author4 fields, but then there is also an author_xref table. Ideally, there would only be a author_xref table, but I have to try to work with the database as it is.
- 07/21/10 10:29am
  
  Lawrence Krubner says:
  Steven, you write:
  
  but I do expect this query to take a long time and possibly even return duplicate values (do to the need of cartesian joins)
  
  There is a Cartesian product in the current results. I've pointed this out but in this context, I think the feeling is that redundancy is not terrible. I think they are content to filter this in the PHP code. The idea here is to get all articles related to a category. A lack of precision is tolerable.
- 07/21/10 10:42am
  
  Lawrence Krubner says:
  At some point we will be rebuilding this system, and re-doing the schema so it is a bit more normal. For now, the LEFT JOINs are there to deal with awkwardness of dealing with such a disorganized and denormalized system. I am told that the system has built up over 10 years and the needs that it serves have changed many times.
Last edited:
07/20/10
11:14pm
Bill Hunt says:
What version of MySQL, out of curiosity? There were lots of view & join related bugs resolved around 5.0.5 as I recall, just want to make sure it's not one of those...
- 07/21/10 10:08am
  
  Lawrence Krubner says:
  Bill, version 5.1.37.
- 07/21/10 10:42am
  
  Bill Hunt says:
  Hmm. still could be a few different bugs. Can you run an EXPLAIN on the query and see what it says?
- 07/21/10 11:09am
  
  Lawrence Krubner says:
  Bill, I'm uploading a screenshot of what EXPLAIN shows me on one query.
  
  Attached Image
- 07/21/10 11:10am
  
  Lawrence Krubner says:
  Bill, uploading another screenshot of EXPLAIN output. This is of the query I posted above.
  
  Attached Image