I need to select all data from Table_A for employees in Unit ABC123, but the Unit code is in Table_B. The common key is Emplid. I need to create an Oracle Export script to basically replace the following code:
SELECT A.* FROM TABLE_A A WHERE exists (SELECT B.EMPLID from TABLE_B B where A.EMPLID = B.EMPLID and B.UNIT = 'ABC123')
Is an Export script possible?
Sounds like what you're looking for is an inner join.
SELECT A.* FROM TABLE_A A INNER JOIN TABLE_B B ON B.EMPLID = A.EMPLID AND B.UNIT = 'ABC123'
The only problem you will have is if the relationship between TABLE_A and TABLE_B is one-to-many. Since you are using the dreaded, evil "select star"—presumably you did this only for convenience in posing the question, and would not do it in real life—you will find it difficult to use GROUP BY, because the GROUP BY would have to include every column being selected.
But there's an easier way. Push the GROUP BY down into a derived table subquery, so that TABLE_A joins to only one row derived from TABLE_B:
SELECT A.* FROM TABLE_A A INNER JOIN ( SELECT EMPLID FROM TABLE_B WHERE UNIT = 'ABC123' GROUP BY EMPLID ) B ON B.EMPLID = A.EMPLID
This eliminates the one-to-many problem in the outer query.
Dig Deeper on Oracle development languages
Related Q&A from Rudy Limeback
Read SQL expert Rudy Limeback's advice for counting combinations in a table with SQL's GROUP BY clause Continue Reading
Read about the Mimer Validator, a tool used to verify your SQL code, in this tip from SQL expert Rudy Limeback. Continue Reading
Read an example of an SQL case expression from our SQL expert Rudy Limeback. Continue Reading