Question: I have this query and I'd like to *select the **inverse** of this query* (the ones which are not concerned.

```
SELECT*
FROM input1
LEFT OUTER JOIN (
    SELECT MAX(Ref) AS Ref, Nomlatin, Lieudit, Commune
    FROM input1
    GROUP BY Nomlatin, Lieudit, Commune
) as t1 
ON input1.Ref = t1.Ref
WHERE t1.Ref IS NULL

```

I have already searched for answers to this question but I didn't succeed to use the `NOT IN`.

If someone can help me, it would be great.

Thanks

Answer: 
**1)** In the first line we don't need `paste`. In the next line we don't need `url(...)`. Also we have limited the input to 1000 lines to illustrate the example in less time. We can omit the `gsub` if we specify `na.strings` in `fread`. Also collapsing the input to a single string allows elimination of `textConnection` in `fread`.

```
library(data.table)

name <- "https://www.sec.gov/Archives/edgar/full-index/2016/QTR1/master.idx"
master <- readLines(name, 1000)
master <- master[grep("SC 13(D|G)", master)]
master <- paste(master, collapse = "\n")
master_table <- fread(master, sep = "|", na.strings = "")

```

**2)** A second approach which may be faster is to download the file first and then `fread` it as shown. 

```
name <- "https://www.sec.gov/Archives/edgar/full-index/2016/QTR1/master.idx"
download.file(name, "master.txt")
master_table <- fread('findstr "SC 13[DG]" master.txt', sep = "|", na.strings = "")

```

The above is for Windows. For Linux with bash replace the last line with:

```
master_table <- fread("grep 'SC 13[DG]' master.txt", sep = "|", na.strings = "")

```
I'm not quite sure of the broader context, in particular whether you need to use `fread()`, but

```
s <- scan(text=master, sep="|", what=character())

```

works well, and fast (0.1 seconds).