Database Notes

MySQL has been my database of choice for the web app projects I worked on. My fellowship experience has made me realize that my level of knowledge was limited due to the size of my data and the complexity of the queries I was using. With that, I have created this page in order to keep track of the new things I learn doing the various SQL exercises.

JOINS:
A JOIN clause is used to combine rows from two or more tables, based on a related column between them.

INNER: Returns records that have matching values in both tables

LEFT OUTER: Return all records from the left table, and the matched records from the right table (intersect)

RIGHT OUTER: Return all records from the right table, and the matched records from the left table (compliment of the intersection)

FULL OUTER (not allowed in MySQL, use LEFT & RIGHT with UNION)

http://cloudtobago.com/images/innerjoin.gif
LEFT OUTER JOIN
RIGHT OUTER JOIN
FULL OUTER JOIN

 

Table: Person

Column Name
PersonId
FirstName
LastName
Type
int
varchar
varchar

PersonId is the primary key column for this table.


Table: Address

Column Name
AddressId
PersonId
City
State
Type
int
int
varchar
varchar

AddressId is the primary key column for this table.

Example question
Write a SQL query for a report that provides the following information for each person in the Person table, regardless if there is an address for each of those people:

Output: FirstName, LastName, City, State

Note, We want to produce a report of PEOPLE with or without addresses. To accomplish this, we can use the LEFT JOIN. LEFT JOIN creates a table with ALL the data from the left table (A) and merges the information from the right table (B) that matches.

QUERY STATEMENT
SELECT FirstName, LastName, City, State
FROM Person LEFT JOIN Address ON Person.PersionId = Address.PersonId