MySQL: Is there a better way to do this query? I'm not sure if this is the most appropriate place for this question, but I thought I'd give it a shot.

The scenario is wanting to retrieve records from a table where a column is null, but if an exact match is found, preference that row instead.

Here's the table structure:

CREATE TABLE TABLE_A
(
  id INT,
  S_ID INT,
  C_ID INT
);


CREATE TABLE TABLE_B
(
  S_ID INT,
  C_ID INT
);

INSERT INTO TABLE_A VALUES(1, 10, NULL);
INSERT INTO TABLE_A VALUES(2, 10, 50);
INSERT INTO TABLE_A VALUES(3, 12, NULL);

INSERT INTO TABLE_B VALUES(10, 50);
INSERT INTO TABLE_B VALUES(10, 60);
INSERT INTO TABLE_B VALUES(12, 50);
INSERT INTO TABLE_B VALUES(12, 60);


When using a c_id of 50, I'd expect records 2 and 3 to come back. If I used c_id of 60, I'd expect records of 1 and 3 to come back.

At the moment, I have this query to solve the issue, but I'm positive there is a better way to do it, so I'm asking for ideas :)

SELECT ca.* 
FROM TABLE_B AS clc 
JOIN TABLE_A AS ca ON (clc.S_ID = ca.S_ID AND clc.C_ID = 50) 
WHERE (
  ca.C_ID IS NULL AND ca.S_ID NOT IN (
     SELECT S_ID FROM TABLE_A WHERE C_ID = clc.C_ID
  ) 
) OR (
  ca.C_ID = clc.C_ID
);


Unfortunately, the tables have no indexes, and no foreign keys. The table structure cannot be changed (legacy).
if add indexes:

CREATE TABLE `table_b` (
  `S_ID` int(11) DEFAULT NULL,
  `C_ID` int(11) DEFAULT NULL,
  KEY `S_ID` (`S_ID`,`C_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
CREATE TABLE `table_a` (
  `id` int(11) DEFAULT NULL,
  `S_ID` int(11) DEFAULT NULL,
  `C_ID` int(11) DEFAULT NULL,
  KEY `S_ID` (`S_ID`,`C_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci


and little modify the query:

set @v_npref = 50;

SELECT ca.id, ca.S_ID, ca.C_ID
FROM TABLE_B AS clc 
JOIN TABLE_A AS ca ON (clc.S_ID = ca.S_ID AND clc.C_ID = @v_npref )
WHERE
ca.C_ID IS NOT NULL OR NOT EXISTS (SELECT id FROM TABLE_A ca2
                                 WHERE ca2.S_ID = ca.S_ID AND ca2.C_ID = @v_npref )

group by ca.S_ID;


we can compare plan of new and original queries,
original:

modified:


this is just my variant, what better 2 FULL scan (as You can see total number of scanned rows bigger) or file sort at the final possible decide only on real data (depend from size of sorted data)