SELECT with JOIN

Here are a few more problems to practice with.  Again, practice these queries against a copy of AdventureWorks.

Now we’re going to start retrieving related data. An Employee is a Contact, but a Contact does not have to be an Employee. In adventureworks we store contacts in Person.Contact and we store employees in HumanResources.Employee.

The primary key for Person.Contact is ContactID. The primary key of HumanResources.Employee is EmployeeID. HumanResources.Employee has a Foreign Key, Contact_ID. That relates an Employee record to a Person.Contact record.

Now for for our questions.
1. What query would show the First name, last name, and title for all employees? (HINT: all employees are contacts.)
2. What query would show the First and last name for NON Employees? (HINT: you’re looking for records that don’t exist in both tables.)
3. Based on the last query, are there any employees that are not in our contacts table? (HINT: You can use a LEFT or a RIGHT join to find this. Bonus points if you show me both.)

There are many uses for the JOIN statement. Play around with the different types, and look at the results you get. If you get unexpected results, send me your query, tell me what you think it should do, then explain to me what it actually did. I’ll help explain why it did what it did. If you want to explore other tables, if two tables have a column with the same name, usually there is a relationship there, and you can do the join. Explore the database and send me at least two more joins that you write. Explain to me what you were looking for, and explain how the query does that for you.

If you have any questions, please let me know.  I’m here to help!

UPDATE: Here are the answers.