Hot to get unmatched rows in the end of result while doing outer join?

Question

I am trying a left outer join like this

SELECT records.some_id FROM forms 
LEFT OUTER JOIN records
ON forms.form_id = records.form_id
ORDER BY records.some_id

This will give me all the results from table:forms including some where records.form_id is not present. However, the returned result has unmatched rows in the beginning, how can I get them in last?

Current result:

NULL
NULL
5
20
100

Expected is:

5
20
100
NULL
NULL

score 3 · Answer 1 · answered Sep 02 '13 at 13:14

3

Use a case. Generally you can do

SELECT records.some_id FROM forms 
LEFT OUTER JOIN records
ON forms.form_id = records.form_id
ORDER BY case when records.some_id is not null 
              then 1 
              else 2 
         end,
         records.some_id

and specifically in MySQL you can also do

SELECT records.some_id FROM forms 
LEFT OUTER JOIN records
ON forms.form_id = records.form_id
ORDER BY records.some_id is not null,
         records.some_id

answered Sep 02 '13 at 13:14

juergen d

201,996
37
293
362

4

+1 . . . Although in MySQL, you don't need `case`. You can just do `order by records.some_id is null desc`. – Gordon Linoff Sep 02 '13 at 13:16

score 0 · Answer 2 · answered Sep 02 '13 at 13:39

0

Also putting a COALESEC might help

SELECT records.some_id FROM forms 
LEFT OUTER JOIN records
ON forms.form_id = records.form_id
ORDER BY COALESCE(records.some_id,-1) DESC

answered Sep 02 '13 at 13:39

cjava

656
1
8
22

This will simply place all NULL rows together, but won't sort rest of rows. – Mohit Verma Sep 03 '13 at 06:16
Did you try this ? because i think this will place all the null at the end and will sort rest of the numbers in descending order . – cjava Sep 03 '13 at 06:34

Hot to get unmatched rows in the end of result while doing outer join?

2 Answers2