Contents
INNER JOIN
*Returns all rows from both tables where there is a match
*Example
SELECT field1, field2, field3 FROM first_table INNER JOIN second_table ON first_table.keyf = second_table.foreign_key
RIGHT OUTER JOIN (ASCII and Oracle 9i)
*All rows from the left table (dept) are retained
*Example
SELECT dept.*, emp.* FROM dept LEFT OUTER JOIN emp ON dept.deptno = dept.deptno
*Returns all the rows from the first table, even if there are no matches in the second table
*Example
SELECT field1, field2, field3 FROM first_table LEFT JOIN second_table ON first_table.key=second_table.foreign_key
RIGHT OUTER JOIN (ASCII and Oracle 9i)
*All rows from the right table (emp) are retained
*Example
SELECT dept.*, emp.* FROM dept RIGHT OUTER JOIN emp ON dept.deptno = dept.deptno
*Returns all the rows from the second table, even if there are no matches in the first table.
*Example
SELECT field1, field2, field3 FROM first_table RIGHT JOIN second_table ON first_table.key=second_table.foreign_key
FULL OUTER JOIN (ASCII and Oracle 9i)
*All rows from both the left and right tables (dept and emp) are retained
*Example
SELECT p.part_id, s.supplier_name FROM part p FULL OUTER JOIN supplier s ON p.supplier_id = s.supplier_id
Pre Oracle 9i OUTER JOIN
*Returns rows even if there is not a match from the outer table (return null values)
*Outer table is the table denoted by the outer join sign
*Driving table is the other table without outer join sign
*Null values are returned from outer table if no match found
*Expression
SELECT field1, field2, field3 FROM left_table LEFT, right_table RIGHT WHERE LEFT.key = RIGHT.key (+) AND RIGHT.field1 (+) = ‘SOME VALUE’
Oracle OUTER JOIN Caveats
*Half baked multi-column joins
SELECT * FROM t1, t2 WHERE t1.c1=t2.c1(+) AND t1.c2=t2.c2(+) Incomplete JOIN trail SELECT * FROM t1,t2,t3,t4 WHERE t1.c1=t2.c1(+) AND t2.c2=t3.c2(+) AND t3.c3=t4.c3(+) JOIN TO a constant SELECT * FROM t1, t2 WHERE t1.c1=t2.c1(+) AND t1.c2(+)=‘Y’
UNION and UNION ALL
*Expression
SQL statement 1 UNION SQL statement 2 Restrictions
*All selected columns need to be of the same data type
*Only selects distinct values
*Use UNION ALL to select all values
References
* http://www.codeproject.com/Articles/33052/Visual-Representation-of-SQL-Joins