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


Craig,

Since you are concerned about "across-the-network calls" (we all are), you could use two differenct methods:

  1. 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:

>
> 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
Received on Thu Oct 03 1996 - 00:00:00 CEST

Original text of this message