Обсуждение: SQL Query question
Hi Whilst I'm not new to SQL I am reasonably new to Postgres and as such I have a question on the following query: SELECT tbl1."TermTypeID", tbl1."ParentID", tbl1."KeywordID", tbl1."Term", tbl2."KeywordID" FROM "Terms" As tbl1 LEFT JOIN "SearchStore" As tbl2 ON tbl1."KeywordID" = tbl2."KeywordID" AND tbl2."StockID" = 1 WHERE (tbl1."TermTypeID" >= 200) AND (tbl1."TermTypeID" < 600) AND (tbl1."IsSynonym" = false) AND (tbl1."LanguageID"= 1) ORDER BY tbl1."TermTypeID", tbl1."Term"; Why does the above query work fine and the folowing query not work? And as a additional kind of spanner in the works I've tried the following on MS SQL Server and Oracle both of which produce the correct results (i.e. the same as the above query). NB: the Terms table always has data whereas the SearchStore may or may not have any data. SELECT tbl1."TermTypeID", tbl1."ParentID", tbl1."KeywordID", tbl1."Term", tbl2."KeywordID" FROM "Terms" As tbl1 LEFT JOIN "SearchStore" As tbl2 ON tbl1."KeywordID" = tbl2."KeywordID" WHERE (tbl1."TermTypeID" >= 200) AND (tbl1."TermTypeID" < 600) AND (tbl1."IsSynonym" = false) AND (tbl1."LanguageID"= 1) AND (tbl2."StockID" = 1) ORDER BY tbl1."TermTypeID", tbl1."Term"; Just to be obvious both queries as far as I can should return everything from Terms and anything if it exists from SearchStore subject to the WHERE clause parameters - obviously! Many thanks in advance Nick
Am Donnerstag, 30. Juni 2005 11:27 schrieb Nick Stone: > SELECT > tbl1."TermTypeID", > tbl1."ParentID", > tbl1."KeywordID", > tbl1."Term", > tbl2."KeywordID" > FROM > "Terms" As tbl1 LEFT JOIN > "SearchStore" As tbl2 ON tbl1."KeywordID" = tbl2."KeywordID" > WHERE > (tbl1."TermTypeID" >= 200) AND > (tbl1."TermTypeID" < 600) AND > (tbl1."IsSynonym" = false) AND > (tbl1."LanguageID" = 1) AND > (tbl2."StockID" = 1) > ORDER BY > tbl1."TermTypeID", > tbl1."Term"; > > Just to be obvious both queries as far as I can should return everything > from Terms and anything if it exists from SearchStore subject to the WHERE > clause parameters - obviously! The condition (tbl2."StockID" = 1) will remove all rows that have null values in the tbl2 fields, thus making your left join useless. Perhaps you should change that to (tbl2."StockID" = 1 OR tbl2."StockID" IS NULL) -- or use your first version. -- Peter Eisentraut http://developer.postgresql.org/~petere/
Nick Stone wrote: > Hi > > Whilst I'm not new to SQL I am reasonably new to Postgres and as such I have > a question on the following query: > FROM > "Terms" As tbl1 LEFT JOIN > "SearchStore" As tbl2 ON tbl1."KeywordID" = tbl2."KeywordID" AND > tbl2."StockID" = 1 > Why does the above query work fine and the folowing query not work? And as a > additional kind of spanner in the works I've tried the following on MS SQL > Server and Oracle both of which produce the correct results > FROM > "Terms" As tbl1 LEFT JOIN > "SearchStore" As tbl2 ON tbl1."KeywordID" = tbl2."KeywordID" > WHERE ... > (tbl2."StockID" = 1) Hmm - I'm not sure that MSSQL/Oracle are giving the "right" answer here. I assume the tbl2.stockid test is the issue here, and we apply the test after the join whereas the others push the condition inside the join. I'm inclined to prefer PG's way of doing things, since it means you get what you explicitly asked for (to my point of view anyway). Not sure what the SQL spec says though, and in the end I suppose that's the only way to decide "right". -- Richard Huxton Archonet Ltd
Thanks for the reply at least that explains it. Nick -----Original Message----- From: Richard Huxton [mailto:dev@archonet.com] Sent: 30 June 2005 12:22 To: Nick Stone Cc: pgsql-sql@postgresql.org Subject: Re: [SQL] SQL Query question Nick Stone wrote: > Hi > > Whilst I'm not new to SQL I am reasonably new to Postgres and as such > I have a question on the following query: > FROM > "Terms" As tbl1 LEFT JOIN > "SearchStore" As tbl2 ON tbl1."KeywordID" = tbl2."KeywordID" AND > tbl2."StockID" = 1 Why does the above query work fine and the folowing > query not work? And as a additional kind of spanner in the works I've > tried the following on MS SQL Server and Oracle both of which produce > the correct results > FROM > "Terms" As tbl1 LEFT JOIN > "SearchStore" As tbl2 ON tbl1."KeywordID" = tbl2."KeywordID" > WHERE ... > (tbl2."StockID" = 1) Hmm - I'm not sure that MSSQL/Oracle are giving the "right" answer here. I assume the tbl2.stockid test is the issue here, and we apply the test after the join whereas the others push the condition inside the join. I'm inclined to prefer PG's way of doing things, since it means you get what you explicitly asked for (to my point of view anyway). Not sure what the SQL spec says though, and in the end I suppose that's the only way to decide "right". -- Richard Huxton Archonet Ltd