SQL
From SkyWiki
(Difference between revisions)
Current revision (16:18, 3 October 2008) (view source) m (→ANSI joins in Oracle) |
|||
| (10 intermediate revisions not shown.) | |||
| Line 1: | Line 1: | ||
| - | [ | + | ===[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 | |
| - | = | + | |
| + | inner join | ||
| + | <source lang=sql> | ||
| + | select ... | ||
| + | from emp | ||
| + | join dept on emp.deptno = dept.deptno | ||
| + | </source> | ||
left outer join | left outer join | ||
| + | <source lang=sql> | ||
| + | select ... | ||
| + | from emp | ||
| + | left outer join dept on emp.deptno = dept.deptno | ||
| + | </source> | ||
right outer join | right outer join | ||
| + | <source lang=sql> | ||
| + | select ... | ||
| + | from emp | ||
| + | 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> | ||
| + | |||
| + | ===traditional Oracle outer join syntax=== | ||
inner join | inner join | ||
| + | <source lang=sql> | ||
| + | select ... | ||
| + | from emp, dept | ||
| + | where emp.deptno = dept.deptno | ||
| + | </source> | ||
| + | left outer join | ||
| + | <source lang=sql> | ||
| + | select ... | ||
| + | from emp, dept | ||
| + | where emp.deptno(+) = dept.deptno | ||
| + | </source> | ||
| - | TOP | + | right outer join |
| + | <source lang=sql> | ||
| + | select ... | ||
| + | from emp, dept | ||
| + | where emp.deptno = dept.deptno(+) | ||
| + | </source> | ||
| + | |||
| + | ===TOP n SELECT=== | ||
| + | MySQL: | ||
| + | <source lang=sql> | ||
| + | select EMPNO, ENAME, JOB, MGR, SQL | ||
| + | from EMP | ||
| + | limit 10 offset 20 | ||
| + | </source> | ||
| + | |||
| + | Oracle >= 8.1.6: | ||
| + | <source lang=sql> | ||
| + | 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; | ||
| + | </source> | ||
| + | |||
| + | MS SQL Server: | ||
| + | <source lang=sql> | ||
| + | select top 10 EMPNO, ENAME, JOB, MGR | ||
| + | from EMP | ||
| + | </source> | ||
| + | |||
| + | Sybase: | ||
| + | <source lang="sql"> | ||
| + | set rowcount 10 | ||
| + | select COLUMNS | ||
| + | from TABLE | ||
| + | </source> | ||
| + | |||
| + | Firebird: | ||
| + | <source lang="sql"> | ||
| + | select first 10 COLUMNS | ||
| + | from TABLE | ||
| + | </source> | ||
| + | |||
| + | |||
| + | [[category:remember]] | ||
| + | [[category:Oracle]] | ||
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
