Home arrow BrainDump arrow More Advanced SQL Statements

More Advanced SQL Statements

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.

TABLE OF CONTENTS:
  1. More Advanced SQL Statements
  2. UNION STATEMENT
  3. And along came baby
  4. Speeding Up the Search
  5. OOPS I DID IT AGAIN
By: James Payne
Rating: starstarstarstarstar / 23
October 08, 2007

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

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;




 
 
>>> More BrainDump Articles          >>> More By James Payne
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

BRAINDUMP ARTICLES

- Apple Founder Steve Jobs Dies
- Steve Jobs` Era at Apple Ends
- Google's Chrome Developer Tool Updated
- Google's Chrome 6 Browser Brings Speed to th...
- New Open Source Update Fedora 13 is Released...
- Install Linux with Knoppix
- iPad Developers Flock To SDK 3.2
- Managing a Linux Wireless Access Point
- Maintaining a Linux Wireless Access Point
- Securing a Linux Wireless Access Point
- Configuring a Linux Wireless Access Point
- Building a Linux Wireless Access Point
- Migrating Oracle to PostgreSQL with Enterpri...
- Demystifying SELinux on Kernel 2.6
- Yahoo and Microsoft Create Ad Partnership

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: