sql:1999 Basic Grammar (study notes)

sql:1999 Basic Syntax

SELECT [DISTINCT] * |Column Name[ as]alias,........ fromTable Name 1[alias 1][Cross join table name 2 alias 2]|[NATURAL Join table name 2 alias 2][Join table Name 2 alias 2 USING (associated column name)][Join table Name 2 Alias 2 on (association condition)][Left | Right | Full OUTER JOIN Table 2 on (association condition)][WHERE condition (s)][Order by sort field 1,asc| DESC, sorted by field 2 asc| DESC,....] 

• Cross connect (crosses in)

Grammar:

SELECT [DISTINCT] * |Column Name[ as]alias,........ fromTable Name 1[alias 1][Cross join table name 2 alias 2]|[WHERE condition (s)][Order by sort field 1,asc| DESC, sorted by field 2 asc| DESC,....] 

Using cross-linking student Cartesian product

SELECT *  from  Cross JOIN

Use Where to remove

SELECT *  from  Cross JOIN Dept D WHERE E.deptno=

• Natural connection (NATURAL join)

Grammar:

SELECT [DISTINCT] * |Column Name[ as]alias,........ fromTable Name 1[alias 1][NATURAL Join table name 2 alias 2][WHERE condition (s)][Order by sort field 1,asc| DESC, sorted by field 2 asc| DESC,....] 

Use natural connections

SELECT *  from emp  JOIN  dept; -- natural connections can eliminate Cartesian product directly

• Using clause

Grammar:

SELECT [DISTINCT] * |Column Name[ as]alias,........ fromTable Name 1[alias 1] [Join table Name 2 alias 2 USING (associated column name)][WHERE condition (s)][Order by sort field 1,asc| DESC, sorted by field 2 asc| DESC,....] 

Using a using clause

SELECT *  from JOIN Dept USING (DEPTNO); -- Using post field ()

• ON clause

Grammar:

SELECT [DISTINCT] * |Column Name[ as]alias,........ fromTable Name 1[alias 1] [Join table Name 2 Alias 2 on (association condition)][WHERE condition (s)][Order by sort field 1,asc| DESC, sorted by field 2 asc| DESC,....] 

Use the ON clause:

SELECT *  from JOIN  on (E.deptno=d.deptno); -- On after is the condition -- () can omit

• External connection

Grammar:

 select  [    *  | Column name [  ]   alias,........  from  table name 1 [  alias 1  ][ left | Right | Full OUTER JOIN Table 2 on (association condition)    [ Span style= "color: #ff0000;" >where condition (s)    [ order by Sort field 1,asc| DESC, sorted by field 2 asc| DESC,....   

Left Outer connection:

SELECT *  from  Left OUTER JOIN Dept D  on (E.deptno=d.deptno); -- use on to eliminate Cartesian product

SELECT *  from  Left OUTER JOIN Dept Dusing (DEPTNO); -- use using to eliminate Cartesian product

Right outer connection:

SELECT *  from  Right OUTER JOIN Dept D  on (E.deptno=d.deptno); -- use on to eliminate Cartesian product

SELECT *  from  Right OUTER JOIN Dept Dusing (DEPTNO); -- use using to eliminate Cartesian product

Full Outer connection:

SELECT *  from  Full OUTER JOIN Dept D  on (E.deptno=d.deptno); -- use on to eliminate Cartesian product

SELECT *  from  Full OUTER JOIN Dept Dusing (DEPTNO); -- use using to eliminate Cartesian product

sql:1999 Basic Grammar (study notes)