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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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).
Can you provide some sample data for the two tables and what your expected results should be?
Your query is right unless you do not want NULL employees/dept.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
Welcome!