More Advanced SQL Statements

Wedding, matrimony. Holy bliss. These are the things we think of when we discuss forming a union or joining together. We think of babies when we talk about creation. Then we look at the blank computer screen in front of us and realize that we should have spent more time working and less time talking. If you don't finish up those database queries, you'll never get to those bars. And without bars full of drunken women whose eyes are too bleary to notice your odd resemblance to Woody Allen, you'll never get married.

When working with databases, sometimes you want to wed data from two or more tables. After all, that's what relational databases are all about. Well to do that, you are going to need a number of SQL Statements designed specifically for that. You'll also need an Internet degree in religion and a certificate that says you can marry people. No, just kidding; the statements will do the trick.

So without further ado, let's get these tables married and make some babies...err more tables.

JOIN

Now let's create a second table that shows our employees' birth dates. We'll also have employees' names and employees' social security numbers in the table. We'll name the table Birthdays. After you finish creating the Birthdays table, go back to your Employees table and add the SocialSecurity column. When you are done the two tables should appear this way:

Employees Table

Birthdays Table

So now we want to take these two tables, have them make sweet sweet database love, and create one new table. This new table will contain each employee's first name, birth date, and social security number.

SELECT EMPLOYEES.FirstName, BIRTHDAYS.BirthDate

FROM EMPLOYEES, BIRTHDAYS

WHERE EMPLOYEES.SocialSecurity=BIRTHDAYS.SocialSecurity;

This will result in the following table:

If we wanted to know who in the database was born on 06/06/06 we could add an AND operator to the mix and find out.

SELECT EMPLOYEES.FirstName, BIRTHDAYS.BirthDate

FROM EMPLOYEES, BIRTHDAYS

WHERE EMPLOYEES.SocialSecurity=BIRTHDAYS.SocialSecurity

AND BIRTHDAYS.BirthDate=06/06/06;

That would show us that Bruce (and the Antichrist if he were in our database) was born on that day.

The above examples are ways of retrieving data from two tables using primary keys. The other way to do so is with a JOIN statement. There are technically three types of JOIN STATEMENTS. We will begin with the INNER JOIN.

SELECT EMPLOYEES.FirstName, BIRTHDAYS.BirthDate

FROM EMPLOYEES

INNER JOIN BIRTHDAYS

ON EMPLOYEES.SocialSecurity=BIRTHDAYS.SocialSecurity;

The INNER JOIN will return all rows of data from the EMPLOYEE table, and all the data from the BIRTHDAYS table where there is a match. If there is not a match, those rows will not be shown.

With the LEFT JOIN, it is slightly different. The LEFT JOIN will return all rows of data from the EMPLOYEE table, and all the data from the BIRTHDAYS table, even if there were no matches in the BIRTHDAYS table. Basically what this means is that if you forgot to type in Bruce Lee's birth date, it will still list his name, but his birth date field will be blank.

SELECT EMPLOYEES.FirstName, BIRTHDAYS.BirthDate

FROM EMPLOYEES

LEFT JOIN BIRTHDAYS

ON EMPLOYEES.SocialSecurity=BIRTHDAYS.SocialSecurity;

Finally there is the RIGHT JOIN, which works in a manner opposite to that of the LEFT JOIN, in that it will return all the results in the BIRTHDAY table regardless of whether there is a match, yet will not list the data in the EMPLOYEES table if there is no match. Its code looks like this:

SELECT EMPLOYEES.FirstName, BIRTHDAYS.BirthDate

FROM EMPLOYEES

RIGHT JOIN BIRTHDAYS

ON EMPLOYEES.SocialSecurity=BIRTHDAYS.SocialSecurity;