Oracle SQL Non-Equijoin

in Oracle Database; Please give me the examples for non-equijoin using IN, LIKE operator and also their usage. I tried to get examples ...
+ Post Reply + Post New Topic
Results 1 to 5 of 5
  1. #1
    ygsunilkumar is offline Junior Member
    Points: 530, Level: 10
    Join Date
    16 Mar 2009
    Posts
    15
    Points
    530

    Oracle SQL Non-Equijoin

    Please give me the examples for non-equijoin using IN, LIKE operator and also their usage. I tried to get examples from google but ultimately I didnot get any examples. Thanks


  2. #2
    rajavu's Avatar
    rajavu is offline Forum Guru
    Points: 10,830, Level: 68
    Join Date
    13 Oct 2008
    Location
    @ Bangalore , India
    Posts
    815
    Points
    10,830

    Re: Oracle SQL Non-Equijoin

    Please give me the examples for non-equijoin using IN, LIKE operator and also their usage. I tried to get examples from google but ultimately I didnot get any examples. Thanks
    It Might be because such situations are rare.
    Any way following are some examples.

    Code sql:
    SQL> SELECT e.empno, e.ename, e.job FROM EMP e;
     
         EMPNO ENAME      JOB
    ---------- ---------- ---------
          7369 SMITH      CLERK
          7499 ALLEN      SALESMAN
          7521 WARD       SALESMAN
          7566 JONES      MANAGER
          7654 MARTIN     SALESMAN
          7698 BLAKE      MANAGER
          7782 CLARK      MANAGER
          7788 SCOTT      ANALYST
          7839 KING       PRESIDENT
          7844 TURNER     SALESMAN
          7876 ADAMS      CLERK
          7900 JAMES      CLERK
          7902 FORD       ANALYST
          7934 MILLER     CLERK
     
    14 ROWS selected.
     
    SQL> SELECT j.job_id, j.job FROM JOB_MASTER j;
     
    JOB_I JOB
    ----- ------------------------------
    RECEP RECEPTIONIST
    SALES SALESMAN
    ANALY ANALYST
    CLERK CLERK
    PRESI PRESIDENT
    PROGR PROGRAMMEE
     
    6 ROWS selected.
     
    SQL> SELECT e.empno, e.ename, j.job_id, J.JOB
      2 FROM emp e INNER JOIN JOB_MASTER j
      3 ON  e.JOB LIKE  j.job_ID||'%' ;
     
         EMPNO ENAME      JOB_I JOB
    ---------- ---------- ----- ----------------------------
          7499 ALLEN      SALES SALESMAN
          7521 WARD       SALES SALESMAN
          7654 MARTIN     SALES SALESMAN
          7844 TURNER     SALES SALESMAN
          7788 SCOTT      ANALY ANALYST
          7902 FORD       ANALY ANALYST
          7369 SMITH      CLERK CLERK
          7876 ADAMS      CLERK CLERK
          7900 JAMES      CLERK CLERK
          7934 MILLER     CLERK CLERK
          7839 KING       PRESI PRESIDENT
     
    11 ROWS selected.
     
    SQL>
    SQL> SELECT e.ename,e.ename,  J.JOB
      2 FROM emp e INNER JOIN JOB_MASTER j
      3 ON  e.JOB IN ( J.JOB);
     
    ENAME      ENAME      JOB
    ---------- ---------- ------------------------------
    SMITH      SMITH      CLERK
    ALLEN      ALLEN      SALESMAN
    WARD       WARD       SALESMAN
    MARTIN     MARTIN     SALESMAN
    SCOTT      SCOTT      ANALYST
    KING       KING       PRESIDENT
    TURNER     TURNER     SALESMAN
    ADAMS      ADAMS      CLERK
    JAMES      JAMES      CLERK
    FORD       FORD       ANALYST
    MILLER     MILLER     CLERK
     
    11 ROWS selected.
     
    SQL>

    Raj.

  3. #3
    ygsunilkumar is offline Junior Member
    Points: 530, Level: 10
    Join Date
    16 Mar 2009
    Posts
    15
    Points
    530

    Smile Re: Oracle SQL Non-Equijoin

    Thanks Raj for those examples. I understood the purpose and usage of LIKE operators in Non-Equijoin but for IN operator I couldnot understand, so please give me some other examples for Non-Equijoins using IN operator and also usage of it.

  4. #4
    rajavu's Avatar
    rajavu is offline Forum Guru
    Points: 10,830, Level: 68
    Join Date
    13 Oct 2008
    Location
    @ Bangalore , India
    Posts
    815
    Points
    10,830

    Re: Oracle SQL Non-Equijoin

    I don't have access to DB now. But the example already pasted is simple isn't it ?

    Other wise if job_master is having fieilds like JOB1, JOB2 then ,

    One such example would be

    Code sql:
    SELECT e.ename,e.ename,  J.JOB
    FROM emp e INNER JOIN JOB_MASTER j
    ON  e.JOB IN ( J.JOB1, J.JOB2, J.JOB3);

    I hope you got the example well.

    Raj.

  5. #5
    rajavu's Avatar
    rajavu is offline Forum Guru
    Points: 10,830, Level: 68
    Join Date
    13 Oct 2008
    Location
    @ Bangalore , India
    Posts
    815
    Points
    10,830

    Re: Oracle SQL Non-Equijoin

    I got connected just now .

    Here is the example I was explaining.

    Code sql:
    SQL> SELECT e.empno, e.ename, e.job FROM EMP e;
     
         EMPNO ENAME      JOB
    ---------- ---------- ---------
          7369 SMITH      CLERK
          7499 ALLEN      SALESMAN
          7521 WARD       SALESMAN
          7566 JONES      MANAGER
          7654 MARTIN     SALESMAN
          7698 BLAKE      MANAGER
          7782 CLARK      MANAGER
          7788 SCOTT      ANALYST
          7839 KING       PRESIDENT
          7844 TURNER     SALESMAN
          7876 ADAMS      CLERK
          7900 JAMES      CLERK
          7902 FORD       ANALYST
          7934 MILLER     CLERK
     
    14 ROWS selected.
     
    SQL> SELECT j.job_category,j.job_1,j.job_2,j.job_3 FROM JOB_CATEGORY_MASTER j;
     
    JOB_CATEGO JOB_1           JOB_2           JOB_3
    ---------- --------------- --------------- ---------------
    CAT1       RECEPTIONIST    SALESMAN        CLERK
    CAT2       ANALYST         MANAGER         PROGRAMMER
    CAT3       PRESIDENT       CEO             CHAIRMAN
     
    SQL> SELECT e.ename,e.ename,  e.JOB, j.JOB_CATEGORY
      2 FROM emp e INNER JOIN JOB_CATEGORY_MASTER j
      3 ON  e.JOB IN ( J.JOB_1,J.JOB_2,J.JOB_3);
     
    ENAME      ENAME      JOB       JOB_CATEGO
    ---------- ---------- --------- ----------
    SMITH      SMITH      CLERK     CAT1
    ALLEN      ALLEN      SALESMAN  CAT1
    WARD       WARD       SALESMAN  CAT1
    MARTIN     MARTIN     SALESMAN  CAT1
    TURNER     TURNER     SALESMAN  CAT1
    ADAMS      ADAMS      CLERK     CAT1
    JAMES      JAMES      CLERK     CAT1
    MILLER     MILLER     CLERK     CAT1
    JONES      JONES      MANAGER   CAT2
    BLAKE      BLAKE      MANAGER   CAT2
    CLARK      CLARK      MANAGER   CAT2
    SCOTT      SCOTT      ANALYST   CAT2
    FORD       FORD       ANALYST   CAT2
    KING       KING       PRESIDENT CAT3
     
    14 ROWS selected.
     
    SQL>

    Raj.