Select multiple tables when one table is empty in MySQL

Question

I'm trying to do

SELECT * FROM a, b

However, it doesn't return anything if one of the tables is empty. How do I make it so it returns 'a' even if the other one is empty?

Answer 1

Using two tables in the from clause is functionally equivalent to a cross join:

select  *
from    A
cross join
        B

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. For example:

select  * 
from    A
left join  
        B
on      1=1

As the condition 1=1 is always true, this is just like a cross join except it also works for empty tables.

Answer 2

SELECT * FROM a LEFT JOIN b ON a.ID = b.ID

Will return everything from a even if b is empty.

Answer 3

You should do a left join.

Like this

SELECT *
FROM A
 LEFT JOIN B ON A.ID = B.ID

Then you receive the rows in A and the respective row in B if exists.