Solved

SQL - combine 2 queries (UNION, EXCEPT, INTERSECT)

Posted on 2014-04-28
3
383 Views
Last Modified: 2014-04-29
Hello,

I have 2 queries.

One is very complicated (a lot subqueries, group bys, etc.). It outputs something like this:

name | age | pets
john | 28 | dog
lyla | 18 | cat
lyla | 18 | dog 

Open in new window


The second one outputs something like this:

name | age | pets
john | 28 | null
lyla | 18 | null
mike| 30 | null

Open in new window


I want to combine them and the result to be:

name | age | pets
john | 28 | dog
lyla | 18 | cat
lyla | 18 | dog
mike| 30 | null

Open in new window


Bear in mind It's just an example so it's not this simple and there is a reason they're 2 separate queries. How to join them this way? Can I do EXCEPT without the rows being identical (comparing just name and age)?

Thanks.
0
Comment
Question by:Carbonecz
3 Comments
 
LVL 45

Expert Comment

by:Kdo
Comment Utility
Hi Carbon,

If you simply want to combine all of the rows from both queries, use UNION ALL.  Use UNION if you want to eliminate the duplicates.

SELECT name, age, pets FROM Q1
UNION ALL
SELECT name, age, pets FROM Q2
ORDER BY age, pets;

Open in new window



Good Luck!
Kent
0
 
LVL 73

Accepted Solution

by:
sdstuber earned 500 total points
Comment Utility
union won't work with some of the pets being populated and some null for a given name/age combination.

unfortunately, you'll need to look for those either with a subquery that compares the results or using analytics to check for the groups


subquery version:

SELECT * FROM firstquery
UNION
SELECT *
  FROM secondquery
 WHERE pets IS NOT NULL OR (name, age) NOT IN (SELECT name, age FROM firstquery)
ORDER BY 1,2

Open in new window



analytics version:

  SELECT name, age, pets
    FROM (SELECT name,
                 age,
                 pets,
                 LAG(pets) OVER(PARTITION BY name, age ORDER BY pets NULLS LAST) prevpets
            FROM (SELECT * FROM firstquery
                  UNION
                  SELECT * FROM secondquery))
   WHERE pets IS NOT NULL OR (pets IS NULL AND prevpets IS NULL)
ORDER BY name, age

Open in new window



The analytics version is probably more efficient
0
 

Author Closing Comment

by:Carbonecz
Comment Utility
The analytics version works great. Thanks!
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now