CODEWITHSUNDEEP
mysqljoin
Tomislav Nikolic
Tomislav Nikolic

Reputation: 171

Reversing inner join query

Alright so I am trying to reverse this INNER JOIN query. I want to select ALL accounts that dont have a field inside the verification table

I tried this way (tried this as an attempt) but failed

SELECT users.*,
user_activations.user_id as user_ID
FROM users 
INNER JOIN user_activations ON 
user_activations.user_id = users.ID
WHERE user_activations.user_id IS NULL

Upvotes: 2

Views: 1851

Answers (1)

Kamil Gosciminski
Kamil Gosciminski

Reputation: 17147

You could do this several ways. Note the use of sql aliases to shorten your code.

1) Use LEFT JOIN with IS NULL condition on user_activations PK (or a not null column) which is called an ANTI-JOIN

SELECT 
  u.*
FROM users u
LEFT JOIN user_activations ua ON 
  ua.user_id = u.ID
WHERE ua.user_id IS NULL

2) Use NOT EXISTS

SELECT 
  u.*
FROM users u
WHERE NOT EXISTS (
  SELECT *
  FROM user_activations ua
  WHERE ua.user_id = u.ID
)

Note that in the (1) option we are making use of LEFT JOIN combined with a WHERE condition which is applied to whole joined dataset and effectively removes rows that would match with the INNER JOIN (intersection of data from both tables through common column values).

In (2) option we have a contradicted EXISTS operator using NOT keyword which when found any matching row based on the WHERE clause inside a subquery, discards it from the output.

Upvotes: 2

Related Questions