A problem that requires attention when the keywords in the Multi-Table connection query are incorrect.

A problem that requires attention when the keywords in the Multi-Table connection query are incorrect.

Author: iamlaosong

In multi-table join queries, the most common thing is inner join, and the most commonly used in inner join is equivalent join. That is, use the equal sign (=) operator in the connection condition to compare the column values in the connected column, all columns in the connected table, including duplicate columns, are listed in the query results. For example:

Select * from tb_evt_mail_clct a, tb_evt_dlv c
Where a. clct_date between to_date ('1970-6-11 ', 'yyyy-mm-dd') and
To_date ('2017-6-11 ', 'yyyy-mm-dd ')
And a. mail_num = c. mail_num;

In the preceding query, the mail number mail_num in Table A (receiving table) and Table C (Shipping table) does not necessarily correspond one to one. It is possible that the email number in Table A does not exist in Table C, query by the preceding statement. These emails will not appear. If you need to appear, we generally add A "(+)" to the right of the equation to list all the content in table, as follows:

Select * from tb_evt_mail_clct a, tb_evt_dlv c
Where a. clct_date between to_date ('1970-6-11 ', 'yyyy-mm-dd') and
To_date ('2017-6-11 ', 'yyyy-mm-dd ')
And a. mail_num = c. mail_num (+ );

To count the number of mails that do not exist in Table C (the number of undelivered mails) in Table A, use the following statement:

Select count (*) from tb_evt_mail_clct a, tb_evt_dlv c
Where a. clct_date between to_date ('1970-6-11 ', 'yyyy-mm-dd') and
To_date ('2017-6-11 ', 'yyyy-mm-dd ')
And a. mail_num = c. mail_num (+)

And c. mail_num is null;

We can use the following statement to count the number of properly sent mails. The condition is c. dlv_sts_code = 'I', that is:

Select count (*) from tb_evt_mail_clct a, tb_evt_dlv c
Where a. clct_date between to_date ('1970-6-11 ', 'yyyy-mm-dd') and
To_date ('2017-6-11 ', 'yyyy-mm-dd ')
And a. mail_num = c. mail_num (+)

And c. dlv_sts_code = 'I ';

However, if we need to count the number of unauthorized mails, the following statement is not true and the result is 0:

Select count (*) from tb_evt_mail_clct a, tb_evt_dlv c
Where a. clct_date between to_date ('1970-6-11 ', 'yyyy-mm-dd') and
To_date ('2017-6-11 ', 'yyyy-mm-dd ')
And a. mail_num = c. mail_num (+)

And c. dlv_sts_code = 'I'

And c. mail_num is null;

This is because, when the condition c. mail_num is null is established, the value of c. dlv_sts_code is also null and cannot be equal to 'I'. Therefore, the statistical result is 0, that is, When the number of mail_num values in Table C is null, other filtering conditions cannot be used.To make statistics, use the following statement: select count (*) from tb_evt_mail_clct
Where a. clct_date between to_date ('1970-6-11 ', 'yyyy-mm-dd') and
To_date ('2017-6-11 ', 'yyyy-mm-dd ')
And not exists (select 1
From tb_evt_dlv c
Where c. mail_num = a. mail_num
And c. dlv_sts_code = 'I ')