CODEWITHSUNDEEP
sqloracle-database
MaMu
MaMu

Reputation: 1869

SQL Statement - optimisation

I have this statement:

SELECT *
  FROM sgtn
 WHERE sgtn_kun_id IN (SELECT DISTINCT kun_id
                         FROM sgtn, kun
                        WHERE kun.kun_e_mail IN (LONG LIST OF EMAILS)
                              AND sgtn_kun_id = kun_id)

       AND sgtn_strasse IN (SELECT sgtn.sgtn_strasse
                              FROM sgtn
                             WHERE sgtn_kun_id IN (SELECT DISTINCT kun_id
                                                     FROM sgtn, kun
                                                    WHERE kun.kun_e_mail IN (LONG LIST OF EMAILS)
                                                          AND sgtn_kun_id = kun_id)

                             GROUP BY sgtn.sgtn_strasse
                            HAVING COUNT(sgtn_strasse) > 2);

LONG LIST OF EMAILS is i.e.: '[email protected]', '[email protected]', . . . '[email protected]'

As you can see I repeat some part of subquerys in this query.

I'm wondering if and how can I replace LONG LIST OF EMAILS. It occurs in my statement twice. Would it be possible to edit this query, so that the mentioned LONG LIST OF EMAILS occurs once?

Upvotes: 0

Views: 68

Answers (3)

rs.
rs.

Reputation: 27427

You can try this:

with CTE as (
SELECT sgtn.*, count(sgtn_strasse) OVER (PARTITION BY sgtn_strasse) cnt
FROM sgtn
WHERE sgtn_kun_id IN (SELECT kun_id FROM kun 
                      WHERE kun.kun_e_mail IN (LONG LIST OF EMAILS))
)

SELECT * FROM  CTE WHERE CNT > 2

Upvotes: 1

Florin Ghita
Florin Ghita

Reputation: 17643

First, you don't need to use

IN (SELECT DISTINCT kun_id
    FROM sgtn, kun
    WHERE kun.kun_e_mail IN (LONG LIST OF EMAILS)
    AND sgtn_kun_id = kun_id)

it is enough

IN (SELECT kun_id
    FROM kun
    WHERE kun.kun_e_mail IN (LONG LIST OF EMAILS))

the rows having multiple sgtn_strasse and email in your list can be detected with

select * from (
  SELECT s.*, count(*) over (partition by sgtn_strasse) cnt_strasse
  FROM sgtn s
  WHERE sgtn_kun_id IN (SELECT kun_id
                       FROM kun
                       WHERE kun.kun_e_mail IN (LONG LIST OF EMAILS))
  )
WHERE cnt_strasse > 1;

Upvotes: 1

Codo
Codo

Reputation: 78855

Use a WITH clause:

WITH kun_list AS (
    SELECT DISTINCT kun_id
      FROM sgtn, kun
     WHERE kun.kun_e_mail IN (LONG LIST OF EMAILS)
           AND sgtn_kun_id = kun_id)

SELECT *
  FROM sgtn
 WHERE sgtn_kun_id IN (SELECT kun_id
                         FROM kun_list)

       AND sgtn_strasse IN (SELECT sgtn.sgtn_strasse
                              FROM sgtn
                             WHERE sgtn_kun_id IN (SELECT kun_id
                                                     FROM kun_list)

                             GROUP BY sgtn.sgtn_strasse
                            HAVING COUNT(sgtn_strasse) > 2);

Upvotes: 2

Related Questions