Mysql is it possible to do the following?

Question

I have a mysql query, that does the following,

SELECT * FROM categoryTable 
LEFT JOIN userMenuTable 
ON userMenuTable.categoryId = categoryTable.categoryId

this returns only results that match this condition

ON userMenuTable.categoryId = categoryTable.categoryId

I was hoping it would be possible to pull all the results and also the ones from the JOIN?

Answer 1

The result of a left outer join, as in your example, will contain all records of the "left" categoryTable, even if the join-condition does not find any matching record in the "right" userMenuTable.

On the other hand, a right outer join resembles a left outer join, except with the treatment of the tables reversed. Every row from the "right" userMenuTable will appear in the result-set at least once.

As a1ex07 suggested in another answer, it may look like you need a full outer join, which in MySQL can be emulated with a UNION between a LEFT JOIN and a RIGHT JOIN.

Answer 2

I think that perhaps what you're looking for is a full outer join. MySQL does not support FULL OUTER JOIN, but you can get the equivalent using LEFT and RIGHT JOINS:

SELECT * FROM categoryTable LEFT JOIN userMenuTable
  ON categoryTable.categoryId = userMenuTable.categoryId
UNION ALL 
SELECT * FROM categoryTable RIGHT JOIN userMenuTable
  ON categoryTable.categoryId = userMenuTable.categoryId
WHERE categoryTable.categoryId IS NULL; 

Share and enjoy.

Answer 3

I think you need FULL JOIN (mysql doesn't have that syntax, but you can achieve desired result by UNION + LEFT + RIGHT JOIN)

SELECT * FROM categoryTable 
LEFT JOIN userMenuTable 
ON userMenuTable.categoryId = categoryTable.categoryId
UNION
SELECT * FROM categoryTable 
RIGHT JOIN userMenuTable 
ON userMenuTable.categoryId = categoryTable.categoryId