Solved

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

Posted on 2014-04-28
3
387 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

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

920 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

14 Experts available now in Live!

Get 1:1 Help Now