CODEWITHSUNDEEP
sqloracle-databaseoracle-sqldeveloper
uğur taş
uğur taş

Reputation: 365

Use one query instead of two queries

I have a query like the sample. There is a duplication in query, I want to minimize it. How can I do that? Sorry if it is a very simple question. I am a newbee in the fields of database and SQL.

SELECT * 
FROM Table1 
WHERE column1 IN (SELECT DISTINCT column1 
                  FROM Table2 
                  WHERE column2 = (SELECT column2 
                                   FROM Table4 
                                   WHERE column3='1324654') 
                  UNION 
                  SELECT DISTINCT column1 
                  FROM Table3 
                  WHERE column2 = (SELECT column2 
                                   FROM Table4 
                                   WHERE column3='1324654'));

Duplication in the subquery: 

SELECT column2 
FROM Table4 
WHERE column3='1324654'

Upvotes: 0

Views: 73

Answers (4)

przemo_pl
przemo_pl

Reputation: 87

You can also use the with clause:

select *
  from table1
 where column1 in (with tab_helper as 
                   (select column2
                      from table4
                     where column3 = '1324654') 
                     select distinct column1
                       from table2,
                             tab_helper
                      where column2 = tab_helper.column2 
                      union 
                      select distinct column1 
                       from table3,
                            tab_helper 
                     where column2 = tab_helper.column2);

Upvotes: 0

Sudipta Mondal
Sudipta Mondal

Reputation: 2572

If you just want to remove duplication

SELECT *
FROM table1
WHERE column1 IN
    ( WITH data AS
    (SELECT column2
    FROM table4
    WHERE column3 = '12324654'
    )
SELECT distint column1
FROM table2,
    data
WHERE table2.column2 = data.column2
UNION
SELECT distinct column1
FROM table3,
    data
WHERE table4.column2 = data.column2
    );

Upvotes: 1

Gerardo Lima
Gerardo Lima

Reputation: 6703

@ugur, you can use PL/SQL and create a variable, as @st suggested, but, if you're concerned about performance you might as well just use SQL-only and leave like it is now: the repeated subquery is deterministic and will most probably be cached, causing no performance penalty.

Upvotes: 1

st mnmn
st mnmn

Reputation: 3667

You can declare a variable which will keep the result of the duplicate query. like this:

declare @result varchar=(SELECT column2 FROM Table4 WHERE column3='1324654')

and then use it in the query:

SELECT * FROM Table1 WHERE column1 IN 
(SELECT DISTINCT column1 FROM Table2 WHERE 
column2=@result 
UNION 
SELECT DISTINCT column1 FROM Table3 WHERE 
column2=@result);

Upvotes: 0

Related Questions