CODEWITHSUNDEEP
mysql
user1286499
user1286499

Reputation: 227

How do I get correct total number of select count(*) with LEFT OUTER JOIN?

Here is my total user without JOIN statement.

mysql> SELECT COUNT(*) AS total
    -> FROM users;
+--------+
| total  |
+--------+
| 608000 |
+--------+
1 row in set (0.28 sec)

And with LEFT OUTER JOIN

mysql> SELECT COUNT(*) AS total
    -> FROM users
    ->        LEFT OUTER JOIN users_icon
    ->          ON users.uid = users_icon.iconuid
    ->        LEFT OUTER JOIN icon_code
    ->          ON users_icon.icondata = icon_code.iconid;
+--------+
| total  |
+--------+
| 608084 |
+--------+
1 row in set (3.78 sec)

Here I've got different total number. With the LEFT OUTER JOIN how do I get the total number is 608000?

Upvotes: 0

Views: 2084

Answers (2)

rs.
rs.

Reputation: 27427

Use:

count(distinct users.id) to get correct count.

Upvotes: 2

sarwar026
sarwar026

Reputation: 3821

Here, LEFT OUTER JOIN may produces more count since, it will also count the table rows in users_icon and icon_code table. If a corresponding row in users_icon or icon_code is null, it will also count. It is the behavior of LEFT OUTER JOIN.

Upvotes: 1

Related Questions