Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
Not equal operator in INNER JOIN

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
subramsa

New User


Joined: 15 Apr 2005
Posts: 16
Location: Bangalore, India

PostPosted: Fri Jan 20, 2006 1:03 pm    Post subject: Not equal operator in INNER JOIN
Reply with quote

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
icon_smile.gif
Back to top
View user's profile Send private message

prabs2006

Active User


Joined: 12 Jan 2006
Posts: 103

PostPosted: Fri Jan 20, 2006 3:04 pm    Post subject:
Reply with quote

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
View user's profile Send private message
pjnithin

Active User


Joined: 22 Dec 2005
Posts: 116

PostPosted: Fri Jan 20, 2006 11:58 pm    Post subject:
Reply with quote

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
View user's profile Send private message
subramsa

New User


Joined: 15 Apr 2005
Posts: 16
Location: Bangalore, India

PostPosted: Mon Jan 23, 2006 9:27 am    Post subject:
Reply with quote

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
View user's profile Send private message
prabs2006

Active User


Joined: 12 Jan 2006
Posts: 103

PostPosted: Mon Jan 23, 2006 10:10 am    Post subject:
Reply with quote

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
View user's profile Send private message
natan66

New User


Joined: 16 Sep 2005
Posts: 5

PostPosted: Thu Aug 03, 2006 11:21 pm    Post subject: Re: Not equal operator in INNER JOIN
Reply with quote

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
View user's profile Send private message
DavidatK

Active Member


Joined: 22 Nov 2005
Posts: 700
Location: Troy, Michigan USA

PostPosted: Fri Aug 04, 2006 4:04 am    Post subject: Re: Not equal operator in INNER JOIN
Reply with quote

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
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Join records from 2 files with No Dup... Poha Eater DFSORT/ICETOOL 22 Sun Aug 27, 2017 10:35 pm
No new posts Reduce CPU Times for Join Sort santoshn SYNCSORT 12 Sat Jun 10, 2017 1:40 pm
No new posts Join giving more records than expected Danielle.Filteau SYNCSORT 2 Thu Mar 09, 2017 3:28 am
No new posts Join of more than one table dibyendumandal11111 DB2 6 Mon Feb 13, 2017 11:36 pm
No new posts Join in SQL Query vickey_dw DB2 2 Tue Jan 17, 2017 12:15 am

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us