mysql Query to get data from two tables when not exists in either table

Question

I am trying to fetch data from 2 tables, Table A and Table B. Table A has id , name , description. Table B has name , details , sub_details.

There are total 10 records in A and 5 Records in B.

if I execute a query,

select a.id , a.name , b.details , b.sub_details 
from A a, B b 
where a.name = b.name and b.name like "%ABC%";

the result will be show the records which has ABC in b.name column and where the same is present in A table. Now the issue occurs when any of the table does not have the data, and in that case entire result is blank.

my question is , what query shall I execute if I want to get the results even if any of the table does not have the data ?

@Barmar can u please write the query – Androing Nov 11 '20 at 20:15 — Androing, Nov 11 '20 at 20:15
Can you show some sample data and the desired result? – Barmar Nov 11 '20 at 20:25 — Barmar, Nov 11 '20 at 20:25

score 1 · Answer 1 · answered Nov 11 '20 at 20:16

1

Use LEFT JOIN

SELECT a.id, b.name, b.details, b.sub_details
FROM b
LEFT JOIN a ON a.name = b.name
WHERE b.name like '%ABC%'

If there's no matching row in a this will show the row from b and a.id = NULL.

answered Nov 11 '20 at 20:16

Barmar

741,623
53
500
612

is that what he wants? I think he wants either table to have data – Garr Godfrey Nov 11 '20 at 20:20
@GarrGodfrey That's kind of difficult if he specifically wants to match a name in `b.name` – Barmar Nov 11 '20 at 20:21
i think looking for FULL JOIN, or in MySQL would need UNION – Garr Godfrey Nov 11 '20 at 20:22
That's certainly possible, and maybe GMB's answer is what they want. – Barmar Nov 11 '20 at 20:25

score 1 · Answer 2 · answered Nov 11 '20 at 20:18

I want to get the results even if any of the table does not have the data

You are describing a full join. Unfortunately, MySQL does not support that. Assuming that name is a unique key in both tables, you can use union all and aggregation:

select max(id) as id, name , max(details) as details , max(sub_details) as sub_details
from (
    select id, name, null as details, null as subdetails from a where name like '%ABC%'
    union all 
    select null, name, details, sub_details from b  where name like '%ABC%'
) t 
group by name

mysql Query to get data from two tables when not exists in either table

2 Answers2