SQL Notes

A Visual Explanation of SQL Joins

• Understanding Explain Select Statement Results
• MySQL 3.23, 4.0, 4.1 Reference Manual
• MySQL 5.1 Reference Manual

• Oracle Technology Network
• Oracle Database Express Edition Online Documentation 10g Release 2 (10.2)
• SQL*Plus Set Commands
• Oracle 10g Data Pump (Advanced Exports and Imports)

• Producing and Reading Explain Plans

To produce an Explain Plan:

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

To read an Explain Plan:

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;

Q. How can you find an orphan row in a join?

Do a Left Join (= Left Outer Join) and look for nulls.

select * 
from TABLE_A
  left outer join TABLE_B
    on TABLE_A.NAME = TABLE_B.NAME 
where TABLE_B.ID is null