Select multiple tables when one table is empty in MySQL

Question

I'm trying to do

SELECT * FROM a, b 

However, it does not return anything if one of the tables is empty. How to do this so that it returns "a" even if the other is empty?

Answer 1

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

 select * from A cross join B 

This returns row A for each row in B. When B is empty, the result is also empty. You can fix this using left join . With 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 

Since the condition 1=1 always true, this is similar to a cross join , except that it also works for empty tables.

Answer 2

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

Will return everything from even if b is empty.

Answer 3

You have to make a left join.

Like this

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

Then you get the lines in and the corresponding line in B if exists.

Answer 4

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

in this example, id is just the example name for the connection key

Answer 5

The above query displays the union of both tables if the record contains 2 records and b contains 7 records, which display 7 * 2 = 14 records. In your case, one of the tables is empty (with 0 entries), it does not display any data. If you still want to display data, and the tables have no relationship , you need to check whether the number of two tables is greater than 0. Otherwise, it displays records from only one table, which is not empty.