LEFT JOIN not working as expected with sub-query

Question

I've got the SQL query below:

SELECT message, sent_date, user_id
FROM messages
LEFT JOIN numbers ON messages.from_id = numbers.id

It returns all the rows (about 4000) in the messages table with additional columns coming from the numbers table. So far, this is what I would expect.

Now I left join this sub-query to another table, again using a left join:

SELECT message, sent_date
FROM (
    SELECT message, sent_date, user_id
    FROM messages
    LEFT JOIN numbers ON messages.from_id = numbers.id
) AS table1
LEFT JOIN users ON table1.user_id = users.id

However, it only returns about 200 rows so many are missing. Since this is a left join I would expect all the rows from table1 to be in the result. Can anybody see what the issue is?

Edit:

So for information here are the 3 relevant tables (with irrelevant columns removed):

CREATE TABLE IF NOT EXISTS `messages` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `message` text CHARACTER SET utf8 NOT NULL,
  `from_id` int(11) DEFAULT NULL,
  `sent_date` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `from_id` (`from_id`),
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=101553 ;

CREATE TABLE IF NOT EXISTS `numbers` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) DEFAULT NULL,
  `number` varchar(32) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=6408 ;

CREATE TABLE IF NOT EXISTS `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(256) CHARACTER SET utf8 DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2395 ;

Do you have `WHERE` in the last query that you didn't include in the question? — ypercubeᵀᴹ, Aug 09 '12 at 10:13
Is it possible that in between some other connection deleted or updated some rows from table? — Omesh, Aug 09 '12 at 10:15
Do you have same behaviour if you change the internal select to: `SELECT message, sent_date, numbers.user_id`? — ypercubeᵀᴹ, Aug 09 '12 at 11:36
@ypercube, I've just tried this but still the same number of results. I still cannot make sense of this query results. In case it helps, I've added the 3 relevant tables to the main post. — laurent, Aug 10 '12 at 09:13
@ypercube, it's version 5.5.16 - MySQL Community Server (GPL) — laurent, Aug 10 '12 at 11:29
I think you are either doing something wrong or you have a hit a bug. Can you test or upgrade in a newer version of MySQL? — ypercubeᵀᴹ, Aug 10 '12 at 13:54

Omesh · Accepted Answer · 2012-08-09T10:38:52.827

2

You can try alternative method to debug the issue:

CREATE TEMPORARY table tmp1 AS SELECT message, sent_date, user_id
                               FROM messages
                                    LEFT JOIN numbers 
                                        ON messages. from_id = numbers.id;

and then see whether this query works.

 SELECT message, sent_date
 FROM tmp1 table1
      LEFT JOIN users 
          ON table1.user_id = users.id;

Also for your case make sure that there are no other insert or updates in between. otherwise use transactions.

edited Aug 09 '12 at 10:38

answered Aug 09 '12 at 10:12

Omesh

27,801
6
42
51

Yes, this is indeed working. Athough I'd be curious to know why the equivalent query, with subquery, doesn't work? – laurent Aug 10 '12 at 09:20
And the original still doesn't work? Are you sure you have not set accidentally a limit (200) on the result set? – ypercubeᵀᴹ Aug 10 '12 at 09:26

podiluska · Answer 2 · 2012-08-09T10:32:18.790

1

~~table1 sometimes won't have a UserID - so that'll be null, so those results will be missing?~~

edited Aug 09 '12 at 10:32

answered Aug 09 '12 at 10:07

podiluska

50,950
7
98
104

3

`user_id` is indeed sometime null, but since table1 is on the left, shouldn't all its results be included? If not, how can the query be fixed? – laurent Aug 09 '12 at 10:21
1

Actually, I'm talking rubbish. Do you have a result limit set on your query window? – podiluska Aug 09 '12 at 10:30
@podiluska: I think you are right, the most probbale cause is a result limit. @Laurent, what is the result if you replace the `SELECT message, sent_date FROM (...` with `SELECT COUNT(*) FROM (...` ? – ypercubeᵀᴹ Aug 09 '12 at 11:42

Mike Perrenoud · Answer 3 · 2012-08-09T11:13:22.250

0

Try this, I think it's a scoping issue on user_id.

SELECT table1.message, table1.sent_date 
FROM ( 
    SELECT messages.message, messages.sent_date, numbers.user_id 
    FROM messages 
    LEFT JOIN numbers ON messages.from_id = numbers.id 
) AS table1 
LEFT JOIN users ON table1.user_id = users.id

I'm not sure if user_id is in messages or numbers.

edited Aug 09 '12 at 11:13

answered Aug 09 '12 at 10:15

Mike Perrenoud

66,820
29
157
232

It also only returns 200 results. – laurent Aug 09 '12 at 10:23

score 0 · Answer 4 · answered Aug 09 '12 at 10:29

I don't have an exact answer to your question, but if I have to start thinking, I will first find out what 3800 rows are missing and try to see the pattern (is it because user_id are null or duplicate)

SELECT message, sent_date, user_id
FROM messages
LEFT JOIN numbers ON messages.from_id = numbers.id
MINUS
(SELECT table1.message, table1.sent_date, table1.user_id
FROM (
    SELECT message, sent_date, user_id
    FROM messages
    LEFT JOIN numbers ON messages.from_id = numbers.id
) AS table1
LEFT JOIN users ON table1.user_id = users.id)

I can't quite see any pattern. There are duplicate user_id in the final results because one user can send more than one message, there are also NULL user_id, it seems quite random to me. The query seems to include or exclude results without any particular reason. — laurent, Aug 10 '12 at 09:09

ypercubeᵀᴹ · Answer 5 · 2012-08-10T09:40:55.103

0

There is no way this should happen.

Try this variation:

SELECT 
    m.message, m.sent_date, n.user_id
FROM
    messages m
  LEFT JOIN 
    numbers AS n  ON m.from_id = n.id
  LEFT JOIN 
    users AS u  ON n.user_id = u.id ;

edited Aug 10 '12 at 09:40

answered Aug 09 '12 at 11:28

ypercubeᵀᴹ

113,259
19
174
235

LEFT JOIN not working as expected with sub-query

5 Answers5