Home SQL improvement - UNION?
Reply: 0

SQL improvement - UNION?

user11069
1#
user11069 Published in September 19, 2018, 7:20 pm

I have 2 tables: "packages" and "items".

"packages" has the following columns: pack_id | item_id

"items" has the following columns.......: item_id | type

One package can have multiple items.

UPDATE:

Sample Data and Expected Results:

pack_id | item_id
1           1
1           2
1           3
2           2
2           1
3           4
3           5
3           6

item_id | type
1           C
2           F
3           Z
4           Z
5           Z
6           Z

Expected result:

pack_id | pack_type
1           Mixed
2           Cool
3           Not Cool

My question is:

Is there a better way to query these 2 tables, in order to get a 3rd one, having as columns: "pack_id" and the new column "pack_type"? Those UNIONs seem to add a huge amount of time to the run time.

    SELECT pack_id, pack_type
    FROM (    
      -- CASE 1 - pack_type is 'Cool' - type 'C' or 'F', but not 'Z'        
      SELECT  Distinct q1.pack_id, 'Cool' AS pack_type    
        FROM (
            SELECT  Distinct p.pack_id, i.type
            FROM packages p 
            INNER JOIN items i
                ON p.item_id = i.item_id
            WHERE i.type <> 'Z') q1
        WHERE q1.type IN ('C','F')

      UNION        

      -- CASE 2 - pack_type is 'Not-Cool' - type 'Z', but not 'C' or 'F'
      SELECT  Distinct q2.pack_id, 'Not-Cool' AS pack_type    
        FROM (
            SELECT  Distinct p.pack_id, i.type
            FROM packages p 
            INNER JOIN items i
                ON p.item_id = i.item_id
            WHERE i.type = 'Z') q2
        WHERE q2.type NOT IN ('C','F')

      UNION

     -- CASE 3 - pack_type is 'Mixed' - type 'Z' and ('C' or 'F')
     SELECT  Distinct q3.pack_id, 'Mixed' AS pack_type    
        FROM (
            SELECT  Distinct p.pack_id, i.type
            FROM packages p 
            INNER JOIN items i
                ON p.item_id = i.item_id
            WHERE i.type = 'Z') q3
        WHERE q3.type IN ('C','F')
    ) m

NOTE: some explication for the multiple comments that point to "A type cannot be Z and also C or F".

I also thought that, but the way I understand the behavior of the query is that if u say "type IN ('C','F')", it will look at each item of type 'C' or 'F' and get the package id that has that item. BUT, a package that has items of type 'C' or 'F' can also have items of type 'Z', hence the inner select, dealing with items of type 'Z' first.

share|improve this question
  • 2
    UNION is a bit slower than UNION ALL because it has to check for duplicates. But you should be able to do this entire thing in a single query with some case expressions. – Sean Lange Nov 14 '17 at 14:33
  • @Sean.. I was just typing that to suggest CASE based on the Type. – WEI_DBA Nov 14 '17 at 14:33
  • You can also store a procedure that puts results of selects into temp table. In the end return that tamp table ;) – DanteTheSmith Nov 14 '17 at 14:35
  • 1
    Maybe I'm missing something in the original query, but how can q1.type in the 2nd and 3rd query reference the first query? – WEI_DBA Nov 14 '17 at 14:50
  • 1
    @WEI_DBA should be q2 and q3 – Juan Carlos Oropeza Nov 14 '17 at 14:52

3 Answers 3

active oldest votes
up vote 1 down vote accepted
You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.309903 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO