CODEWITHSUNDEEP
sqlmysql
Alex
Alex

Reputation: 103

Select multiple tables when one table is empty in MySQL

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?

Upvotes: 10

Views: 14998

Answers (5)

Miroslav Asenov
Miroslav Asenov

Reputation: 183

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

in this example id is just example name for join key

Upvotes: 1

jaxb
jaxb

Reputation: 2077

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.

Upvotes: 0

Bruno Costa
Bruno Costa

Reputation: 2720

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.

Upvotes: 1

duraz0rz
duraz0rz

Reputation: 397

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

Will return everything from a even if b is empty.

Upvotes: 1

Andomar
Andomar

Reputation: 238076

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.

Upvotes: 25

Related Questions