Compatible query for outer join..

[Sameer]

SELECT e.ename, a.street_address, a.city,a.state,a.post_code
FROM emp e, addr a
WHERE e.empno = a.empno(+)
AND a.state = 'TEXAS'

Which is the compatible ANSI/ISO query for the above mentioned query (A or B)

A)

SELECT e.ename, a.street_address, a.city,a.state,a.post_code
FROM emp e RIGHT OUTER JOIN addr a
ON (e.empno = a.empno)
AND a.state = 'TEXAS'

B)

SELECT e.ename, a.street_address, a.city,a.state,a.post_code
FROM emp e LEFT OUTER JOIN addr a
ON (e.empno = a.empno)
AND a.state = 'TEXAS'


Thanks

Sameer

[AndyEPS]

Sameer

b - LEFT OUTER JOIN is the correct form of those two.

However SQL-92 provides a simpler syntax assuming the two columns have only the empno column in common -

SELECT e.ename, a.street_address, a.city,a.state,a.post_code
FROM emp e NATURAL LEFT OUTER JOIN addr a

Alternatively if other columns share names you could use: -

SELECT e.ename, a.street_address, a.city,a.state,a.post_code
FROM emp e LEFT OUTER JOIN addr a USING(empno)

Andy