SQL

From SkyWiki

(Difference between revisions)
Jump to: navigation, search
m
(traditional Oracle outer join syntax)
Line 1: Line 1:
-
==traditional Oracle outer join syntax==
+
===[http://www.adp-gmbh.ch/ora/sql/join/ansi.html ANSI joins in Oracle]===
 +
 +
===traditional Oracle outer join syntax===
left outer join
left outer join
-
 
<source lang=sql>
<source lang=sql>
</source>
</source>
-
 
right outer join
right outer join
-
 
<source lang=sql>
<source lang=sql>
</source>
</source>
-
 
inner join
inner join
-
 
<source lang=sql>
<source lang=sql>
</source>
</source>

Revision as of 15:41, 3 October 2008

ANSI joins in Oracle

traditional Oracle outer join syntax

left outer join

 

right outer join

 

inner join

 


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