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?
1
5 Answers
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.
4 Comments
Imre L
FYI, "In MySQL, CROSS JOIN is a syntactic equivalent to INNER JOIN (they can replace each other)"
Andomar
@Imre L: Interesting. It's probably still clearer to use
cross join by convention if there is no on clause, even if MySQL allows inner join for the same purpose!Imre L
Yes. I personally prefer to omit words
INNER and CROSS, etc. But i dont like using inline CROSS JOIN when there is some conditions used on joined table.
Zvi
does not work if I use a WHERE clause on table A that returns 0 rows (the table is not empty). I tried also switching between tables A and B, but still no records are returned
SELECT * FROM a LEFT JOIN b ON a.ID = b.ID
Will return everything from a even if b is empty.
1 Comment
strattonn
You've assumed there are fields to join with, there may be no common field. Without common fields this can't work. See the ON 1=1 in the answer.
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.
2 Comments
strattonn
You've assumed there are fields to join with, there may be no common field. Without common fields this can't work. See the ON 1=1 in the answer. That's the key to the whole question, joining empty sets without common fields.
Bruno Costa
Yes, you're right, in that case a cross join is required or the condition 1=1 which is almost the same.
SELECT a.*, b.* FROM a LEFT JOIN b ON a.id = b.id
in this example id is just example name for join key
1 Comment
strattonn
You've assumed there are fields to join with, there may be no common field. Without common fields this can't work. See the ON 1=1 in the answer.
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.
INNER JOINwhich results with x*y records, so if 1 of them has 0 rows then x*0 = 0.