Re: Single SELECT vs. multiple SELECTs on UNrelated tables ??
From: Les Gainous <lesgainous_at_earthlink.net>
Date: 1996/10/03
Message-ID: <3254A66C.4D39_at_earthlink.net>#1/1
go
Date: 1996/10/03
Message-ID: <3254A66C.4D39_at_earthlink.net>#1/1
Craig,
Since you are concerned about "across-the-network calls" (we all are), you could use two differenct methods:
- UNION:
SELECT * FROM TABLE_A a where a.col1=1 UNION SELECT * FROM TABLEB b where b.col1=99
or,
2) Stored Procedure:
create proc pMyProc as
SELECT * FROM TABLE_A a where a.col1=1 SELECT * FROM TABLEB b where b.col1=99
go
Using the stored procedure depicted in #2, you will get two result sets. Some front-ends can't easily handle multiple result sets. If that's the case then use a hybrid put the logic in #1 (union) in a stored procedure (as in #2).
Either way, you're only going "down the wire" only one round trip (one way submits the command, the return leg gives you the results).
good.luck = true
-- Les Gainous, lesgainous_at_earthlink.net Visit my web page at http://home.earthlink.net/~lesgainous Looking for a Client-Server job in California? http://home.earthlink.net/~lesgainous/jobs.html Craig wrote:Received on Thu Oct 03 1996 - 00:00:00 CEST
>
> SELECT * FROM TABLE_A a, TABLE_B b where a.col1=1 and b.col1=99 [this
> returns 1,2,3,98,99]
>
> versus
>
> SELECT * FROM TABLE_A a where a.col1=1 [this returns 1,2,3]
>
> SELECT * FROM TABLEB b where b.col1=99 [this returns 98,99]
>
> These are two absolutely unrelated tables and each return only one row for
> the above specified conditions. So my question is:
>
> which is more efficient: the one select or two selects?
>
> To add more complexity, if this is a client/server application and you want
> as few "across-the-network" calls to the database as possible, would the
> single select have even greater merit. I would think so, but am interested
> in input from the SQL world.
>
> Thanks.
>
> Chris