Question: 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 :)

```sql
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).

Answer: 
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:
[![Original](https://i.stack.imgur.com/eaktO.png)](https://i.stack.imgur.com/eaktO.png)
modified:
[![Modified](https://i.stack.imgur.com/z1pJw.png)](https://i.stack.imgur.com/z1pJw.png)

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)
You Can Try This:

```
SELECT TABLE_B.S_ID,
       a.C_ID
       FROM ( SELECT TABLE_A.id as id,
                    TABLE_A.S_ID as S_ID,
                    TABLE_A.C_ID as C_ID
                   FROM TABLE_A
where C_ID IN ( SELECT MAX(TABLE_A.C_ID)
                                    FROM TABLE_A 
                           GROUP BY TABLE_A.S_ID ) OR C_ID IS NULL) as a 
RIGHT OUTER JOIN TABLE_B ON TABLE_B.S_ID =
                            a.S_ID AND a.C_ID = TABLE_B.C_ID

```