Hugo Kornelis digs into execution plans when retrieving data from temporal tables:
The query above will simply grab the requested data from the Products table as if it were a normal table. The corresponding history table is not used at all. To understand why this happens it is important to recall that system-versioned temporal tables always store the currently valid version of the row in the table itself; all older, no longer valid versions are stored in the accompanying history table. So the query above, which does not use any specific temporal logic, means “I don’t care about the history, I want the data as it is now”. Due to how temporal tables are designed, SQL Server only has to query the actual table for this.
Since there is nothing special or interesting about this, let’s move on to queries that do use special logic in the query to retrieve older versions of the data.
Click through for quite a bit more detail on what it looks like for less-regular queries.