MySQL SELECT Count statement issue

Question

I have two MySQL queries:

First:

SELECT DISTINCT (broker.broker_id),company_id ,broker_name,company_name,mobile1_no,email,pan_card_num,broker_id,broker_id,company_id
FROM broker_firm AS broker_firm
LEFT JOIN broker ON broker_firm.company_id = broker.firm_id
AND broker_firm.is_active =1
AND broker.is_active =1

This query is generating 331 results.

Second:

SELECT COUNT( broker.broker_id ) AS tot
FROM broker_firm AS broker_firm
LEFT JOIN broker AS broker ON broker_firm.company_id = broker.firm_id
AND broker_firm.is_active =1
AND broker.is_active =1

This query is generating 289 results.

Can anyone please tell me the reason why? I expected both of the results to be same. Or maybe, the Count(*) result to be greater.

Thanks in advance

Gordon Linoff · Accepted Answer · 2014-05-24 11:03:38Z

1

When you do a left join, the logic is simple: keep all the rows in the first table, regardless of whether the condition in the on clause is true. If the condition is false, then all the columns in the second table get a value of NULL.

When you do an inner join, the logic is to keep all rows in the first table.

In the first query, the additional conditions are in the on clause. Hence, all rows in the first table are kept (and don't forget that the join itself may result in duplicates). In the second query, the where clause has a condition broker.is_active = 1. This condition will fail when is_active is NULL -- which is what happens when the records don't match. In other words, the condition is turning the left join into an inner join.

EDIT:

The idea is the same. The second query is counting the matching records. count(broker.broker_id) counts the non-NULL values for that column. This is the same as doing an inner join.

The first query is counting all records. select distinct selects distinct values of all the columns. Your syntax is a bit confusing, because it suggests that you just want the distinct value of one column. But that is not how it works. Because you have columns from both tables in the select, the non-matching brokers will have their company information on the row, making that row distinct from all other rows.

edited May 24, 2014 at 11:03

answered May 24, 2014 at 10:42

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Tushar Over a year ago

when I replace the where with and in the second query the results are all the same.

echo_Me · Accepted Answer · 2014-05-24 11:19:27Z

0

why dont you use both in one query ?

 SELECT broker.broker_id,company_id ,broker_name,company_name,mobile1_no,email,pan_card_num,broker_id,broker_id,company_id,COUNT( broker.broker_id ) AS tot
 FROM broker_firm AS broker_firm
 LEFT JOIN broker ON broker_firm.company_id = broker.firm_id
 AND broker_firm.is_active =1
 AND broker.is_active =1
 GROUP BY broker.broker_id

answered May 24, 2014 at 11:19

echo_Me

37.2k5 gold badges62 silver badges81 bronze badges

Comments

Abylay Sabirgaliyev · Accepted Answer · 2014-05-24 11:27:13Z

0

First query counts all firms, you have 42 firms with no broker. try

select count(broker_firm.company_id)
FROM broker_firm
where broker_firm.company_id not in (select firm_id from broker)

edited May 24, 2014 at 11:27

answered May 24, 2014 at 11:15

Abylay Sabirgaliyev

7369 silver badges18 bronze badges

Collectives™ on Stack Overflow

MySQL SELECT Count statement issue

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related