CODEWITHSUNDEEP
sqljoin
user1899415
user1899415

Reputation: 3125

SQL: how do you join to give you INVERSE rows?

I have a query that looks like:

SELECT b.user_name FROM
(SELECT user_name FROM my_table_name
WHERE date='2013-07-11') a
JOIN
(SELECT user_name FROM my_table_name
WHERE date='2013-06-11') b
ON 
a.user_name != b.user_name

How do I do a JOIN that gives me the names in June, but NOT IN July?

Upvotes: 0

Views: 51

Answers (1)

Martin Smith
Martin Smith

Reputation: 453212

This is known as an anti join. The SQL Standard EXCEPT operator can be used if your RDBMS supports it.

SELECT user_name
FROM   my_table_name
WHERE  date = '2013-06-11'
EXCEPT
SELECT user_name
FROM   my_table_name
WHERE  date = '2013-07-11'

Other possibilities include NOT IN, NOT EXISTS or LEFT OUTER JOIN ... NULL or you could use GROUP BY as below.

SELECT user_name
FROM   my_table_name
WHERE  date IN ('2013-06-11', '2013-07-11')
GROUP BY user_name
HAVING MAX(date) = '2013-06-11'

Upvotes: 2

Related Questions