What is the correct usage of IN and ORDER BY keywords when used in conjunction with subqueries?

Question

ERD for my simple db design:

I want to order children based on their parent's last name. I am trying to use the following query:

SELECT * 
FROM children 
WHERE parent_id IN (SELECT parent_id  
                    FROM parent 
                    ORDER BY lastName ASC);

SELECT parent_id FROM parent ORDER BY lastName ASC returns the parent_ids in the order of the parent's last names (this is the intended behavior). The ordering of parent_ids according to their last names in ascending order is 154,156,155,157

However, when used as a subquery, the ordering is apparently not used correctly. The results are:

As you can see the results are ordered in ascending order of parent_id (not of the child's respective parent's last name).

Is there a way to correctly structure this query without changing my design?

Thank you.

Answer 1

The subselect returns a set, which has no order, the order by there is useless - it will not apply to the final result set. If you want to order "children" by their "parent"s' "last names", you'll have to project those "last names" to the outer select by joining the two relations:

SELECT c.* 
FROM children c
INNER JOIN parent p
ON c.parent_id = p.parent_id
ORDER BY lastName ASC