How to select from one table and a sub table

Question

I have something like the following two tables in SQL Server:

Table1
MainID Descrip
     1 tree
     2 dog
     3 blah
     4 etc

Table2
SubID MainID SubDescrip
    1      2 rover
    2      2 fido

I want to be able to select all from Table1 and Table2 when it has a matching MainID.

My desired output is as follows:

MainID Descrip SubID SubDescrip
     1 tree     NULL       NULL
     2 dog      NULL       NULL
     2 dog         1      rover
     2 dog         2       fido
     3 blah     NULL       NULL
     4 etc      NULL       NULL

I can get all but the second row of the desired output with an outer join, but how do I get that second row as well?

In other words, this:

SELECT m.MainID, m.Descrip, s.SubID, s.SubDescrip
FROM Table1 m LEFT OUTER JOIN
     Table2 s ON m.MainID = s.MainID
ORDER BY m.MainID, s.SubID

gives me this:

MainID Descrip SubID SubDescrip
     1 tree     NULL       NULL
     2 dog         1      rover
     2 dog         2       fido
     3 blah     NULL       NULL
     4 etc      NULL       NULL

but I want this as well:

 2 dog      NULL       NULL

Answer 1

use UNION

SELECT  m.MainID, 
        m.Descrip, 
        s.SubID, 
        s.SubDescrip
FROM    Table1 m 
        LEFT OUTER JOIN Table2 s 
            ON m.MainID = s.MainID
UNION
SELECT  MainID, 
        Descrip, 
        NULL SubID, 
        NULL SubDescrip
FROM    Table1
ORDER   BY MainID, SubID

Answer 2

You can use something like this:

    SELECT m.MainID, m.Descrip, s.SubID, s.SubDescrip
    FROM Table1 m INNER JOIN
         Table2 s ON m.MainID = s.MainID
    UNION ALL
    SELECT m.MainID, m.Descrip, NULL, NULL
    FROM Table1 m
    ORDER BY MainID, SubID

Razvan