CODEWITHSUNDEEP
mysqlleft-join
chx
chx

Reputation: 11770

Simplifying LEFT JOINs

So I have this query:

  SELECT c1.field1, c2.field2
  FROM table1 c1
  LEFT JOIN table1 c2 ON c1.field1 = c2.field1 AND c1.field2 = :arg1 AND c2.field2 <> :arg1
  LEFT JOIN table2 cc ON c2.field2 = cc.field2 AND cc.field3 = :arg2
  WHERE (c2.field2 IS NULL OR cc.field2 IS NOT NULL)
  ORDER BY ISNULL(c2.field2) ASC

This does two things:

  1. Picks up table1.field1 for a given field2 value.
  2. Picks up a table1.field2 for which field1 is the same as in the previous point that also has a specific propery from a second table. (It's only this complicated because of normalization) If this doesn't exist then return NULL.

While this works it's really ugly. Is there a simpler version doing the same? I am mostly bothered how the second JOIN needs to be a LEFT followed by this WHERE. I felt INNER should be simpler but it doesn't work.

Upvotes: 0

Views: 146

Answers (1)

Jaugar Chang
Jaugar Chang

Reputation: 3196

Try to transfer left join into union and inner join, then you can get a new query like this:

SELECT c1.field1, null as field2
FROM table1 c1
  UNION ALL
SELECT c1.field1, c2.field2
FROM table1 c1
INNER JOIN table1 c2 ON c1.field1 = c2.field1 
INNER JOIN table2 cc ON c2.field2 = cc.field2 
WHERE c1.field2 = :arg1 AND c2.field2 <> :arg1 AND cc.field3 = :arg2

Firstly, I transfer your original query from join into union by splitting the where clause like this:

SELECT c1.field1, c2.field2
FROM table1 c1
LEFT JOIN table1 c2 ON c1.field1 = c2.field1 AND c1.field2 = :arg1 AND c2.field2 <> :arg1
WHERE (c2.field2 IS NULL )
  UNION ALL
SELECT c1.field1, c2.field2
FROM table1 c1
INNER JOIN table1 c2 ON c1.field1 = c2.field1 AND c1.field2 = :arg1 AND c2.field2 <> :arg1
LEFT JOIN table2 cc ON c2.field2 = cc.field2 AND cc.field3 = :arg2
WHERE (c2.field2 IS NOT NULL AND cc.field2 IS NOT NULL)

But the first part of union can keep transfer like this:

SELECT c1.field1, null
FROM table1 c1
WHERE c1.field2 <> :arg1 --c2.field2 must be null
  UNION ALL
SELECT c1.field1, c2.field2
FROM table1 c1
LEFT JOIN table1 c2 ON c1.field1 = c2.field1  AND c2.field2 <> :arg1
WHERE c1.field2 = :arg1 AND c2.field2 IS NULL

At the end, I get the top query that transfer all left join into union and inner join.

I don't have any data, so I'm not sure I'm right or not. Wish it helps.

Upvotes: 1

Related Questions