MySQL query for getting default record if specific record is not found

Question

I would like to have a table able to store team specific values, if no record found, the default record will be returned.

TableName: Products

ProdID  ProdName    Team
1       AAA         <NULL>
1       AAA_T1      T1
2       BBB         <NULL>
3       CCC         <NULL>
3       CCC_T1      T1
3       CCC_T2      T2
4       DDD         <NULL>
4       DDD_T3      T3 

For the query, Team will need to pass in, all Products will be returned.

Team T1

ProdID  ProdName
1       AAA_T1
2       BBB
3       CCC_T1
4       DDD

Team T2

ProdID  ProdName
1       AAA
2       BBB
3       CCC_T2
4       DDD

Team T3

ProdID  ProdName
1       AAA
2       BBB
3       CCC
4       DDD_T3

How do I achieve this? Do I need to change the table structure?

Answer 1

With NOT EXISTS:

select distinct p.prodname
from products p
where p.team = 'T1'
or (
  p.team is null 
  and not exists (
    select 1 from products
    where prodid = p.prodid and team = 'T1'
  )
)

You may remove distinct if there are no duplicates.
See the demo.

Or with conditional aggregation:

select max(case when team = 'T1' or team is null then prodname end) prodname
from products
group by prodid

See the demo.
Results:

> | prodname |
> | :------- |
> | AAA_T1   |
> | BBB      |
> | CCC_T1   |
> | DDD      |