CODEWITHSUNDEEP
sqlsql-server
BlackCat
BlackCat

Reputation: 2044

How does this query work?

Table Name:

+-------+
| Name  |
+-------+
|  red  |
|  black|
| white |
| green |
| orange|
+--------+

Output will return the pair (where permutation wont be allowed, e.g. black,red and red,black)

My SQL Server query:

select  
    A, B 
from
    (select 
         A.colorname A, B.colorname B 
     from 
         colors A  
     join 
         colors B on A.colorname <> B.colorname
                  and A.colorname < B.colorname) C

My question is: how is this query working?

I am not getting the way A.colorname < B.colorname working here...and the thing is, removing A.colorname<>B.colorname is returning the same result.

Upvotes: 2

Views: 79

Answers (2)

ydoow
ydoow

Reputation: 3006

The values in your table are all different and can always be sorted alphabetically.

By joining the table with < comparison, it would always give your resulted rows by joining the row on left, to the rows which has a 'greater' value on right.

+-------+   +-------+
| Name  |   | Name  |   
+-------+   +-------+
| red   |   | red   |
+-------+   +-------+
| black |   | black |
+-------+   +-------+
| white |   | white |
+-------+   +-------+
| green |   | green |
+-------+   +-------+
| orange|   | orange|
+-------+   +-------+

Starting from first row on LEFT table, result from JOIN is 

red - white

Then on the second row, result from JOIN is 

black - red
black - white
black - green
black - orange

On the third row, as there's nothing greater than white that would yield empty result.

The same logic you can work out the remaining results.

As a result, it will show all pairs without permutation.

Note A.colorname<>B.colorname is not necessary as it's just a subset of A.colorname < B.colorname.

Upvotes: 1

Akshey Bhat
Akshey Bhat

Reputation: 8545

A.colorname < B.colorname gives you result in an alphabetical order. A permutation of two colors is picked only once. For e.g. black and red has two permutations:

  1. black,red
  2. red,black.

Only the first one is picked because name of colors is in alphabetical order.

Upvotes: 1

Related Questions