Solved

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

Posted on 2014-04-28
3
391 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:Kent Olsen
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 74

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

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

809 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