MySQL how join to empty table?

Question

i'm have SQL query:

SELECT a.id, b.id
FROM a
LEFT JOIN b ON b.id = 50
WHERE a.something = 'something'

AND a table is really empty, and so it should be. But table b not empty and have valid result on b.id = 50. This query print empty set.

As a result i need:

| a.id  | b.id  |
|------ |------ |
| null  | 50    |

You should use right join, Read at https://stackoverflow.com/questions/5706437/whats-the-difference-between-inner-join-left-join-right-join-and-full-join — Alpesh Jikadra, Mar 28 '18 at 07:05
if table 'a' is empty why use a.something='something'? It will provide null result. Besides use either right join or use b left join a — Anandhu Nadesh, Mar 28 '18 at 07:14
This is a misuse of `JOIN`. `JOINs` (whether `LEFT` or not) should state (in `ON`) how the two tables are related. — Rick James, Apr 16 '18 at 01:56

score 2 · Accepted Answer · answered Mar 28 '18 at 07:04

2

Reverse the left join or do a right join:

SELECT a.id, b.id
FROM b
LEFT JOIN a ON a.something = 'something'
WHERE b.id = 50

OR

SELECT a.id, b.id
FROM a
RIGHT JOIN b ON b.id = 50
WHERE a.something = 'something'

answered Mar 28 '18 at 07:04

Roy Bogado

Your RIGHT JOIN will be converted to an INNER JOIN. – Paul Spiegel Mar 28 '18 at 07:18
Thx, this solution work, im swap not empty table to `from` and empty to left join. – Lipau3n Mar 28 '18 at 07:34

1 Answers1