I know the thread How can I inner join two csv files in R which has a merge option, which I do not want. I have two data CSV files. I am thinking how to query like them like SQL with R. Two CSV files where primary key is data_id. data.csv where OK to have IDs not found in log.csv (etc 4)data_id, event_value1, 7771, 6662, 1114, 123 3, 3241, 245log.csv where no duplicates in ID column but duplicates can be in namedata_id, name1, leo2, leopold3, loremPseudocode by partial PostgreSQL syntaxLet data_id=1Show name and event_value from data.csv and log.csv, respectivelyPseudocode like partial PostgreSQL select SELECT name, event_value     FROM data, log    WHERE data_id=1;Expected outputleo, 777leo, 666 leo, 245R approachfile1 <- read.table(file1.csv, col.names=c(data_id, event_value))file2 <- read.table(file2.csv, col.names=c(data_id, name))# TODO here something like the SQL query # http://stackoverflow.com/a/1307824/54964Possible approaches where I think sqldf can be sufficient heresqldfdata.tabledplyrPostgreSQL Schema pseudocode to show what I am trying to do with CSV filesCREATE TABLE data (        data_id SERIAL PRIMARY KEY NOT NULL,        event_value INTEGER NOT NULL);CREATE TABLE log (        data_id SERIAL PRIMARY KEY NOT NULL,        name INTEGER NOT NULL);R: 3.3.3OS: Debian 8.7Related: PostgreSQL approach in the relevant thread How to SELECT with two CSV files/ on PostgreSQL? 
How to select on CSV files by R sqldf/data.table/dplyr?
csv;r;sql
sqldf approach.One approach which shows a caveat with join approach - you cannot use WHERE data_id on both tables if you join by data_id. Code 1file1 <- read.table(data.csv, col.names=c(data_id, event_value))file2 <- read.table(log.csv, col.names=c(data_id, name))library(sqldf)df3 <- sqldf(SELECT event_value, name   FROM file1   LEFT JOIN file2 USING(data_id))df3Output wrong because data_id = 1 should be active tooLoading required package: gsubfnLoading required package: protoLoading required package: RSQLiteLoading required package: tcltkWarning message:Quoted identifiers should have class SQL, use DBI::SQL() if the caller performs the quoting.   event_value    name1 event_value    name2         777     leo3         666     leo4         111 leopold5         123    <NA>6         324   lorem7         245     leoCode 2Code df3 <- sqldf(SELECT event_value, name   FROM file1   LEFT JOIN file2 USING(data_id)   WHERE data_id = 1)Output blank because join applied already[1] event_value name       <0 rows> (or 0-length row.names)Code 3Do WHERE earlierdf3 <- sqldf(SELECT event_value, name   FROM file1   WHERE data_id = 1   LEFT JOIN file2 USING(data_id))Output error because two tables are of different size so WHERE should be applied on both tablesError in rsqlite_send_query(conn@ptr, statement) :   near LEFT: syntax errorCalls: sqldf ... initialize -> initialize -> rsqlite_send_query -> .CallIn addition: Warning message:Quoted identifiers should have class SQL, use DBI::SQL() if the caller performs the quoting. Execution haltedCode 4Using two SELECTs with JOINdf3 <- sqldf(SELECT event_value, name   FROM file1   WHERE data_id = 1   LEFT JOIN        (SELECT data_id, name       FROM file2       WHERE data_id = 1)   USING(data_id))Output errorError in rsqlite_send_query(conn@ptr, statement) :   near LEFT: syntax errorCalls: sqldf ... initialize -> initialize -> rsqlite_send_query -> .CallIn addition: Warning message:Quoted identifiers should have class SQL, use DBI::SQL() if the caller performs the quoting. Execution haltedMaybe, a syntax error with the second SELECT and its attachment to JOIN.