CODEWITHSUNDEEP
mysqlsqljoin
wiradikusuma
wiradikusuma

Reputation: 1909

How to return NULL on LEFT JOIN when criteria produces no result for the 2nd table

I have table Account that has zero-to-many Subscription, and I want to return Account.name and Subscription.end.

If there's no active subscription, Subscription.end should be NULL.

This is my (incorrect) SQL:

SELECT acc.name,
       s.t_end
FROM ACCOUNT acc LEFT JOIN SUBSCRIPTION s ON acc.id = s.account_id
WHERE acc.id = 1
  AND s.t_start <= CURRENT_TIMESTAMP()
  AND s.t_end > CURRENT_TIMESTAMP()

The problem with my SQL: If there's no matching subscription for the account, it will return empty set:

acc.name | s.t_end
------------------
<nothing>

What I want:

acc.name | s.t_end
------------------
1        | NULL

Upvotes: 2

Views: 59

Answers (2)

Madhur Bhaiya
Madhur Bhaiya

Reputation: 28834

Whenever using Left Join, if there are any Where conditions on the right-side tables; it will limit the data, and you will not get all the rows from the left-most table as desired.

Instead, you can shift the Where conditions on the right-side tables to the join ON condition:

SELECT acc.name,
       s.t_end
FROM ACCOUNT acc 
LEFT JOIN SUBSCRIPTION s ON acc.id = s.account_id AND 
                            s.t_start <= CURRENT_TIMESTAMP() AND 
                            s.t_end > CURRENT_TIMESTAMP()
WHERE acc.id = 1

Also, it is generally a good practice to put as many conditions as possible into the JOIN ON conditions, as it increases readability of the queries. It will be much easier to change these queries as well, in case the Join type is being changed.

Upvotes: 4

Nikhil Bhosle
Nikhil Bhosle

Reputation: 13

you are applying where condition on subscription table , so the because of the where condition the left join behaves like an inner join.

Try eliminating the where condition, if you can't you can use below query-

SELECT acc.name,
       s.t_end
FROM ACCOUNT acc 
LEFT JOIN 
(select account_id,t_end from SUBSCRIPTION 
where 
     t_start <= CURRENT_TIMESTAMP()
and  t_end > CURRENT_TIMESTAMP()
) s
ON acc.id = s.account_id
WHERE acc.id = 1

Upvotes: 0

Related Questions