Question 191881 in Mysql Question posted in
The official MySQL documentation can be found here.

aurora-mysql join results are weird

I have 2 tables and when i join them on ID column I get very absurd count. Is it a bug or something wrong from my end?

 Env: Aurora MySQL (5.7.12)
 cpu = 8 vcpu
 RAM = 64GB RAM
 Table1: 1555778 Records
 Table2: 1294229 Records

Am joining on ID column where table1.ID=table2.ID

SELECT COUNT(*) FROM  table1 t1 JOIN table2 t2 ON t1.ID = t2.ID;

+-------------+
| COUNT(*)    |
+-------------+
| 15172673205 |
+-------------+

Thank you,

Tags:

3

Answers


  1. Chosen as BEST ANSWER
    0

    Thank you @Horaciux and @kazi-mohammad-ali-nur-romel

    Abnormal behavior is due to duplicates as you stated. thank you again. Here is the query i used and see the results as expected.

    SELECT count(*) FROM table1 t1 WHERE EXISTS (SELECT NULL FROM table2 t2 WHERE t1.ID = t2.ID);
+----------+
| count(*) |
+----------+
|  1339145 |
+----------+

  2. 0

    Please try

    SELECT COUNT(*) FROM  table1 as t1 Inner JOIN table2 as t2 ON t1.ID = t2.ID;

    ***If you have duplicate values in ID column it could really generate abnormal count.

    Login or Signup to reply.
  3. 0

    As mentioned in the comments. That is the reason you have that count: duplicates. Try this

    WITH 
    CTE_Table1 AS (SELECT DISTINCT ID  AS id FROM table1),
    CTE_Table2 AS (SELECT  DISTINCT ID AS id FROM table2)

SELECT 
    count(*) from CTE_Table1  t1 inner join CTE_Table2 t2 on t1.id=t2.id
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search