sql join returns fields with null value

Question

I have the following query:

SELECT *
FROM tableA.A 
LEFT JOIN tableB AS B ON B.id=A.id 
LEFT JOIN tableC AS C ON C.id=A.id2 
LEFT JOIN tableD AS D ON D.id=A.id3 
WHERE D.id = '124' AND A.field = 1 
GROUP BY A.id ORDER BY D.sortorder

The structure above is identic with my real query and i want to mention that all tables i used in the query are valid and rows are populated with numeric and alphabetic characters.There is no NULL value anywhere.

The problem is that, after i execute this query, it returns some fields with NULL values even though they are not null.

I tried to explain as good as i could,but it's a strange behaviour and i couldn't find anything on google.

If it's not a common issue and it's hard to find the mistake, maybe some suggestions would help me find the bug.

Thank you in advance

UPDATE I want to apologize.The problem was caused by an enter at the end on table A, that's why it returned NULL because there was actually no match.Thank you for your help

Never use SELECT * with GROUP BY. That suggests that you don't know what you are doing. — Gordon Linoff
– Gordon Linoff, Commented Nov 29, 2016 at 14:41
@GordonLinoff , in my code i use some specific fields , some of them with the same name , that was the purpose of group by — Peter Cos
– Peter Cos, Commented Nov 29, 2016 at 14:52
@PeterCos - it's pretty confusing to read like that, because as written, your query would throw an error. It would be easier for us if you put in a few of the column names and how you grouped them — kjmerf
– kjmerf, Commented Nov 29, 2016 at 14:54
Otherwise there is nothing wrong with your query as written. There shouldn't be nulls if there are matching ids in table B, C and D. The only reason there would be nulls is in the case where the table A ids do not find matches in tables B, C or D. I suggest looking at a specific A.id that is returning nulls from one of the other tables and go in and verify that there is in fact a matching id there — kjmerf
– kjmerf, Commented Nov 29, 2016 at 14:58

sagi · Accepted Answer · 2016-11-29 14:29:52Z

1

Try this query:

SELECT *
FROM tableA.A 
LEFT JOIN tableB AS B ON B.id=A.id 
LEFT JOIN tableC AS C ON C.id=A.id2 
LEFT JOIN tableD AS D ON D.id=A.id3 and D.id = '124'
WHERE A.field = 1 
GROUP BY A.id
ORDER BY COALESCE(D.sortorder,0)

Conditions on the right table of a LEFT JOIN should be placed inside the ON clause , not the WHERE clause.

If that doesn't work either, then I thing you misunderstood the LEFT JOIN purpose . It is used to keep all the records from the master table(A in your case) and discard all the data that doesn't match from the detail table, so , there will be NULL value when no match fouhd .

answered Nov 29, 2016 at 14:29

sagi

40.6k7 gold badges66 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Peter Cos Over a year ago

the right table condition is not working if i'm changing it like that.

sagi Over a year ago

When you put conditions on the WHERE instead of the ON , the joins turns into an INNER JOIN . You can wrap the query with another select and then filter it .

Peter Cos Over a year ago

Yes i know,but on the D table i got fields that match some of the A table by the id, and i'm using another field to extract some of those matched fields, that was the purpose of D.id=124 . But it won't return what i need because some of the returned fields have null when they shouldn't.In the D table those values have the value 124

sagi Over a year ago

Then remove it from the ON , wrap the query -> SELECT * FROM (QUERY ) s WHERE s.d_id = '124'

Collectives™ on Stack Overflow

sql join returns fields with null value

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related