From a SQL perspective, Access Filters work the same way as most View-level or Report-level filters: by adding a WHERE clause.
The main difference here is that Access Filters are a mandatory WHERE clause on the primary query, and it will force itself in regardless of whether the field being filtered on is contained in the report itself.
To use a Demographic Access Filter as an example, if the Demographic field was not in the report at all, that field is now being filtered in the report, because it’s in the Access Filter:
The list that’s returned will come from the mapping that’s created at the Data Source level. That’s what you’re doing when configuring an Access Filter - you're, for example, determining which users have access to which rows.
Yellowfin constructs the query by looking at that mapping, pulling out values for your user in your session, and plugging the relevant values into the IN clause.
You can read more about configuring Access Filters in our Wiki here.