MySQL - JOINS (excepts)

Question

I'm trying to find 2 different result via JOINS. Table look like;

SELECT id,member_id,registered_year FROM records;

I can listing which members registered in 2012 and also in 2013 with;

SELECT member_id FROM records a
INNER JOIN records b ON a.member_id=b.member_id
WHERE  a.registered_year='2013' AND b.registered_year='2012';

But I can't list revers of It. How can I list which members were registered in 2012 but not in 2013?

Thnx in advance.

If there is only one table named "records", there is no join necessary. — Niro, Jun 27 '13 at 22:32
@Orin, I think (also from your answer) you don’t understand the structure of the table. It has several rows per member, one for each year. — chirlu, Jun 27 '13 at 22:35
Understood, thank you. Removed my answer to prevent confusion. — Niro, Jun 27 '13 at 22:36

Ian Kenney · Answer 1 · 2013-06-27T22:29:35.770

2

SELECT member_id FROM records a 
WHERE a.registered_year='2012' and  member_id not in 
(
  SELECT member_id FROM records
  WHERE registered_year='2013'
)

the inner select SELECT member_id FROM records WHERE registered_year='2013' gets all users who where registered in 2013

the outer one gets all users that where not in the inner select who where registered in 2012

edited Jun 27 '13 at 22:29

answered Jun 27 '13 at 22:23

Ian Kenney

6,376
1
25
44

`WHERE a.registered_year='2013'` --- will always be falsy!!! "does not answer the question !" (c) – zerkms Jun 27 '13 at 22:24
yep - bad copy paste - fixed – Ian Kenney Jun 27 '13 at 22:27
It's interesting how mysql would optimize this. `member_id not in ` would definitely cause a scan, but would it perform a huge nested `select` "everything" for every row – zerkms Jun 27 '13 at 22:28
1

good read here http://stackoverflow.com/questions/2246772/whats-the-difference-between-not-exists-vs-not-in-vs-left-join-where-is-null on performance diffs - you can always check with explain for the particular query – Ian Kenney Jun 27 '13 at 22:32
1

bookmarked it thanks. PS: an article about optimizer behaviour may become a bit inactual after 4 years :-S – zerkms Jun 27 '13 at 22:34

score 2 · Answer 2 · answered Jun 27 '13 at 22:23

2

SELECT member_id
  FROM records a
 WHERE registered_year = 2012
   AND NOT EXISTS (SELECT null
                     FROM records b
                    WHERE a.member_id = b.member_id
                      AND b.registered_year = 2013)

answered Jun 27 '13 at 22:23

zerkms

249,484
69
436
539

score 1 · Answer 3 · edited Jun 27 '13 at 22:33

1

For users which are not in both tables, you can use a left join rather than an inner join and then test for null.

Example:

SELECT a.member_id 
FROM records a
  LEFT JOIN records b ON  a.member_id = b.member_id
                      AND b.registered_year = '2013' 
WHERE a.registered_year = '2012' 
  AND b.registered_year IS NULL;

edited Jun 27 '13 at 22:33

ypercubeᵀᴹ

113,259
19
174
235

answered Jun 27 '13 at 22:21

exussum

18,275
8
32
65

1

With this way, also listing 2010, 2011s too. I only need registered in 2012 and not registered in 2013s. – Looden Jun 27 '13 at 22:26
it shouldnt be ? Can you post an example on SQLfiddle ? – exussum Jun 27 '13 at 22:27
1

Should be `ON a.member_id=b.member_id AND b.registered_year='2013' WHERE ...` – ypercubeᵀᴹ Jun 27 '13 at 22:31

score 1 · Answer 4 · answered Jun 27 '13 at 22:41

1

This is usually solved with NOT EXISTS (or the equivalent LEFT JOIN / IS NULL and NOT IN constructions.) In this particular case, there is a way with GROUP BY:

SELECT member_id 
FROM records
WHERE registered_year IN (2012, 2013)
GROUP BY member_id
HAVING MAX(registered_year) = 2012 ;

I doubt this will be efficient but you can test along with the other versions.

answered Jun 27 '13 at 22:41

ypercubeᵀᴹ

113,259
19
174
235

"registered in 2012 but **not in** 2013" – zerkms Jun 27 '13 at 22:41
@zerkms: Yes. That's what it does. And the MIN comparison is redundant. – ypercubeᵀᴹ Jun 27 '13 at 22:42

MySQL - JOINS (excepts)

4 Answers4