"SEMI JOIN" support as a "real" JOIN

First, I apologize if I'm not submitting this to the right section. It's not a bug, but I don't see "Feature Request." I'll try to keep this, um, short, not epic.

OVERVIEW –
Whether correct or not, the following occurs. You have 2 tables in a typical 1:M relationship, but you only want 1 row back. OK, use a semi-join, right?

Well, first, up until now the best you could do is use an index-subquery, which was a very limited approach for an exact type of query. Now we have options. But before that in the stone age, what would lots of people do?

They'd do a LEFT or INNER JOIN and add on a 'hacky' GROUP BY at the end. In fact that's one of your execution approaches for semi-joins. But it's not always the best one. It depends.

But ...

The nice part about this awful hack is that you get the columns from the table you're 'semi-joining' to. Sometimes you don't care which row the extra columns are from, and you just need to ensure you get 1 row. Also, in the case that the planner knows it can use an INNER JOIN strategy and rewrite the query for you, it would be 100% valid and loss-less. In other cases, you might just know that what you want is the same across the many rows it would be encountering anyway (yes, I know that's denormalized, but real life isn't always normal). Lots of people summarizing data end up with schemas like this.

I know this would not pass muster with the SQL normalization overlords, but it would be darn useful and very much in the spirit of MySQL.

Thoughts?