CODEWITHSUNDEEP
sqloracle-databasein-clause
DataQuery
DataQuery

Reputation: 1

SQL - appointing multiple where clauses to a single subquery

Is it possible to appoint multiple where clauses to a single subquery on an Oracle Database?

NAME AGE HAIR EYE GENDER
steve 8 blonde brown boy
john 9 blonde blue boy
emily 8 brown brown girl
josh 8 blonde brown boy

table name: PERSONAL

Lets say I just know the name josh and I want to know the ones that have the same attributes as john but instead of first learning the attributes of john and writing a second query to find out all the names. Since I might use this kind of a query very often, I do not want to change the 'NAME' for every where clause. I would rather change it ones and want it to be changed somehow automatically for the other clauses. To be more specific rather than writing the below query,

SELECT *
FROM PERSONAL
WHERE AGE IN(SELECT AGE
             FROM PERSONAL
             WHERE NAME = 'josh')
AND HAIR IN (SELECT HAIR
            FROM PERSONAL
            WHERE NAME = 'josh')
AND EYE IN (SELECT EYE
            FROM PERSONAL
            WHERE NAME = 'josh')
AND GENDER IN(SELECT GENDER
              FROM PERSONAL
              WHERE NAME = 'josh')

I am trying to find something like the below one,

SELECT *
FROM PERSONAL
WHERE AGE,HAIR,EYE,GENDER IN(SELECT AGE,HAIR,EYE,GENDER
                             FROM PERSONAL
                             WHERE NAME = 'josh')

Is it somehow possible to do so? I would appreciate any suggestions or different ways.

Thanks.

Upvotes: 0

Views: 46

Answers (2)

MT0
MT0

Reputation: 167972

You can do it by wrapping the column identifiers in parentheses:

WHERE (AGE, HAIR, EYE, GENDER) IN (SELECT AGE, HAIR, EYE, GENDER FROM ...)

But that will scan the table (or index) twice; once for the outer SELECT and once for the SELECT in the IN clause.

If you want to do it in a single table (or index) scan then you can use analytic functions to count the rows in each partition with the name josh and then filter out the unmatched rows using an inline view:

SELECT name, age, hair, eye, gender
FROM   (
  SELECT name, age, hair, eye, gender,
         COUNT(CASE name WHEN 'josh' THEN 1 END)
           OVER (PARTITION BY age, hair, eye, gender) AS num_matches
  FROM   personal
)
WHERE  num_matches > 0
AND    name != 'josh';

Or, from Oracle 12, can use MATCH_RECOGNIZE to find the partition that includes josh and return all the other rows (excluding the pattern that matched josh by wrapping it in {- -}):

SELECT name, age, hair, eye, gender
FROM   personal
       MATCH_RECOGNIZE(
         PARTITION BY age, hair, eye, gender
         ORDER BY name
         ALL ROWS PER MATCH
         PATTERN ( ^ other* {- name_match+ -} other* $ )
         DEFINE name_match AS name = 'josh'
       )

Which, for the sample data:

CREATE TABLE personal (NAME PRIMARY KEY, AGE, HAIR, EYE, GENDER) AS
SELECT 'steve', 8, 'blonde', 'brown', 'boy'  FROM DUAL UNION ALL
SELECT 'john',  9, 'blonde', 'blue',  'boy'  FROM DUAL UNION ALL
SELECT 'emily', 8, 'brown',  'brown', 'girl' FROM DUAL UNION ALL
SELECT 'josh',  8, 'blonde', 'brown', 'boy'  FROM DUAL;

Both output:

NAME AGE HAIR EYE GENDER
steve 8 blonde brown boy

fiddle

Upvotes: 0

Littlefoot
Littlefoot

Reputation: 142713

It is possible; just enclose the whole column list into parenthesis:

SELECT *
FROM PERSONAL
WHERE (AGE,HAIR,EYE,GENDER) IN (SELECT AGE,HAIR,EYE,GENDER
      ---------------------     FROM PERSONAL
      parenthesis here          WHERE NAME = 'josh')

Upvotes: 4

Related Questions