Обсуждение: SQL Join - MySQL/PostgreSQL difference?
Greetings. I'm working with a product provided by a third part that interfaces to data housed in a database of your choice. Previously, my choice was MySQL - apparently it handled certain queries too slowly, so I'm giving PostgreSQL a shot. Here's the query: SELECT a.Number, a.Code, a.Text FROM b, a WHERE (b.Id = a.Id) AND (VersionId = 'key1') AND (Category = 'key2') AND (b.d_Id = 'key3') ORDER BY a.Number; (my apologies: I had to 'mangle' the table/column names because of NDA) So my question is this: would this query operate differently in MySQL than in PostgreSQL? The reason I ask is that this query in MySQL returns results, yet in PostgreSQL it does not. I read a post about PostgreSQL not supporting outer joins, but I don't have enough experience with SQL to determine if this is such a query or not. Please advise. Any help will be (as always) sincerely appreciated. -- Brice Ruth WebProjkt, Inc. VP, Director of Internet Technology http://www.webprojkt.com/
Brice Ruth writes: > SELECT > a.Number, > a.Code, > a.Text > FROM > b, > a > WHERE > (b.Id = a.Id) AND > (VersionId = 'key1') AND > (Category = 'key2') AND > (b.d_Id = 'key3') > ORDER BY > a.Number; > > (my apologies: I had to 'mangle' the table/column names because of NDA) > > So my question is this: would this query operate differently in MySQL > than in PostgreSQL? The reason I ask is that this query in MySQL > returns results, yet in PostgreSQL it does not. Without showing the tables and the data in it, it's fairly hard to tell. I think MySQL does case insensitive string comparisons; check that. > I read a post about PostgreSQL not supporting outer joins, but I don't > have enough experience with SQL to determine if this is such a query > or not. Please advise. This is not an outer join. -- Peter Eisentraut peter_e@gmx.net http://yi.org/peter-e/
On Thu, 1 Feb 2001, Brice Ruth wrote: ->SELECT -> a.Number, -> a.Code, -> a.Text ->FROM -> b, -> a ->WHERE -> (b.Id = a.Id) AND These next two statements are very ambiguous. Make them explicit as you have with "(b.Id = a.Id)" and "(b.d_Id = 'key3')" Also, be sure that 'key3' is how what you want looks in the database -> (VersionId = 'key1') AND -> (Category = 'key2') AND -> (b.d_Id = 'key3') ->ORDER BY -> a.Number; Also, make sure ( '\d b' ) that your columns are case-sensatively named 'Id' and such as this does matter. -- Dave
Ian, Thanx - I figured the same about the ambiguity. I'll keep tryin' to debug this with the vendor, then. -Brice Ian Harding wrote: > > Brice Ruth wrote: > > > Greetings. > > > > I'm working with a product provided by a third part that interfaces to > > data housed in a database of your choice. Previously, my choice was > > MySQL - apparently it handled certain queries too slowly, so I'm giving > > PostgreSQL a shot. Here's the query: > > > > SELECT > > a.Number, > > a.Code, > > a.Text > > FROM > > b, > > a > > WHERE > > (b.Id = a.Id) AND > > (VersionId = 'key1') AND > > (Category = 'key2') AND > > (b.d_Id = 'key3') > > ORDER BY > > a.Number; > > > > (my apologies: I had to 'mangle' the table/column names because of NDA) > > > > So my question is this: would this query operate differently in MySQL > > than in PostgreSQL? The reason I ask is that this query in MySQL > > returns results, yet in PostgreSQL it does not. I read a post about > > PostgreSQL not supporting outer joins, but I don't have enough > > experience with SQL to determine if this is such a query or not. Please > > > > advise. > > > > Any help will be (as always) sincerely appreciated. > > > > -- > > Brice Ruth > > WebProjkt, Inc. > > VP, Director of Internet Technology > > http://www.webprojkt.com/ > > It should work the same in both. The only thing I notice is that not all > the field names are qualified with table names or aliases. That can lead > to ambiguity, but the query would blow up on both databases if that were a > problem. > > Ian -- Brice Ruth WebProjkt, Inc. VP, Director of Internet Technology http://www.webprojkt.com/
Here's some more details:
The following query:
SELECT * FROM tblpemdruglink WHERE drugid = 'DG-5039';
returns the following results:
drugid | monographid
---------+-------------DG-5039 | 2008
(1 row)
the following query:
SELECT * FROM tblfdbmono WHERE monographid='2008' AND versionid='FDB-PE' AND category='PEM';
returns the following results:
versionid | category | monographid | sequencenumber | sectioncode | linetext
-----------+----------+-------------+----------------+-------------+---------------------------------------------------------------------
{content}
sorry, the {content} is what's under NDA :(
In any case, you can see the structure of the tables tblpemdruglink and
tblfdbmono from here.
Now the following query:
SELECT sequencenumber,sectioncode,linetext FROM tblfdbmono fdb, tblpemdruglink pem WHERE
fdb.monographid=pem.monographidAND fdb.versionid='FDB-PE' AND fdb.category='PEM' AND pem.drugid='DG-5039';
Should return the same results as the second query above. However, this
is what I get:
sequencenumber | sectioncode | linetext
----------------+-------------+----------
(0 rows)
And it takes about 3-4 seconds to return with that prognosis. What's
going on here?
-Brice
Peter Eisentraut wrote:
>
> Brice Ruth writes:
>
> > SELECT
> > a.Number,
> > a.Code,
> > a.Text
> > FROM
> > b,
> > a
> > WHERE
> > (b.Id = a.Id) AND
> > (VersionId = 'key1') AND
> > (Category = 'key2') AND
> > (b.d_Id = 'key3')
> > ORDER BY
> > a.Number;
> >
> > (my apologies: I had to 'mangle' the table/column names because of NDA)
> >
> > So my question is this: would this query operate differently in MySQL
> > than in PostgreSQL? The reason I ask is that this query in MySQL
> > returns results, yet in PostgreSQL it does not.
>
> Without showing the tables and the data in it, it's fairly hard to tell.
> I think MySQL does case insensitive string comparisons; check that.
>
> > I read a post about PostgreSQL not supporting outer joins, but I don't
> > have enough experience with SQL to determine if this is such a query
> > or not. Please advise.
>
> This is not an outer join.
>
> --
> Peter Eisentraut peter_e@gmx.net http://yi.org/peter-e/
--
Brice Ruth
WebProjkt, Inc.
VP, Director of Internet Technology
http://www.webprojkt.com/
David, About the case-sensitivity, I was under the impression that PostgreSQL was case-insensitive unless things were explicitly put in quotes. This is at least what I read in the book ... -Brice David Olbersen wrote: > > On Thu, 1 Feb 2001, Brice Ruth wrote: > > ->SELECT > -> a.Number, > -> a.Code, > -> a.Text > ->FROM > -> b, > -> a > ->WHERE > -> (b.Id = a.Id) AND > > These next two statements are very ambiguous. Make them explicit as you have > with "(b.Id = a.Id)" and "(b.d_Id = 'key3')" > > Also, be sure that 'key3' is how what you want looks in the database > > -> (VersionId = 'key1') AND > -> (Category = 'key2') AND > -> (b.d_Id = 'key3') > ->ORDER BY > -> a.Number; > > Also, make sure ( '\d b' ) that your columns are case-sensatively named 'Id' and > such as this does matter. > > -- Dave -- Brice Ruth WebProjkt, Inc. VP, Director of Internet Technology http://www.webprojkt.com/
Brice Ruth <brice@webprojkt.com> writes:
> About the case-sensitivity, I was under the impression that PostgreSQL
> was case-insensitive unless things were explicitly put in quotes.
Names in queries (of tables, fields, functions, etc) are
case-insensitive. This has nothing to do with the behavior of data
comparisons, however.
regards, tom lane
That was my understanding as well, thank you for the clarification. :) Tom Lane wrote: > > Brice Ruth <brice@webprojkt.com> writes: > > About the case-sensitivity, I was under the impression that PostgreSQL > > was case-insensitive unless things were explicitly put in quotes. > > Names in queries (of tables, fields, functions, etc) are > case-insensitive. This has nothing to do with the behavior of data > comparisons, however. > > regards, tom lane -- Brice Ruth WebProjkt, Inc. VP, Director of Internet Technology http://www.webprojkt.com/