====== SQL Notes ======

===== General Documentation =====

**A Visual Explanation of SQL Joins**
\\ {{:wiki:explainselectstatement.gif?direct&500|A Visual Explanation of SQL Joins (click for full size)}}


===== MySQL =====

  * [[http://www.kirkdorffer.com/wiki/images/ExplainSelectStatement.gif|Understanding Explain Select Statement Results]]
  * [[http://dev.mysql.com/doc/refman/4.1/en/index.html|MySQL 3.23, 4.0, 4.1 Reference Manual]]
  * [[http://dev.mysql.com/doc/refman/5.1/en/index.html|MySQL 5.1 Reference Manual]]

===== Oracle =====

  * [[http://www.oracle.com/technetwork/index.html|Oracle Technology Network]]
  * [[http://www.oracle.com/pls/xe102/homepage|Oracle Database Express Edition Online Documentation 10g Release 2 (10.2)]]
  * [[http://ss64.com/ora/syntax-sqlplus-set.html|SQL*Plus Set Commands]]
  * [[http://www.oracle-base.com/articles/10g/OracleDataPump10g.php|Oracle 10g Data Pump]] (Advanced Exports and Imports)

==== Explain Plans ====

  * [[http://www.evolt.org/node/2986|Producing and Reading Explain Plans]]

To produce an Explain Plan:

<code sql:showcontrols>
explain plan set statement_id = '<SOME_IDENTIFIER>' for
/* ------ Your SQL here ------ */
</code>

To read an Explain Plan:

<code sql:showcontrols>
select operation, options, object_name
from plan_table
where statement_id = '<SOME_IDENTIFIER>'
start with id = 0
connect by prior id=parent_id and prior statement_id = statement_id;
</code>

===== Questions and Topics  =====

Q. How can you find an orphan row in a join?
  Do a Left Join (= Left Outer Join) and look for nulls.
<code sql:showcontrols>
select * 
from TABLE_A
  left outer join TABLE_B
    on TABLE_A.NAME = TABLE_B.NAME 
where TABLE_B.ID is null
</code>