Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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.
like image 574
instcode Avatar asked Jan 29 '13 05:01

instcode


People also ask

Can two tables be join without any relation?

The answer to this question is yes, you can join two unrelated tables in SQL, and in fact, there are multiple ways to do this, particularly in the Microsoft SQL Server database. The most common way to join two unrelated tables is by using CROSS join, which produces a cartesian product of two tables.

Which join return rows that don't match?

The JOIN or INNER JOIN does not return any non-matching rows at all. It returns only the rows that match in both of the tables you join. If you want to get any unmatched rows, you shouldn't use it. The LEFT JOIN and the RIGHT JOIN get you both matched and unmatched rows.

How do I join two tables left join?

Syntax For Left Join:SELECT column names FROM table1 LEFT JOIN table2 ON table1. matching_column = table2. matching_column; Note: For example, if you have a left table with 10 rows, you are guaranteed to have at least 10 rows after applying join operation on two tables.

Which joins do not have matching values?

The outer join is needed when you wish to include rows that do not have matching values.


1 Answers

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.

like image 104
Andriy M Avatar answered Oct 17 '22 01:10

Andriy M