SQL Notes

SQL Notes

--------------------
GROUP BY, DISTINCT
--------------------	

"group by" lets you use aggregate functions, like avg, max, min, sum, and count. "distinct" just removes duplicates.

For example, if you have a bunch of purchase records, and you want to know how much was spent by each department, you might do something like:

select department, sum(amount) from purchases group by department
This will give you one row per department, containing the department name and the sum of all of the "amount" values in all rows for that department.

SELECT c FROM myTbl GROUP BY C
Has the same result as:
SELECT DISTINCT C FROM myTble

---------------------
JOINS
---------------------

Left join

The LEFT JOIN keyword returns all rows from the left table (table_name1), even if there are no matches in the right table (table_name2).
http://www.w3schools.com/sql/sql_join_left.asp

SELECT column_name(s)
FROM table_name1
LEFT JOIN table_name2
ON table_name1.column_name=table_name2.column_name

Right join

The RIGHT JOIN keyword Return all rows from the right table (table_name2), even if there are no matches in the left table (table_name1).

SELECT column_name(s)
FROM table_name1
RIGHT JOIN table_name2
ON table_name1.column_name=table_name2.column_name

Inner Join

The INNER JOIN keyword return rows when there is at least one match in both tables.

SELECT column_name(s)
FROM table_name1
INNER JOIN table_name2
ON table_name1.column_name=table_name2.column_name


-------------------------------
UNION
-------------------------------

SELECT column_name(s) FROM table_name1
UNION
SELECT column_name(s) FROM table_name2
Note: The UNION operator selects only distinct values by default. To allow duplicate values, use UNION ALL.

SQL UNION ALL Syntax

SELECT column_name(s) FROM table_name1
UNION ALL
SELECT column_name(s) FROM table_name2