Left column join unrelated to 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); 

Note that clip_state is 0 or 1, and the data model allows you to query for one or not a plan in the plans table from two data: benefit and state , Firstly, using the benefit condition, we can filter the plans table for a maximum of two rows, one with clip_state = 0 and one = 1. Then, using state and joining the clip_states table clip_states we can reduce the result to one row (or zero) by checking:

• If state is in the clip_states table, make sure clip_state matches the two tables. If there is no match, the result is not returned.
• If state does not exist in clip_states , it only matches a row in the 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 rather strange because it does not join the relationship between the two tables. So my question is:

• Is it normal to have such a connection?
• Is there a better solution (clean and efficient)?

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

Updated 1: I updated the text above.

Updated 2: Additional Information

• If a state not in the clip_states table, then its clip_state implicitly 0. Otherwise, its clip_state is in the table.
• From the two data: benefit and state find the row in the plans table, where plans.clip_state = clip_state of what state indicates. Of course, if this does not match, the string is not returned.

Answer 1

I assume that I should use the following query as a solution. I checked the query plan and look at one step better than my question in the question. The idea is to narrow the plan first and then join the clip_states table.

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

Thanks to everyone.

Answer 2

Updated answer to the updated question:

 SELECT p.* FROM plans p WHERE p.benefit = 'A' AND NOT EXISTS ( SELECT 1 FROM clip_states cs WHERE cs.state = 'LA' AND cs.clip_state <> p.clip_state ) ORDER BY p.clip_state LIMIT 1; 

-> sqlfiddle

This could be a definition:

• Find all rows in the plans table for the given benefit .

• Eliminate rows where clip_state does not match the same column in the clip_states table for the given state .

• From the remaining lines, return the value with the smallest value in clip_state .

Also, since

clip_state is either 0 or 1

you should use the boolean data type instead of int .

Answer 3

A completely different approach:

• "Add" the required state to the clip_states table with clip_state of 0, if this state is not already specified in the table.

• Internal connection of the result set with plans on clip_state , additionally filtering on state .

Here's what it looks 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 this request is "in action": http://sqlfiddle.com/#!1/b0feb/7

It seems according to your requirements if I haven't missed anything.

Answer 4

In fact, LEFT JOIN looks weird.

• I do not see join conditions between tables plans and clip_states ;
• You do not use the columns from clip_state in your SELECT list, so you cannot get the NULL values ​​that you are targeting.

I recommend doing this as follows (also in SQL-Fiddle ):

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

• I moved the join condition from WHERE to the FROM query part and simplified it;
• I added a column from the clip_states table to the SELECT list, it can be NULL if there are no corresponding rows;
• When cs.clip_state is NULL , I turn it to 0, as expected.

I think this does what you need.

Please take a look at this Visual Explanation of Associations , I find it very useful.

Answer 5

Given that he does the trick:

• Is it normal to have such a connection?

This is not as usual. But, as I see it, JOIN just accepts the condition after ON, which can be any condition.

• Is there a better solution (clean and efficient)?

It seems like the shortest solution is available here, and I don't understand why this should degrade performance.

If you create a VIEW from this query and include the STATE field in the selected fields, you can make a query from this view as follows:

 SELECT * FROM stateview WHERE state='FL' 

By the way, by coincidence, I think the solution provided by vyegorov is exactly what I entered in the sql script to do some testing. But he thought it was wrong, so after that he deleted it.