showing null rows using join

Question

Hi,

In mysql i m selecting from a table shouts having a foreign key to another table named "roleuser" with the matching column as user_id

Now the user_id column in the shouts table for some rows is null (not actually null but with no inserts in mysql)

How to show all the rows of the shouts table either with user_id null or not

I m executing the sql statement

SELECT s.*, r.firstname, r.lastname
FROM shouts s left join roleuser r where r.user_id = s.user_id limit 50;

which does not executes and shows

 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'where r.user_id = s.user_id limit 50' at line 2

but using inner join the sql executes which shows rows which only have user_id values in the shouts table. the nulls are not shown.

SELECT s.*, r.firstname, r.lastname
FROM shouts s inner join roleuser r where r.user_id = s.user_id limit 50;

How can i show all the rows from the shouts table and null values in the firstname and lastname columns where the user_id is null in the shouts table. If not at all possible with sql may be using stored procedures...

Thanks

Pradyut

Answer 1

You want an ON clause in your join.

SELECT s.*, r.firstname, r.lastname
FROM  roleuser r 
  LEFT JOIN shouts s 
    ON s.user_id = r.user_id 
WHERE
    r.firstname like '%byron%' -- where clause goes here..

LIMIT 50;

Answer 2

It looks like you want

SELECT s.*, r.firstname, r.lastname 
FROM shouts s left join roleuser r ON r.user_id = s.user_id limit 50;

You were using WHERE instead of ON.

Your second query worked because

SELECT s.*, r.firstname, r.lastname 
FROM shouts s inner join roleuser r where r.user_id = s.user_id limit 50; 

just happens to give the same results as

SELECT s.*, r.firstname, r.lastname 
FROM shouts s inner join roleuser r ON r.user_id = s.user_id limit 50; 

even though they mean slightly different things. For an INNER JOIN, the ON is optional and if you leave it off you get the Cartesian product of all the rows in the first table and all the rows in the second table, then your WHERE clause filters out all the mismatched rows. If you put the condition in the ON clause instead, the join evaluates to only the matched rows and you don't need to put a condition in your WHERE clause. In either case the results are identical for an inner join.

However an outer join has no equivalent WHERE clause equivalent, so its ON clause is not optional.