SQL - 如果存在于另一个表中

Question

There are 2 tables: subjects and messages.

subjects table:

----id----   ----title----
1            Subject 1
2            Subject 2
3            Subject 3

messages table:

----id----   ----subject----   ----message----
1            1                 Message to Subject 1
2            1                 Message to Subject 1
3            2                 Message to Subject 2

How to get mysql_num_rows of subjects, which has got any messages in messages? The result must be 2, because Subject 1 has message in messages, and Subject 2 has message in messages, but Subject 3 hasn't got any message in messages.

Something like:

mysql_num_rows(mysql_query("SELECT * FROM subjects ...

Answer 1

You would normally use in or exists for this purpose:

select count(*)
from subjects s
where exists (select 1 from messages m where m.subject_id = s.id);

This can easily be modified to get the subjects with no messages.

If you have a proper foreign key relationship defined between the tables, then you can just count the subjects in messages:

select count(distinct m.subject_id)
from messages;

Aggregation in MySQL is quite expensive. There are circumstances where this will perform better. However, the existsis likely to perform better under most circumstances, assuming you have an index onmessages(subject_id)`.