CODEWITHSUNDEEP
oracle-databasejoin
Joe88
Joe88

Reputation: 441

Oracle query by column1 where column2 is the same

I have a table like this in Oracle 9i DB:

+------+------+
| Col1 | Col2 |
+------+------+
|    1 | a    |
|    2 | a    |
|    3 | a    |
|    4 | b    |
|    5 | b    |
+------+------+

Col1 is the primary key, Col2 is indexed. I input col1 as condition for my query and I want to get col1 where col2 is the same as my input. For example I query for 1 and the result should be 1,2,3. I know I can use self join for this, I would like to know if there is a better way to do this.

Upvotes: 0

Views: 458

Answers (2)

onedaywhen
onedaywhen

Reputation: 57023

I'd call this a semi-join: does it satisfy your 'no self joins' requirement?:

  SELECT * 
    FROM YourTable
   WHERE Col2 IN ( SELECT t2.Col2
                     FROM YourTable t2
                    WHERE t2.Col1 = 1 );

I'd be inclined to avoid the t2 range variable like this:

  WITH YourTableSearched
       AS ( SELECT Col2
              FROM YourTable
             WHERE Col1 = 1 )
    SELECT * 
      FROM YourTable
     WHERE Col2 IN ( SELECT Col2
                       FROM YourTableSearched );

but TNH I would probably do this:

  WITH YourTableSearched
       AS ( SELECT Col2
              FROM YourTable
             WHERE Col1 = 1 )
    SELECT *
      FROM YourTable
           NATURAL JOIN YourTableSearched;

Upvotes: 2

Boneist
Boneist

Reputation: 23578

It's possible. Whether it's better (i.e. more performant) than using a self-join, particularly if there is an index on col1, col2, is anyone's guess.

Assuming col1 is unique, you could do:

SELECT col1
FROM   (SELECT col1,
               col2,
               MAX(CASE WHEN col1 = :p_col1_value THEN col2 END) OVER () col2_comparison
        FROM   your_table)
WHERE col2 = col2_comparison;

And with :p_col1_value = 1:

      COL1
----------
         1
         2
         3

And with :p_col1_value = 5:

      COL1
----------
         4
         5

Upvotes: 0

Related Questions