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.

Answer 4

Hi Alex,

The query mentioned above display join of both tables if a contain 2 record and b contain 7 records it displays 7*2 = 14 records. In your case one of the table is empty( with 0 records), it will not display any data. If still you want to display data and tables are not having any relationship, you need to check if count of both tables greater that 0. Otherwise display records from only one table which is not empty.

Answer 5

SELECT a.*, b.* FROM a LEFT JOIN b ON a.id = b.id 

in this example id is just example name for join key