Reputation: 6707
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
Reputation: 41508
Short answer: Not possible.
Why:
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. What you're attempting to do is unnecessary and would provide no added benefit.
Upvotes: 2
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
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
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