SQL
From SkyWiki
(Difference between revisions)
(→traditional Oracle outer join syntax) |
(→ANSI joins in Oracle) |
||
Line 1: | Line 1: | ||
===[http://www.adp-gmbh.ch/ora/sql/join/ansi.html ANSI joins in Oracle]=== | ===[http://www.adp-gmbh.ch/ora/sql/join/ansi.html ANSI joins in Oracle]=== | ||
[http://www.nyoug.org/Presentations/2002/9ijoin.PDF Presentation] about the ANSI syntax introduced in Oracle 9i | [http://www.nyoug.org/Presentations/2002/9ijoin.PDF Presentation] about the ANSI syntax introduced in Oracle 9i | ||
+ | |||
+ | inner join | ||
+ | <source lang=sql> | ||
+ | select ... | ||
+ | from emp | ||
+ | join dept on emp.deptno = dept.deptno | ||
+ | </source> | ||
+ | |||
+ | left outer join | ||
+ | <source lang=sql> | ||
+ | select ... | ||
+ | from emp | ||
+ | left outer join dept on emp.deptno = dept.deptno | ||
+ | </source> | ||
+ | |||
+ | right outer join | ||
+ | <source lang=sql> | ||
+ | select ... | ||
+ | from emp | ||
+ | right outer join dept on emp.deptno = dept.deptno | ||
+ | </source> | ||
===traditional Oracle outer join syntax=== | ===traditional Oracle outer join syntax=== |
Revision as of 16:13, 3 October 2008
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
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