MariaDB 10.2

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE state 
      ( state_name VARCHAR(20), 
        population INTEGER
      );
INSERT INTO state VALUES ('Montana', 2000000);
CREATE TABLE person 
      ( person_name VARCHAR(50),
        person_state VARCHAR(20) DEFAULT NULL,        -- yes! 
        salary INTEGER
      );
INSERT INTO person 
VALUES 
  ('Alex', NULL, 12000),
  ('Bill', 'New York', 145000),
  ('Cath', 'Mississippi', 5000),
  ('Dean', NULL, 1000) ;
    SELECT p.person_name,
           COALESCE(p.person_state, s.state_name) AS person_state,
           p.salary
    FROM person AS p
      CROSS JOIN state AS s ;


person_name
person_state
salary


Alex
Montana
12000

Bill
New York
145000

Cath
Mississippi
5000

Dean
Montana
1000


DELETE FROM state;
INSERT INTO state VALUES ('Alabama', 50000);
    SELECT p.person_name,
           COALESCE(p.person_state, s.state_name) AS person_state,
           p.salary
    FROM person AS p
      CROSS JOIN state AS s ;


person_name
person_state
salary


Alex
Alabama
12000

Bill
New York
145000

Cath
Mississippi
5000

Dean
Alabama
1000

person_name	person_state	salary
Alex	Montana	12000
Bill	New York	145000
Cath	Mississippi	5000
Dean	Montana	1000

person_name	person_state	salary
Alex	Alabama	12000
Bill	New York	145000
Cath	Mississippi	5000
Dean	Alabama	1000