Tags

links:
http://www.dbbm.fiocruz.br/class/Lecture/d17/sql/jhoffman/sqltut.html#Joins
http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-joins.html (venn diagram)
http://databases.about.com/od/sql/l/aaaggregate1.htm
http://stackoverflow.com/questions/16162897/oracle-using-clause-best-practice

summary:
– select columns should match group columns
– aggregate functions provide summarization of large volumn of data
– imo when cases where tables are normalized and join putting them de-normalized, we’re using “having” to filter what we need and aggregate functions can analyze the non-normalize logical table/group

IN, “= ANY”, or “= SOME”
========================
– that these keywords refer to any possible row matches, not column matches…that is, you cannot put multiple columns in the subquery Select clause

JOIN
====
– inner join
– implicit/old style:
SELECT * FROM table1 A, table2 B WHERE A.col1=B.col2
– explicit:
SELECT * FROM table1 A INNER JOIN table2 B ON A.col1=B.col2

– left join
– implicit/old style:
SELECT * FROM table1 A, table2 B WHERE A.col1=B.col2(+)
– explicit:
SELECT * FROM table1 A LEFT JOIN table2 B ON A.col1=B.col2

– valid A but B null:
SELECT * FROM table1 A LEFT JOIN table2 B ON A.col1=B.col2
WHERE B.col2 IS null

– cross join
– cartesian product somewhat like a cross product join
pretty much similar to this (select 2 tbls with no conditions):
SELECT * from table1, table2

GROUP-BY
========
– group-by columns must be part of the select columns
– only the select clause can include functions in terms of those columns

JOIN-USING
==========
select e.ename, d.dname
from emp e join dept d using (deptno);

select e.ename, d.dname
from emp e join dept d on d.deptno = e.deptno;

Advertisements