SQL

From SkyWiki

(Difference between revisions)
Jump to: navigation, search
(ANSI joins in Oracle)
Current revision (16:18, 3 October 2008) (view source)
m (ANSI joins in Oracle)
 
Line 21: Line 21:
from emp
from emp
right outer join dept on emp.deptno = dept.deptno
right outer join dept on emp.deptno = dept.deptno
 +
</source>
 +
 +
full join
 +
<source lang=sql>
 +
select ...
 +
from emp
 +
full join dept on emp.deptno = dept.deptno
 +
</source>
 +
 +
cross join
 +
<source lang=sql>
 +
select ...
 +
from emp
 +
cross join dept
</source>
</source>

Current revision

ANSI joins in Oracle

Presentation about the ANSI syntax introduced in Oracle 9i

inner join

SELECT ...
FROM emp
JOIN dept ON emp.deptno = dept.deptno

left outer join

SELECT ...
FROM emp
LEFT OUTER JOIN dept ON emp.deptno = dept.deptno

right outer join

SELECT ...
FROM emp
RIGHT OUTER JOIN dept ON emp.deptno = dept.deptno

full join

SELECT ...
FROM emp
FULL JOIN dept ON emp.deptno = dept.deptno

cross join

SELECT ...
FROM emp
CROSS JOIN dept

traditional Oracle outer join syntax

inner join

SELECT ...
FROM emp, dept
WHERE emp.deptno = dept.deptno

left outer join

SELECT ...
FROM emp, dept
WHERE emp.deptno(+) = dept.deptno

right outer join

SELECT ...
FROM emp, dept
WHERE emp.deptno = dept.deptno(+)

TOP n SELECT

MySQL:

SELECT EMPNO, ENAME, JOB, MGR, SQL
FROM   EMP
LIMIT 10 offset 20

Oracle >= 8.1.6:

SELECT * FROM (SELECT 
    EMPNO, ENAME, JOB, MGR, SQL,
    rank() over(partition BY JOB ORDER BY HIRE_DATE DESC 
) RN
FROM EMP
) 
WHERE RN BETWEEN 20 AND 30
 
SELECT EMPNO, ENAME, JOB, MGR, SAL,
    rank() over
      (ORDER BY SAL DESC NULLS LAST) AS RANK,
    dense_rank() over
      (ORDER BY SAL DESC NULLS LAST) AS DRANK
FROM EMP
ORDER BY SAL DESC NULLS last;

MS SQL Server:

SELECT top 10 EMPNO, ENAME, JOB, MGR 
FROM EMP

Sybase:

SET rowcount 10
SELECT COLUMNS
FROM TABLE

Firebird:

SELECT first 10 COLUMNS
FROM TABLE
Personal tools