Tale of <> Operator

Question

Post reply

Tale of <> Operator

Viewing 6 posts - 16 through 20 (of 20 total)

You must be logged in to reply to this topic. Login to reply

karthik M One Orange Chip Points: 29139 More actions · Answer 1

Jeff,

It gives us every row in the first table, joined with every row in the second table.

say for example,we have two tables called TableA and TableB.

Table A

A

B

Table B

C

D

Cartesian Product Output :

A C

A D

B C

B D

am i correct ? If not ,Please let me know the correct answer. If yes ,Please give me some more examples and definitions.

karthik

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

You're correct on what a cartesian product is.

Jeff was asking how an OR used in a where clause will get you a cartesian product.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

karthik M One Orange Chip Points: 29139 More actions · Answer 3

it must look at every row in one of the tables for each row in the other table.

for example,

Table A:

----------

sno eno

----------

A 1

B 2

Table B:

----------

sno eno

----------

A 1

D 2

Query :

--------------------------

select a.sno,a.eno,b.sno,b.eno

from TableA A,TableB B

where A.sno = B.sno

or A.eno = B.eno

---------------------------

Well, First condition will check the sno column,so the internal output is

A 1 , A 1 Then second conditions will check eno column, the internal output is

A 1 ,B 2,D 2. As per 'OR' logic i.e 0+1 = 1 , 1+0 = 1 ,1+1 = 1 if any condition satisfied the output will be displayed.

So the final output is

A 1 A 1

B 2 D 2

JAm i correct ? Jeff and Gila , If i am wrong kindly give me the proper answer with definition.

karthik

karthik M One Orange Chip Points: 29139 More actions · Answer 4

Am i correct ? Jeff and Gila , If i am wrong kindly give me the proper answer with definition

karthik

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 5

Although the "OR" in your example allows each value to be matched and displayed, it does not form a Cartesian product...

DECLARE @TableA TABLE (SNo CHAR(1),ENo INT)

INSERT INTO @TableA (SNo,ENo)

SELECT 'A',1 UNION ALL

SELECT 'B',2 UNION ALL

SELECT 'C',3

DECLARE @TableB TABLE (SNo CHAR(1),ENo INT)

INSERT INTO @TableB (SNo,ENo)

SELECT 'A',1 UNION ALL

SELECT 'D',2 UNION ALL

SELECT 'C',3

SELECT a.SNo,a.ENo,b.SNo,b.ENo

FROM @TableA a,@TableB b

WHERE a.SNo = b.SNo

OR a.ENo = b.ENo

sno eno sno eno

---- ----------- ---- -----------

A 1 A 1

B 2 D 2

C 3 C 3

(3 row(s) affected)

The following, however, is a Cartesian product...

SELECT a.SNo,a.ENo,b.SNo,b.ENo

FROM @TableA a, @TableB b

SNo ENo SNo ENo

---- ----------- ---- -----------

A 1 A 1

B 2 A 1

C 3 A 1

A 1 D 2

B 2 D 2

C 3 D 2

A 1 C 3

B 2 C 3

C 3 C 3

(9 row(s) affected)

Cartesian products are caused by some form of Cross-Join (accidental or explicit) and the basic definition of that is "for every row in [A], return all rows in ". Size of result set works out to be (A*B)...

Next worse thing after a Cartesian product, is a "Triangular Join" which you can read about at... http://www.sqlservercentral.com/Forums/Topic359124-338-1.aspx#bm360151

However, I get your point... if the DATA is such, an "OR" can begin to form some "duplicates" that some may mistakenly call a Cartesian join...

DECLARE @TableA TABLE (SNo CHAR(1),ENo INT)

INSERT INTO @TableA (SNo,ENo)

SELECT 'A',1 UNION ALL

SELECT 'B',2 UNION ALL

SELECT 'C',2

DECLARE @TableB TABLE (SNo CHAR(1),ENo INT)

INSERT INTO @TableB (SNo,ENo)

SELECT 'A',1 UNION ALL

SELECT 'D',2 UNION ALL

SELECT 'C',2

SELECT a.SNo,a.ENo,b.SNo,b.ENo

FROM @TableA a,@TableB b

WHERE a.SNo = b.SNo

OR a.ENo = b.ENo

SNo ENo SNo ENo

---- ----------- ---- -----------

A 1 A 1

B 2 D 2

C 2 D 2

B 2 C 2

C 2 C 2

(5 row(s) affected)

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

karthik M One Orange Chip Points: 29139 More actions · Answer 6

Thanks Jeff ! Really i am feeling dame good real time experience from your answers.

karthik