Is this a right query? If it is what does it mean

I was given a query to explain. Could someone please explain it to me:

select j.ip_num from 
jobs j, address a
where j.jobtype='C' and
a.sel_code(+)='H' and 
j.ip_num=a.ip_num and
a.ip_num is null order by a.ip_n开发者_运维知识库um

That query selects every JOB.IP_NUM which doesn't have a matching ADDRESS record or where the matching ADDRESS record has a SEL_CODE not equal to 'H'.

The (+) is Oracle's old outer join syntax. It is the only OUTER JOIN syntax supported in versions of Oracle before 9i.

In this query we get one row for every row in EMP which matches a department, plus a row for the DEPTNO=40, which has no employees:

SQL> select d.dname
  2         , e.ename
  3  from dept d
  4       , emp e
  5  where d.deptno = e.deptno(+)
  6  /

DNAME          ENAME
-------------- ----------
ACCOUNTING     SCHNEIDER
ACCOUNTING     BOEHMER
ACCOUNTING     KISHORE
RESEARCH       ROBERTSON
RESEARCH       KULASH
RESEARCH       GASPAROTTO
RESEARCH       RIGBY
RESEARCH       CLARKE
SALES          HALL
SALES          CAVE
SALES          SPENCER
SALES          BILLINGTON
SALES          PADFIELD
SALES          VAN WIJK
SALES          KESTELYN
SALES          LIRA
OPERATIONS     PSMITH
HOUSEKEEPING   VERREYNNE
HOUSEKEEPING   FEUERSTEIN
HOUSEKEEPING   PODER
HOUSEKEEPING   TRICHLER
COMMUNICATIONS

22 rows selected.

SQL> 

Now, if we put an additional filter on the EMP table like this, we simply get one record for each Department, because only one record in EMP now matches:

SQL> select d.dname
  2         , e.ename
  3  from dept d
  4       , emp e
  5  where d.deptno = e.deptno(+)
  6  and e.ename(+) = 'CAVE'
  7  /

DNAME          ENAME
-------------- ----------
ACCOUNTING
RESEARCH
SALES          CAVE
OPERATIONS
HOUSEKEEPING
COMMUNICATIONS

6 rows selected.

SQL> 
/

To convert this query into the ANSI SQL syntax we have to do this:

SQL> select d.dname
  2         , e.ename
  3  from dept d
  4       left outer join emp e
  5          on ( d.deptno = e.deptno
  6               and e.ename = 'CAVE' )
  7  /

DNAME          ENAME
-------------- ----------
ACCOUNTING
RESEARCH
SALES          CAVE
OPERATIONS
HOUSEKEEPING
COMMUNICATIONS

6 rows selected.

SQL>

Note that if we don't include the additonal clause in the JOIN but leave it in the WHERE clause we get a different result:

SQL> select d.dname
  2         , e.ename
  3  from dept d
  4       left outer join emp e
  5          on ( d.deptno = e.deptno )
  6  where  e.ename = 'CAVE'
  7  /

DNAME          ENAME
-------------- ----------
SALES          CAVE

SQL>

This is the equivalent of omitting the (+) in the second old skool query.

The query is joining the 2 tables jobs, and address. These tables are joining on the field ip_num but you are looking for the records that exist in the jobs table but do not exist in the address table.

This is a LEFT OUTER JOIN. This query could also be written

SELECT j.ip_num 
FROM jobs j
LEFT OUTER JOIN address a
    ON j.ip_num=a.ip_num
WHERE j.jobtype='C' AND
    a.sel_code(+)='H' AND 
    a.ip_num is null 
ORDER BY a.ip_num

It might be useful to see a visual picture joins http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-joins.html