Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I'm fetching the grandparent of all nodes in a tree as follows:

SELECT, P.parent, GP.parent gp_id FROM product_groups P             
INNER JOIN product_groups GP ON P.parent =

There is no entry with id=0 in the table, but some where parent=0, indicating roots of the trees. Those rows are not fetched by this query, as there's no grandparent to join them with. I'd like those rows returned, with 0 (or NULL) for both parent and gp_id.

I was planning to UNION the second table with (0,0), but can't figure out the syntax. How to do this?

share|improve this question
up vote 5 down vote accepted

Just do a LEFT JOIN instead:

SELECT, P.parent, GP.parent gp_id FROM product_groups P             
LEFT JOIN product_groups GP ON P.parent =

That will return all rows from P and any non-matching rows in GP will have NULL values.

share|improve this answer
SELECT, P.parent, 
       GP.parent, GP.gp_id 
  FROM product_groups P             
       INNER JOIN product_groups GP
          ON P.parent =
SELECT, P.parent, 
       0 AS GP.parent, 0 AS gp_id 
  FROM product_groups P
                   SELECT * 
                     FROM product_groups GP 
                    WHERE P.parent =
share|improve this answer
Of course I could union two different selects. I wanted to union the table that I join. The union of multiple complete selects would not work well in my actual case. – Tim Mar 22 '12 at 16:39
My answer satisfies the spec and is semantically the same as the answer you accepted (except mine returns zeros rather than nulls, as the spec allows). – onedaywhen Mar 23 '12 at 9:12

This will return all the lines of the two tables. You can change the aliases so that you get the column name you want.

SELECT t1.col1 as name, t1.col2, t1.col3 
FROM t1 


SELECT t2.cola as name, t2.colb, null 
FROM t2;
share|improve this answer
This doesn't answer the question. – Colin 't Hart Jan 29 at 13:47

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.