1. Tech

Your suggestion is on its way!

An email with a link to:

http://databases.about.com/od/careers/l/aajoins1.htm

was emailed to:

Thanks for sharing About.com with others!

Retrieving Data from Multiple Tables with SQL Joins
Part 1: Introducing Joins
More of this Feature
Part 2: Inner Joins
Part 3: Outer Joins

Join the Discussion
"Share your questions and tap the knowledge of hundreds of your peers."
Mike Chapple

Related Resources
SQL Fundamentals
Creating Databases and Tables in SQL
Retrieving Data with SQL Queries

In several recent articles we explored the fundamental concepts of SQL, the process of creating databases and database tables and the art of retrieving data from a database using simple queries. This article expands on these topics and looks at using join techniques to retrieve data from multiple tables.

By way of example, let's return to our fictitious XYZ Corporation. XYZ utilizes an Oracle database to track the movements of their vehicle fleet and drivers between their facilities. Some employees are assigned to drive trucks while others are assigned to drive cars. Take a moment to examine the following two tables from their vehicle management database:

drivers

licensenum

lastname

firstname

location

class

13232

Baker

Roland

New York

Car

18431

Smythe

Michael

Miami

Truck

41948

Jacobs

Abraham

Seattle

Car

81231 Ryan Jack Annapolis Car

vehicles

tag

location

class

D824HA

Miami

Truck

H122JM

New York

Car

J291QR

Seattle

Car

L990MT

Seattle

Truck

P091YF

Miami

Car

In the previous article, we looked at methods used to retrieve data from single tables. For example, we could use simple SELECT statements to answer questions such as:

  • Which drivers are located in New York?

  • How many cars are in each city?

  • Which drivers assigned to drive trucks are located in Miami?

Practical applications often require the combination of data from multiple tables. Our vehicle managers might make requests like the following:

  • List all of the vehicle/driver pairings possible without relocating a vehicle or driver

  • List all of the drivers authorized to drive vehicles located in Miami

Granted, it would be possible to create complex SELECT statements using subqueries to fulfill these requests. However, there's a much simpler method -- the use of inner and outer joins. We'll explore each of these concepts in the next two sections of this article. Read on!

Next page > Inner Joins > Page 1, 2, 3


You can opt-out at any time. Please refer to our privacy policy for contact information.

Discuss in my forum

See More About
Related Video
Learn About Databases
Create Excel Pivot Tables

©2014 About.com. All rights reserved.