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
Last Comment
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
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
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
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!
SQL
SQL (Structured Query Language) is designed to be used in conjunction with relational database products as of a means of working with sets of data. SQL consists of data definition, data manipulation, and procedural elements. Its scope includes data insert, query, update and delete, schema creation and modification, and data access control.
61K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
