BrainDump
  Home arrow BrainDump arrow More Advanced SQL Statements
Dev Shed Forums 
Administration  
Apache  
BrainDump  
DHTML  
Flash  
Java  
JavaScript  
Multimedia  
MySQL  
Oracle  
Perl  
PHP  
Practices  
Python  
Reviews  
Security  
Style-Sheets  
Web Services  
XML  
Zend  
Zope  
Forums Sitemap 
IBM® developerWorks 
Dedicated Servers 
E-Commerce Hosting 
Linux Web Hosting 
Managed Hosting 
Small Business Hosting 
Download TestComplete 
VPS Hosting 
Weekly Newsletter

 
Developer Updates  
Free Website Content 
 RSS  Articles
 RSS  Forums
 RSS  All Feeds
Write For Us Get Paid 
Request Media Kit
Contact Us 
Site Map 
Privacy Policy 
Support 
 USERNAME
 
 PASSWORD
 
 
  >>> SIGN UP!  
  Lost Password? 
BRAINDUMP

More Advanced SQL Statements
By: James Payne
  • Search For More Articles!
  • Disclaimer
  • Author Terms
  • Rating: 5 stars5 stars5 stars5 stars5 stars / 7
    2007-10-08

    Table of Contents:
  • More Advanced SQL Statements
  • UNION STATEMENT
  • And along came baby
  • Speeding Up the Search
  • OOPS I DID IT AGAIN

  • Rate this Article: Poor Best 
      ADD THIS ARTICLE TO:
      Del.ici.ous Digg
      Blink Simpy
      Google Spurl
      Y! MyWeb Furl
    Email Me Similar Content When Posted
    Add Developer Shed Article Feed To Your Site
    Email Article To Friend
    Print Version Of Article
    PDF Version Of Article
     
     
     
    ADVERTISEMENT

    Virtual Tradeshows by Ziff Davis Enterprise – A Unique Opportunity to Interact with IT Experts, Access Information, and Gain Insight on Today’s Trends in Technology Learn more

    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;


    More BrainDump Articles
    More By James Payne


       · This is the third installment in my Beginner SQL series. It covers things like the...
     

       

    BRAINDUMP ARTICLES

    - Outsourcing: the Hoopla, the Reality
    - MySQL Plays in the Sun
    - All About SQL Functions
    - SQL: Functioning in the Real World
    - More Advanced SQL Statements
    - Beginning SQL the SEQUEL: Working with Advan...
    - Beginning SQL
    - A Look at the VI Editor
    - A Quick Tour of Boo
    - Book Review: Open Source Licensing
    - PGP and GPG: Email for the Practical Parano...
    - Microsoft Continues War on Open Source
    - Secure Remote Desktop Sharing with VNC on Li...
    - A Look at Google Project Hosting
    - What we can Learn from Two Linux vs. Microso...

     
    Accelerating Trading Partner Performance
     
    Competing on Analytics
     
    Cost Effective Scaling with Virtualization and Coyote Point Systems
     
    Five Checkpoints to Implementing IP Telephony
     
    Hosted Email Security: Staying Ahead of New Threats
     




    © 2003-2008 by Developer Shed. All rights reserved. DS Cluster 1 hosted by Hostway