SQL question -- TIP on Valentina SQL

[Ruslan Zasukhin]

On 11/30/05 5:23 PM, "Ivan Smahin" <IvanSmahin at public.kherson.ua> wrote:

> Hello Florian,
> 
> Tuesday, November 29, 2005, 9:33:22 PM, you wrote:
> 
>> Hi all!
>> 
>> Say I have two tables:
>> 
>> TABLE CUSTOMER with fields ID,NAME,ADRESS
>> 
>> And
>> 
>> TABLE PAYBACKCARD with fields CUSTOMER_ID,ID,CARDNUMBER
>> 
>> If I search for customers with cards, I do "SELECT * FROM
>> CUSTOMER,PAYBACKCARD WHERE CUSTOMER.ID=PAYBACKCARD.CUSTOMER_ID"
>> 
>> Fine. But now I want to find all customers WITHOUT card. How can I do
>> this?
>> In this case there is no link between the two tables, I need
>>   "SELECT * FROM CUSTOMER WHERE there is no link between the tables..."

> There is at least 2 ways.
> 
> 1. SELECT * FROM t1 WHERE t1.id NOT IN ( SELECT RecID FROM T1,T2 ...)
> 
> 2. SELECT * FROM t1
>    EXCEPT
>    SELECT RecID FROM T1,T2 ...
> 
> Also. You can consider to have a link between tables.
> So you will get a lot of link-specific opportunities.

Florian, 

It is not clear from your letter if you have FOREIGN KEY for this table.
IF you have it, then you have LINK from Valentina point of view.

And then wow, you can use MUCH MORE effective SQL than above 2 standard
ways:

> 1. SELECT * FROM t1 WHERE COUNT_LINKED(t1, LNK_t1_t2) = 0

Right.
 
You can see that in this case Valentina do not need do JOIN and other hard
operations. Valentina will just use link. This is much faster.


> 2. SELECT * FROM t1 WHERE t1->CUSTOMER_ID is NULL

Ivan, this query will require jumps to second table, so it is not so
effective.


-- 
Best regards,

Ruslan Zasukhin
VP Engineering and New Technology
Paradigma Software, Inc

Valentina - Joining Worlds of Information
http://www.paradigmasoft.com

[I feel the need: the need for speed]