Avatar of holemania
holemania asked on

SQL Query - Full Join

I have a SQL query that joins another select statement.  If the main query is null, I still want to pull from the nested query.  Is this even possible or would I need to create 2 temp table and do that?

Example:

SELECT E.ID, E.NAME, E.DEPT_ID, A.DEPT_DESC, E.PAYRATE FROM EMPLOYEE E
FULL OUTER JOIN
(SELECT D.DEPT_ID, D.DEPT_DESC FROM DEPT D) A ON A.DEPT_ID = E.DEPT_ID

Output:

2912      Joe Smuack      122     MIS     $22.00
NULL     NULL                NULL   FIN     NULL
SQL

Avatar of undefined
Last Comment
Hamed Nasr

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Argenti

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Qlemo

The "nested query" (which is aka as ad-hoc view or derived table) isn't necessary here, and it is much better to flatten the join as shown by Argenti. And indeed, if you need rows even if the join cannot find a match, you always use an outer join (left, right or full, depending on the situation).
awking00

Can you provide some sample data for the two tables and what your expected results should be?
sameer2010

Your query is right unless you do not want NULL employees/dept.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
SOLUTION
Hamed Nasr

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
holemania

Sorry was just making the query as simple as can be, but it gets pretty complex so that's why I did a nested query since the 2nd query consists of joining a few other tables.

Was able to achieve the request by putting both queries into 2 temp tables and then doing a full outer join with the tables.

Reason why I want to pull items that doesn't match the other temp table (nested query) is so that we can see departments that haven't been used.  Instead of generating 2 separate report, users was asking if we can incorporate both into 1 report.

Thanks for all the suggestions.
Hamed Nasr

Welcome!