R: I need to understand the mechanism behind reading a subset of a file in R (e.g., via sqldf) or other data-science-centered programming language
There are multiple aspects to performance when it comes to reading a portion of the file. I am going to discuss the two most common one based on functions that are available in the base R. 

What is R doing at the time of reading?  


Establish a connection with the file.
Open the file
Read the data sequentially
Load the data in memory
Close the connection
dump the object into a variable if assigned, else render the output in some gui.


While it is doing all these steps, it is also trying to:  


Understand how big the data is so that it can allocate appropriate memory
What are the data types or classes so that it can (again) allocate appropriate memory.


Sub bullet 2 is important here because unless R reads the entire dataset, it cannot determine the datatype. Providing this information alone at the time of the read makes processing much faster.

By reading a subset of the data, R makes it easier to interpret the datatype and then assigns it to the subsequent sets without worrying about what the actual datatype is.

Does R really loads all the data every time?  

Functions that allow filter at the time of read, really target the third step of reading data sequentially.

Each row is compared against the filter criteria and the result stored as a binary logical index. This index is then used to determine, what remains when the connection closes. I could not find specific reference in the documentation if the discard happens immediately or post entire read, but I will assume that the R collects the garbage automatically as it reads with a filter criteria and makes the memory available to a subsequent line of data.

May be there are other experts who can throw more light on the real inner workings here and either validate or correct my assumptions, but hope my context helps.