Re: [SQL] simple SQL query
On Wednesday 29 October 2008 18:39:42 Kevin Duffy wrote: Hello: I have a couple of queries that are giving me headaches. They are actually very simple, but I do not understand why I am not getting the expected results. Maybe I need new glasses. Please be kind. The table definitions are below. The table TMP_INDEX_MEMBER contains 21057 rows. These rows contain 3167 distinct ISINs. ISIN is a type of unique security identifier. This query select * from security where securitytypekey NOT IN ( 5,27) and ISIN IN (select ISIN from tmp_index_member ) returns 3069 rows. This tells me that there are 3069 ISINs in the SECURITY table. ISINs that I already know about. update tmp_index_member set securitykey = security.securitykey from security where securitytypekey NOT IN (5,27) and tmp_index_member.ISIN = security.ISIN results in Query returned successfully: 20545 rows affected, 2169 ms execution time. There are now 512 row in TMP_INDEX_MEMBER that have not been updated. OK now the confusion begins. I would expect the following query to return 512 rows. It returns zero. select * from tmp_index_member tim where tim.ISIN NOT IN (select distinct sec.ISIN from security sec where securitytypekey NOT IN ( 5,27) ) I want to add to SECURITY the securities that are new to me. To do this I need the above query to work. I bet you have NULLs in some of the rows so your NOT IN doesn't work. I suggest you rewrite to something like: ... WHERE (securitytypekey IS NOT NULL OR securitytypekey NOT IN (5,27))... Question: does a UNIQUE constraint create an index? Yes. Maybe your fresh eyes will see something obvious. Many thanks to taking a look at this issue. -- Andreas Joseph Krogh [EMAIL PROTECTED] Senior Software Developer / CEO +-+ OfficeNet AS| The most difficult thing in the world is to | Karenslyst Allé 11 | know how to do a thing and to watch | PO. Box 529 Skøyen | somebody else doing it wrong, without | 0214 Oslo | comment.| NORWAY | | Tlf:+47 24 15 38 90 | | Fax:+47 24 15 38 91 | | Mobile: +47 909 56 963 | | +-+ -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] simple SQL query
Gentlemen: Thanks so much for your assistance. This returns 512 rows. select * from tmp_index_member tim where tim.ISIN NOT IN (select ISIN from security sec where ISIN is NOT NULL and securitytypekey IS NOT NULL and securitytypekey NOT IN ( 5,27) ) Can someone explain why the NULL ISINs in Security is causing so much grief? I do not get it. KD -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Andreas Joseph Krogh Sent: Wednesday, October 29, 2008 3:58 PM To: pgsql-sql@postgresql.org Subject: Re: [SQL] simple SQL query On Wednesday 29 October 2008 18:39:42 Kevin Duffy wrote: Hello: I have a couple of queries that are giving me headaches. They are actually very simple, but I do not understand why I am not getting the expected results. Maybe I need new glasses. Please be kind. The table definitions are below. The table TMP_INDEX_MEMBER contains 21057 rows. These rows contain 3167 distinct ISINs. ISIN is a type of unique security identifier. This query select * from security where securitytypekey NOT IN ( 5,27) and ISIN IN (select ISIN from tmp_index_member ) returns 3069 rows. This tells me that there are 3069 ISINs in the SECURITY table. ISINs that I already know about. update tmp_index_member set securitykey = security.securitykey from security where securitytypekey NOT IN (5,27) and tmp_index_member.ISIN = security.ISIN results in Query returned successfully: 20545 rows affected, 2169 ms execution time. There are now 512 row in TMP_INDEX_MEMBER that have not been updated. OK now the confusion begins. I would expect the following query to return 512 rows. It returns zero. select * from tmp_index_member tim where tim.ISIN NOT IN (select distinct sec.ISIN from security sec where securitytypekey NOT IN ( 5,27) ) I want to add to SECURITY the securities that are new to me. To do this I need the above query to work. I bet you have NULLs in some of the rows so your NOT IN doesn't work. I suggest you rewrite to something like: ... WHERE (securitytypekey IS NOT NULL OR securitytypekey NOT IN (5,27))... Question: does a UNIQUE constraint create an index? Yes. Maybe your fresh eyes will see something obvious. Many thanks to taking a look at this issue. -- Andreas Joseph Krogh [EMAIL PROTECTED] Senior Software Developer / CEO +-+ OfficeNet AS| The most difficult thing in the world is to | Karenslyst Allé 11 | know how to do a thing and to watch | PO. Box 529 Skøyen | somebody else doing it wrong, without | 0214 Oslo | comment.| NORWAY | | Tlf:+47 24 15 38 90 | | Fax:+47 24 15 38 91 | | Mobile: +47 909 56 963 | | +-+ -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] simple SQL query
On Wednesday 29 October 2008 21:56:14 Kevin Duffy wrote: Gentlemen: Thanks so much for your assistance. This returns 512 rows. select * from tmp_index_member tim where tim.ISIN NOT IN (select ISIN from security sec where ISIN is NOT NULL and securitytypekey IS NOT NULL and securitytypekey NOT IN ( 5,27) ) Can someone explain why the NULL ISINs in Security is causing so much grief? I do not get it. Sure. BTW; I ment IS NULL OR securitytypekey NOT IN (5,27). Remember that WHERE col NOT IN (list) doesn't match NULL-values for col, so these will both return false for NULL-value of col: WHERE col NOT IN (2,3) WHERE col = 2 The reason is that NULL is unknown, so testing against it also returns unknown(NULL). -- Andreas Joseph Krogh [EMAIL PROTECTED] Senior Software Developer / CEO +-+ OfficeNet AS| The most difficult thing in the world is to | Karenslyst Allé 11 | know how to do a thing and to watch | PO. Box 529 Skøyen | somebody else doing it wrong, without | 0214 Oslo | comment.| NORWAY | | Tlf:+47 24 15 38 90 | | Fax:+47 24 15 38 91 | | Mobile: +47 909 56 963 | | +-+ -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] simple SQL query
Kevin Duffy [EMAIL PROTECTED] writes: Can someone explain why the NULL ISINs in Security is causing so much grief? I do not get it. NULL generally is taken as unknown in SQL comparisons. So if you have any nulls in the output of the sub-select, what the upper select sees is a situation like where 42 NOT IN (1,2,3, ..., NULL, ...) Now, if it finds 42 in the subquery output, it can say definitively that the result of NOT IN is FALSE, because 42 clearly *is* in the output. However, if it doesn't find a match, then what does that NULL represent? It's unknown, and therefore whether it's equal to 42 is unknown, and so the result of the NOT IN is unknown. And WHERE treats an unknown result the same as FALSE, so you don't get an output row from the upper query. NOT IN is generally pretty evil and best avoided: the funny behavior with nulls makes it not only a trap for novices, but hard for the system to optimize. Consider recasting as NOT EXISTS instead. regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
