CODEWITHSUNDEEP
mysqlsqljoin
Martin AJ
Martin AJ

Reputation: 6707

How can I make a conditional join?

Here is my tables structure:

// users
+------+----------+ 
|  id  |   name   |
+------+----------+
| 1    | Jack     |
| 2    | Peter    |
| 3    | Ali      |
| 4    | John     |
+------+----------+

posts
+----+-----------+-----------+---------------+-------------------------+
| id | author_id | editor_id |     title     |          content        |
+----+-----------+-----------+---------------+-------------------------+
| 1  | 4         | null      | title1        | content1                |
| 2  | 3         | null      | title2        | content2                |
| 3  | 1         | 3         | title3        | content3                |
| 4  | 3         | null      | title4        | content4                |
| 5  | 2         | null      | title5        | content5                |
| 6  | 2         | 1         | title6        | content6                |
+----+-----------+-----------+---------------+--------------------------+

All I'm trying to do is getting names instead of ids in the posts table. So here is my query:

SELECT p.id,
       p.title, 
       p.content, 
       u1.name AS author_name, 
       u2.name AS editor_name
FROM posts
LEFT JOIN users u1 ON p.author_id = u1.id
LEFT JOIN users u2 ON p.editor_id = u2.id
WHERE p.id = :post_id

Ok all fine. What's my question? As you see, in the most of times, editor_id contains null. So I don't need to have a join on it (when its value is null). I mean second LEFT JOIN shouldn't occur in the most of times. So I want to implement an condition like this before second join:

IF ( p.editor_id <> null ) THEN 
   LEFT JOIN users u2 ON p.editor_id = u2.id
ENDIF

How can I do that?

Upvotes: 0

Views: 440

Answers (4)

Ray
Ray

Reputation: 41508

Short answer: Not possible.

Why:

  • The very nature of a LEFT JOIN is in itself a conditional. It includes all records from the 'Left side' table and populate values that match from the right, otherwise supplies nulls when no match is found base on the join condition.
  • A conditional functionality like you describe in SQL could create a broken query. You can't just take an entire product of join out of a query, this is especially obvious if it's results are used elsewhere (like in the select parameters).

What you're attempting to do is unnecessary and would provide no added benefit.

Upvotes: 2

Gurwinder Singh
Gurwinder Singh

Reputation: 39537

If you have a very few editor_id really present, I guess you can use correlated subquery in the select to get the editor's name.

SELECT p.id,
       p.title, 
       p.content, 
       u1.name AS author_name, 
       (select name from users u where u.id = p.editor_id) as editor_name
FROM posts p
LEFT JOIN users u1 ON p.author_id = u1.id
WHERE p.id = :post_id

Upvotes: 1

spencer7593
spencer7593

Reputation: 108530

You can't do that. And there's no need to do that.

The conditional test shown in the question p.editor_id <> null is guaranteed to evaluate to NULL, so it will never be TRUE. But even if we change that to a more reasonable p.editor_id IS NOT NULL, there's still no point.

There's already an equality predicate that requires u2.id to be equal to p.editor_id.

That means that if p.editor_id is NULL, then it's impossible for the equality comparison to return TRUE.

It's not like the database engine is going to chunk through all of the rows in users to find a row that it already knows cannot exist.

So what is the point? I'm not understanding what you are attempting to achieve.

Are you attempting to affect the resultset that is returned (to get something different), or improve performance, or what?

It would be valid to change this:

 LEFT JOIN users u2 ON p.editor_id = u2.id

To this:

 LEFT JOIN users u2 ON p.editor_id = u2.id AND p.editor_id IS NOT NULL

But adding that condition doesn't change anything. It's redundant. The p.editor_id = u2.id is already checking that p.editor_id is not null; if it's null, then it's not possible for it to be equal to u2.id. (NULL is never "equal" to anything.)

Upvotes: 0

Karan Shah
Karan Shah

Reputation: 1324

I don't think you need to specify the NOT NULL condition, that's the way LEFT JOIN would work (irrespective of whether you achieve your goal or not)

Upvotes: 1

Related Questions