View previous topic :: View next topic
|
Author |
Message |
subramsa
New User
Joined: 15 Apr 2005 Posts: 16 Location: Bangalore, India
|
|
|
|
Hi All,
This is regarding Not equal operator in INNER JOIN ...
There are two tables EMP and DEPT.
there is NO RI releation between these two tables EMP and DEPT
My requirement is to get list of all employees which are present in EMP table whose DEPT_CODE NOT in DEPT table
Select using Subquery :
select * from EMP
where EMP.DEPT_CODE NOT in ( select DEPT_CODE from DEPT )
But I want to re-write this query using Joins( want to avoid sub select).
I tried following query. but doesn't give the correct answer.
The following query gives Cartesian PRODUCT of Two Table
SELECT E.EMPNO, E.EMP_NAME, E.EMP_DEPT_CODE
FROM EMP E
INNER JOIN DEPT D
ON E.DEPT_CODE <> D.DEPT_CODE;
Is there any way to do using Joins ...
Many Thanks in advance
Best Regards
Sai
|
|
Back to top |
|
|
prabs2006
Active User
Joined: 12 Jan 2006 Posts: 103
|
|
|
|
Hi,
Wonder why its not working...Juz try this other form of join
SELECT E.EMPNO, E.EMP_NAME, E.EMP_DEPT_CODE
FROM EMP E, DEPT D
WHERE E.DEPT_CODE <> D.DEPT_CODE;
Thanks & Regards
Prabs |
|
Back to top |
|
|
pjnithin
Active User
Joined: 22 Dec 2005 Posts: 116
|
|
|
|
Prabs,
This will give you a wrong result. This will end up selecting all the rows from EMP table since for each employee there are chances of finding more than one non matching DEPT_CODE from DEPT table. While his actual requirement is to select only those employees from EMP table who doesn't have a matching DEPT_CODE in DEPT table. The sub query is the best method to select it. The above query will result in a cartesian product. |
|
Back to top |
|
|
subramsa
New User
Joined: 15 Apr 2005 Posts: 16 Location: Bangalore, India
|
|
|
|
Yep, even i think so. Sub query with NOT IN condition or NOT EXISTS could be correct solution.
I would like to know anyother better way of coding without using subquery. |
|
Back to top |
|
|
prabs2006
Active User
Joined: 12 Jan 2006 Posts: 103
|
|
|
|
Hi Friends,
You guys are right....
If EmpNo is present in Dept column, we can include the same in Where caluse...could this be fine?
SELECT E.EMPNO, E.EMP_NAME, E.EMP_DEPT_CODE
FROM EMP E, DEPT D
WHERE E.DEPT_CODE <> D.DEPT_CODE AND
E.EMPNO = D.EMPNO;
Note: I dont have the luxury of checking these qrys.These are just my suggestion, so please bear with me if I go wrong
Thnaks & Regards
Prabs |
|
Back to top |
|
|
natan66
New User
Joined: 16 Sep 2005 Posts: 5
|
|
|
|
hi,
i think the best way to do this is with full join.
SELECT E.EMPNO, E.EMP_NAME, E.EMP_DEPT_CODE
FROM EMP E full join DEPT D on
E.DEPT_CODE = D.DEPT_CODE
WHERE D.DEPT_CODE is null or E.DEPT_CODE is null
i also don't have the privilege to check this code.
i hope i helped. |
|
Back to top |
|
|
DavidatK
Active Member
Joined: 22 Nov 2005 Posts: 700 Location: Troy, Michigan USA
|
|
|
|
Sai,
What is your objection to using sub queries? They can be very efficient if used correctly.
When you use the ?IN? processing, for every row in the main query DB2 must do a select of every row of the result table from the sub query or until a match is made. For ?NOT IN? DB2 must query every row in the result table. This can be very resource intensive if the result table is very large. Also, if the sub query references values in the main query, this will now be very inefficient because DB2 will have to rebuild the result table for every main query row.
Using the ?EXISTS? or ?NOT EXISTS? can be very efficient if the columns you are comparing are indexed. You target on a single row, and either it EXISTS, or it does NOT EXISTS. Now, if the columns you are comparing are not indexed, the ?EXISTS? and ?NOT EXISTS? will have to query rows as with the ?IN? and ?NOT IN?.
Which is going to be more efficient? Only you will know. If the columns are indexed, the ?EXISTS? will be efficient. If the columns are not indexed and the result table is small, the ?IN? will be efficient, Otherwise both will be inefficient.
One way you can make this more efficient sometimes is to utilize a ?GLOBAL TEMPORARY TABLE? in your processing. if your sub query table is very large and not indexed, and your result table is moderate to large in size, you can use the populate the Temporary table with the result table, index it and then use ?EXISTS? processing.
Dave |
|
Back to top |
|
|
|