SQL

From SkyWiki

(Difference between revisions)
Jump to: navigation, search
Current revision (16:18, 3 October 2008) (view source)
m (ANSI joins in Oracle)
 
(9 intermediate revisions not shown.)
Line 1: Line 1:
-
[[category:remember]]
+
===[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
-
==traditional Oracle outer join syntax==
+
 +
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>
<source lang=sql>
 +
select ...
 +
from emp
 +
left outer join dept on emp.deptno = dept.deptno
</source>
</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>
<source lang=sql>
 +
select ...
 +
from emp
 +
full join dept on emp.deptno = dept.deptno
</source>
</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>
<source lang=sql>
 +
select ...
 +
from emp, dept
 +
where emp.deptno(+) = dept.deptno
</source>
</source>
 +
right outer join
 +
<source lang=sql>
 +
select ...
 +
from emp, dept
 +
where emp.deptno = dept.deptno(+)
 +
</source>
-
===TOP SELECT===
+
===TOP n SELECT===
MySQL:
MySQL:
<source lang=sql>
<source lang=sql>
-
SELECT product, descr, email
+
select EMPNO, ENAME, JOB, MGR, SQL
-
FROM products
+
from  EMP
-
LIMIT 10
+
limit 10 offset 20
</source>
</source>
Line 33: Line 71:
<source lang=sql>
<source lang=sql>
select * from (select  
select * from (select  
-
JOB_NUM,  
+
    EMPNO, ENAME, JOB, MGR, SQL,
-
JOB_NME,  
+
    rank() over(partition by JOB order by HIRE_DATE desc  
-
START_DT_GMT,  
+
) RN
-
STATUS_CD,  
+
from EMP
-
rank() over(partition by JOB_NUM order by START_DT_GMT desc  
+
-
) rn
+
-
from NM_SCHD_JOB_RUN
+
)  
)  
-
where rn < 10
+
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>
</source>
MS SQL Server:
MS SQL Server:
-
MySQL:
 
<source lang=sql>
<source lang=sql>
-
SELECT TOP 10 product, descr, email
+
select top 10 EMPNO, ENAME, JOB, MGR
-
FROM products
+
from EMP
</source>
</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
Personal tools