Home » SQL & PL/SQL » SQL & PL/SQL » Set Operations on Columns (not Tables)? (Oracle 11g)
Set Operations on Columns (not Tables)? [message #420064] Wed, 26 August 2009 21:33 Go to next message
jeffpc
Messages: 4
Registered: August 2009
Junior Member
Hi Oracle gurus,

How do you efficiently compare multiple columns between tables?

I'm dreaming of the following SQL:

Select t1.c1, t2.c1 from t1 INNER JOIN t2 ON (T1.C2 <> T2.C2)
WHERE (( T1.C3 , T1.C4 , T1.C5) INTERSECTION ( T2.C3 , T2.C4 , T2.C5 ) IS NOT NULL)

OR

Select t1.c1, t2.c1 from t1 INNER JOIN t2 ON ((T1.C2 <> T2.C2)
AND (( T1.C3 , T1.C4 , T1.C5) INTERSECTION ( T2.C3 , T2.C4 , T2.C5 ) IS NOT NULL))

In other words I'm looking for t1.c1 & t2.c1 pairs that differ on c2 but at least one of t1's c3,c4 or c5 is the same as at least one of c3, c4, c5 in t2. In real life C3, C4, C5 might be phone numbers and C2 account numbers. So which accounts have at least one matching phone number.

Ive tried case:

case when t1.c3 in (T2.C3 , T2.C4 , T2.C5 ) then t1.c3
when t1.c4 in (T2.C3 , T2.C4 , T2.C5 ) then t1.c4
when t1.c5 in (T2.C3 , T2.C4 , T2.C5 ) then t1.c5
else NULL
end

and then tested it for NULL in a where clause. It works but its very inefficient.

Is there a sexier Oracle SQL solution to what I'm sure is a common requirement?

Regards,
Jeff P-C
Gold Coast, Australia
Re: Set Operations on Columns (not Tables)? [message #420065 is a reply to message #420064] Wed, 26 August 2009 22:04 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
The way I would handle it:

First, get a complete list of C3, C4, and C5 from both tables. You can use this technique:

SELECT c1, c2
     , CASE n
       WHEN 1 THEN c3
       WHEN 2 THEN c4
       WHEN 3 THEN c5
       END AS cn
FROM (
    SELECT c1, c2, c3, c4, c5
    FROM   t1
    CROSS JOIN (SELECT level AS n FROM DUAL CONNECT BY level <= 3)
)


Create two such SQLs - one for T1 and one for T2 - then join them like this:

SELECT DISTINCT v1.c1, v2.c1
FROM (
    sql1
) v1
JOIN (
    sql2
) v2
ON v1.cn = v2.cn
AND v1.c2 <> v2.c2


Ross Leishman
Re: Set Operations on Columns (not Tables)? [message #420066 is a reply to message #420065] Wed, 26 August 2009 22:21 Go to previous messageGo to next message
jeffpc
Messages: 4
Registered: August 2009
Junior Member
Hi rleishman,

Many thanks for your quick reply.

Trying to convert guru speak into newbie speak:

I think what your saying is

(i) decompose each table into multiple rows for each original row with alternating values from c3, c4 and c5 as a new column:

i.e. every c1, c2, c3, c4 ,c5 row becomes

C1, C2, CN
c1, c2, c3
c1, c2, c4
c1, c2, c5

(ii) compare the two new decomposed results across the single generated column (and c2 as per my question).

I'll give it a whirl.

I am amazed that SQL doesn't handle this more elegantly though (e.g. more like my dream SQL statement).

Many thanks.
Re: Set Operations on Columns (not Tables)? [message #420087 is a reply to message #420066] Thu, 27 August 2009 00:26 Go to previous messageGo to next message
Michel Cadot
Messages: 64144
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I am amazed that SQL doesn't handle this more elegantly though (e.g. more like my dream SQL statement).

It is just because you don't understand what is Relational DB and so SQL.
If you need to do what you said then your model is not is the appropriate form and tables should be reviewed.

Regards
Michel
Re: Set Operations on Columns (not Tables)? [message #420095 is a reply to message #420087] Thu, 27 August 2009 00:44 Go to previous messageGo to next message
jeffpc
Messages: 4
Registered: August 2009
Junior Member
Hi Michel,

Interesting comment. To me Oracle is a tool to achieve business outcomes and not a mathematical model. Normal Form is ideal for some business situations but not others. Oracle has to work in the real world.

I believe you should save your judgements of someone's expertise based on a single (and in my view still valid) comment to yourself. For instance, I will not presume you are an arrogant person based on your otherwise unnecessary post.

I really appreciated Ross Leishman's help and indeed his solution is more computationally efficient than my original attempt.

Have a great day.

Regards,
Jeff P-C
Gold Coast, Australia
Re: Set Operations on Columns (not Tables)? [message #420099 is a reply to message #420095] Thu, 27 August 2009 01:03 Go to previous messageGo to next message
Michel Cadot
Messages: 64144
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
To me Oracle is a tool to achieve business outcomes and not a mathematical model.

This is wrong.
Oracle is first a mathematical model based on relational theory.
A screwdriver is designed to handle screws not nails. Do you blame a screwdriver sailor to not be able to drive a nail?

Quote:
Normal Form is ideal for some business situations but not others.

My grandfather always told me "the good worker as the good tools".

Quote:
Oracle has to work in the real world.

This is why it has introduced many extensions libe object-relational, analytical functions, model clause, domain carttridges and so on.

Quote:
I believe you should save your judgements of someone's expertise based on a single (and in my view still valid) comment to yourself. For instance, I will not presume you are an arrogant person based on your otherwise unnecessary post.

Don't take it for yourself and remember those that answers here has not necessarly english as their native language and so don't master the subtility of the language. Remember that way of speaking is different in different regions and what seems rude for some is not for others and the opposite. You have to read the answers in a fuzzy way (in the meaning of fuzzy set theory).

Regards
Michel

P.S.: by the way, I am an arrogant person.

[Updated on: Thu, 27 August 2009 01:07]

Report message to a moderator

Re: Set Operations on Columns (not Tables)? [message #420109 is a reply to message #420099] Thu, 27 August 2009 01:42 Go to previous message
jeffpc
Messages: 4
Registered: August 2009
Junior Member
Hi Michel,

This will be my last post on the matter. But:

Oracle is not a mathematical model, it is a business tool. We shall disagree here. As you yourself pointed out, Oracle has provided many facilities that are above and beyond that required for the pure implementation of relational theory.

I am not blaming any screwdriver for an inability to handle nails (in fact I was not blaming at all, I was merely querying whether there were further methods of achieving the outcome). Oracle provides many ways of achieving the same outcome. It would seem to me that in this world of datawarehouses, star schemas, commercial applications with data models over which most Oracle users have no influence, ETL, data mining etc etc, that set operations over columns (or some equivalent) would be a natural facility to provide users.

I have worked overseas in a number of cultures and took your cultural and linguistic differences into account. You have not provided any valuable contribution to this thread, but have made posts anyway (neither have I, since the first two). You cannot hide your intent behind the excuse of linguistic and cultural differences.

I hope you have found my feedback useful to help you liaise more effectively with others in the future.

Regards,
Jeff P-C
Previous Topic: delete conditional rows
Next Topic: Date
Goto Forum:
  


Current Time: Fri Dec 09 11:26:44 CST 2016

Total time taken to generate the page: 0.17954 seconds