Stack Overflow
Stack Overflow

Questions tagged [anti-join]

An anti-join, also called an excluding-join or left-outer-join, comes from requesting data from a table where some value is not in another table

Formal Definition

The antijoin, written as R ▷ S (where R and S are relations), is similar to the semijoin, but the result of an antijoin is only those tuples in R for which there is no tuple in S that is equal on their common attribute names.

Venn Diagram

anti-join

SQL Phrasing

SELECT <select_list>
FROM TableA A
LEFT JOIN TableB B
ON A.Key = B.Key
WHERE B.Key IS NULL

Related to

References:

78 questions
1
vote
1 answer

anti-join not working - giving 0 rows, why?

I am trying to use anti-join exactly as I have done many times to establish which rows across two datasets do not have matches for two specific columns. For some reason I keep getting 0 rows in the result and I can't understand why. Below are two…
user197410
  • 31
  • 4
1
vote
3 answers

How to add values from other column if conditional join does not execute?

I have two tables this one is old names Last Name|First Name|ID Clay Cassius 1 Alcindor Lou 2 Artest Ron 3 Jordan Michael 4 Scottie Pippen 5 Kanter Enes 6 New Names Last Name| First Name| ID Ali …
user35131
  • 1,105
  • 6
  • 18
1
vote
3 answers

R anti_join keep only rows from one dataframe

df1 = data.frame(Id = c(1:6), Product = c(rep("Toaster", 3), rep("Radio", 3)), Test = NA) df2 = data.frame(Id = c(2, 4, 6, 7), State = c(rep("Alabama", 2), rep("Ohio", 2))) df_sum <- anti_join(df1,df2, by = "Id") %>% bind_rows(df2) Is there an…
BroNKo
  • 39
  • 7
1
vote
1 answer

SQL query equivalent in python to: delete only one identical record in a dataframe

Problem-stmt: To Delete a column from a table[MDF] based on a condition[OP_DIRECTIVE = 'D'] from a different table[INC]. Both the tables have identical columns. I am looking for a SQL Equivalent Query in Python to Delete one identical record even if…
Mando
  • 75
  • 10
1
vote
2 answers

Master view of multiple dataframes with common columns

I have three dataframes like below: df3 <- data.frame(col1=c('A','C','E'),col2=c(4,8,2)) df2 <- data.frame(col1=c('A','B','C','E','I'),col2=c(4,6,8,2,9)) df1 <- data.frame(col1=c('A','D','C','E','I'),col2=c(4,7,8,2,9)) The differences between any…
Prradep
  • 5,506
  • 5
  • 43
  • 84
1
vote
3 answers

How to semi_join two dataframes by string column with one being colon-separated

I have two dataframes, dfa and dfb: dfa <- data.frame( gene_name = c("MUC16", "MUC2", "MET", "FAT1", "TERT"), id = c(1:5) ) dfb <- data.frame( gene_name = c("MUC1", "MET; BLEP", "MUC21", "FAT", "TERT"), id = c(6:10) ) which look like…
MonkeyBack
  • 61
  • 6
1
vote
0 answers

set difference between consecutive vectors in tibble (cumulative way)

I would like to find a shorter way of computing the consecutive difference between grouped vectors in a tibble (not A in B) where each difference is between a vector at group "x" and the concatenation of the vectors in all previous groups. I have…
Francesco Cabiddu
  • 131
  • 8
1
vote
2 answers

How to remove unmatched data from two data frames, to create a new data frame in R

I am creating a graph that correlates the life expectancy age and the state pension age for each country. I have used web scraping packages to scrape 2 datasets from 2 Wikipedia pages. One of the datasets contains the column "Country" and the other…
Nick
  • 55
  • 1
  • 7
1
vote
0 answers

Approximative double comparision in sparklyr antijoin

I am trying to push tiny changes to an existing sparklyr code ; these changes are meant to give the same results, only the code is supposed to be more readable and efficient. Therefore, I want to make sure I get the same results, which I have stored…
Vincent
  • 482
  • 6
  • 22
1
vote
2 answers

How to perform an anti-join, or left outer join, (get all the rows in a dataset which are not in another based on multiple keys) in pandas

I have two datasets: df1 = pd.DataFrame(data = {'label1': ['A', 'A', 'B', 'C'], 'label2': ['a', 'b', 'c', 'd'], 'value': [1,2,3,4]}) df2 = pd.DataFrame(data = {'label1': ['A', 'A', 'D', 'E'], 'label'2': ['a', 'd', 'c','e'], 'value2':…
Olivia Watkins
  • 526
  • 1
  • 6
  • 17
1
vote
3 answers

oracle anti-join alternatives

I'm facing a problem where I have to select rows from a table ('CHILD') that should have a foreign key towards another table ('PARENT'). The problem is that the foreign key is broken (long story short, the table is partitioned and for some reason…
francesco foresti
  • 2,004
  • 20
  • 24
1
vote
3 answers

Find all books where availability is = 0 or unknown

I have a table Books which has many properties. Properties are stored as key and value. So if Books are: 1 LOTR 2 Harry Potter 1 3 Harry Potter 2 And properties are id book_id key value 1 1 available 0 2 2 available …
Amit
  • 3,952
  • 7
  • 46
  • 80
1
vote
1 answer

MySQL exclude ids using joins

I have 2 tables: uid uname 1 alex 2 anna 3 sergey 4 arnold 5 john mid message uid 1 hello 3 2 DELETED 3 3 xcvcxv 4 4 bye 1 5 DELETED 2 6 4452 5 I would like to get all messages, but if message contains…
Dmitry Chornobyl
  • 13
  • 2
1
vote
4 answers

Oracle Anti-Join Execution plan question

We have two tables like so: Event id type ... a bunch of other columns ProcessedEvent event_id process There are indexes defined for Event(id) (PK) ProcessedEvent (event_id, process) The first represents events in an…
Jens Schauder
  • 77,657
  • 34
  • 181
  • 348
0
votes
2 answers

Selecting users from one table when not in the other

I have a query that selects a registration date of a user from one table. SELECT DATE(registered) FROM users WHERE user_id= ".$currentUser." ORDER BY registered ASC LIMIT 1 I need to modify the original query by joining another table, to make sure…
santa
  • 12,234
  • 49
  • 155
  • 255
1 2
3
4 5 6