Left join on column isn't in the relationship between two tables

Question

I have 2 tables:

CREATE TABLE plans
    (id int, benefit varchar(5), clip_state int);

INSERT INTO plans
    (id, benefit, clip_state)
VALUES
    (1, 'A', 1),
    (2, 'A', 0),
    (3, 'B', 0),
    (4, 'C', 0);

CREATE TABLE clip_states
    (state varchar(2), clip_state int);

INSERT INTO clip_states
    (state, clip_state)
VALUES
    ('LA', 1),
    ('FL', 0);

Please noted that the clip_state is either 0 or 1 and the data model allows to query for one or no plan in plans table from 2 information: benefit & state. First, using benefit condition, we can filter the plans table to 2 rows at most, one with clip_state = 0, and one = 1. Then by using state and join with the clip_states table, we can reduce the result to one row (or zero) by checking:

• If the state is in clip_states table, make sure the clip_state is matched between 2 tables. If no match, no result returned.
• If the state doesn't exist in clip_states, it only matches with the row in plans table that has clip_state = 0.

Here is my query that does the trick:

SELECT id, p.clip_state, benefit
FROM plans p
LEFT JOIN clip_states cs ON STATE IN ('LA')
WHERE benefit = 'A' AND
(p.clip_state = cs.clip_state OR (p.clip_state = 0 AND cs.clip_state IS NULL));

As you can see, the left join is quite strange because it doesn't join on the relationship between the 2 tables. So, my question is:

• Is it normal to have such kind of join?
• Is there any better solution (clean & performance)?

You can check my solution at: http://sqlfiddle.com/#!1/8912d/53

Updated 1: I have updated the text of the question above.

Updated 2: More information

1. If a state isn't in clip_states table, its clip_state implicitly equals to 0. Otherwise, its clip_state is in the table.
2. From the two given info: benefit and state, find a row in plans table where plans.clip_state = clip_state of that given state. Of course if it doesn't match, no row returned.

Answer 1

A completely different approach:

1. "Add" the required state to the clip_states table with the clip_state of 0, if that state isn't already in the table.

2. Inner join the resulting set to plans on clip_state, additionally filtering on state.

This is what it might look like:

SELECT p.*
FROM plans p
INNER JOIN (
  SELECT state, clip_state FROM clip_states
  UNION ALL
  (
  SELECT 'LA', 0
  EXCEPT
  SELECT state, 0 FROM clip_states
  )
) cs
ON p.clip_state = cs.clip_state
WHERE p.benefit = 'A'
  AND cs.state = 'LA'
;

Here's this query "in action": http://sqlfiddle.com/#!1/b0feb/7

Seems consistent with your requirements, unless I've missed something again.