Solved

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

Posted on 2014-04-28
3
390 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
ID: 40026961
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
ID: 40027063
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
ID: 40030845
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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

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…
Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

803 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