The difference can be summarized into:

I want to be as big as my parent allows (double.infinity)
I want to be as big as the screen (MediaQuery).
Usually, you'll want to use double.infinity, but it's not always possible.

Some Widgets allow their children to be as big as they want to be (Column, ListView, OverflowBox...). In that situation using double.infinity creates a paradox:

The parent allows any size
The child wants the biggest size allowed by the parent
Using MediaQuery in these situations is bad though. You will rarely want to do that unless you're creating a widget similar to Scaffold.

That's where widgets such as IntrinsincHeight comes in handy. These widgets are able to solve the paradox, and therefore have a valid layout.