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.
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)
PersonId is the primary key column for this table.
AddressId is the primary key column for this table.
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.
SELECT FirstName, LastName, City, State
FROM Person LEFT JOIN Address ON Person.PersionId = Address.PersonId