Simple example. Let’s select students with grades using left outer join: SELECT DISTINCT s.id FROM students s LEFT JOIN grades g ON g.student_id = s.id WHERE g.student_id IS NOT NULL Now the same with left semi-join: SELECT s.id FROM students s WHERE EXISTS (SELECT 1 FROM grades g WHERE g.student_id = s.id) The latter is … Read more
You would use an INNER join to establish the relationship between the common gameid field; select votes.userid, games.title from games inner join votes on (votes.gameid = game.gameid) where votes.userid = ‘a’
More possibilities : w = unique(x[y,which=TRUE]) # the row numbers in x which have a match from y x[w] If there are duplicate key values in x, then that needs : w = unique(x[y,which=TRUE,allow.cartesian=TRUE]) x[w] Or, the other way around : setkey(y,x) w = !is.na(y[x,which=TRUE,mult=”first”]) x[w] If nrow(x) << nrow(y) then the y[x] approach should … Read more