Query not returning rows in a table that don't have corresponding values in another [associative] table

Question

I have

Table: ARTICLES

ID | CONTENT
---------------
1  | the quick
2  | brown fox
3  | jumps over
4  | the lazy

Table: WRITERS

ID | NAME
----------
1  | paul
2  | mike
3  | andy

Table: ARTICLES_TO_WRITERS

ARTICLE_ID | WRITER_ID
-----------------------
1          | 1
2          | 2
3          | 3

To summarize, article 4 has no writer.

So when I do a "search" for articles with the word "the":

SELECT a.id, a.content, w.name 
FROM articles a, writers w, articles_to_writers atw 
WHERE a.id=atw.article_id AND w.id=atw.writer_id AND content LIKE '%the%'

article 4 does not show up in the result:

ID | CONTENT   | NAME
-----------------------
1  | the quick | paul

How do I make article 4 still appear in the results even though it has no writers?

Answer 1

Try the following query:

SELECT a.id, a.content, w.name 
FROM articles a LEFT JOIN articles_to_writers atw ON a.id = atw.article_id  
  LEFT JOIN writers w ON w.id=atw.writer_id
WHERE a.content LIKE '%the%'

Your original query used an older SQL syntax creating implicit INNER joins between all tables. INNER joins require a matching record in each table joined.

In the revised query, above, the tables are "attached" using an LEFT OUTER joins. This will allow records on the left side of the join to appear in the final result set even if there's no matching row in writer. The columns from writer will have NULL values for these "extra" rows.

Answer 2

SELECT A.ID, A.CONTENT, W.NAME
FROM 
    ARTICLES A
    LEFT JOIN ARTICLES_TO_WRITERS ATW ON ATW.ARTICLE_ID = A.ID 
    LEFT JOIN WRITERS W ON W.ID = ATW.WRITER_ID
WHERE A.CONTENT LIKE '%the%'

Answer 3

In ANSI format:

SELECT a.id, a.content, w.name
FROM articles a
LEFT JOIN articles_to_writers atw on a.id=atw.article_id
LEFT JOIN writers w on w.id=atw.writer_id
WHERE
acontent LIKE '%the%'

This is a left join, which means that all the articles will be displayed, even if there is no writer.