More Advanced SQL Statements (Page 1 of 5 )
In this third part to a series on beginning SQL, you'll learn how to use SQL statements to manipulate entire tables: to join them, alter them, and even delete them. It's all part of keeping a firm grip on your databases. Keep reading to learn more.
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
First Name | Last Name | Position | Salary | Social Security |
Larry | Smith | President | $100,000 | 555001234 |
Homer | Sampson | CEO | $75,000 | 444001234 |
Homer | Sampson | CFO | $75,000 | 333001234 |
Liza | Sampson | CTO | $80,000 | 222001234 |
Bruce | Lee | Security | $50,000 | 11100123 |
Your | Name | Whipping Boy | $15,000 | 00000000 |
Birthdays Table
Employee Name | Birth Date | Social Security |
Larry Smith | 02/12/1932 | 555001234 |
Homer Sampson | 02/17/1950 | 444001234 |
Homer Sampson | 02/17/1950 | 333001234 |
Liza Sampson | 06/19/1990 | 222001234 |
Bruce Lee | 06/06/06 | 11100123 |
Your Name | 01/01/1992 | 00000000 |
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:
First Name | Birth Date |
Larry | 02/12/1932 |
Homer | 02/17/1950 |
Homer | 02/17/1950 |
Liza | 06/19/1990 |
Bruce | 06/06/06 |
You | 01/01/1992 |
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.
First Name | Birth Date |
Bruce | 06/06/06 |
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;
Next: UNION STATEMENT >>
More BrainDump Articles
More By James Payne
