• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 412
  • Last Modified:

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

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
Carbonecz
Asked:
Carbonecz
1 Solution
 
Kent OlsenData Warehouse Architect / DBACommented:
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
 
sdstuberCommented:
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
 
CarboneczAuthor Commented:
The analytics version works great. Thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now