Tip

Joining tables in SQL queries

Having the capability to select data from multiple tables is one of SQL's most powerful features. Without this capability, the entire relational database concept would not be feasible. Single-table queries are sometimes quite informative, but in the real world, the most practical queries are those whose data is acquired from multiple tables within the database.

A relational database is broken up into smaller, more manageable tables for simplicity and the sake of overall management ease. As tables are divided into smaller tables, the related tables are created with common columns -- primary keys. These keys are used to join related tables to one another.

A join combines two or more tables to retrieve data from multiple tables. You might ask why you should normalize tables if, in the end, you are only going to rejoin the tables to retrieve the data you want. You rarely select all data from all tables, so it is better to pick and choose according to the needs of each individual query. Although performance may suffer slightly due to a normalized database, overall coding and maintenance are much simpler.

While different implementations have many ways of joining tables, you concentrate on the most common joins in this lesson. The types of joins that you learn are:

    EQUIJOINS
    NATURAL JOINS
    NON-EQUIJOINS
    OUTER JOINS
    SELF JOINS

Perhaps the most used and important of the joins is the EQUIJOIN, also referred

    Requires Free Membership to View

to as an INNER JOIN. The EQUIJOIN joins two tables with a common column in which each is usually the primary key. The syntax for an EQUIJOIN is

SELECT TABLE1.COLUMN1, TABLE2.COLUMN2...
FROM TABLE1, TABLE2 [, TABLE3 ]
WHERE TABLE1.COLUMN_NAME = TABLE2.COLUMN_NAME
[ AND TABLE1.COLUMN_NAME = TABLE3.COLUMN_NAME ]
Look at the following example:
SELECT EMPLOYEE_TBL.EMP_ID,
    EMPLOYEE_PAY_TBL.DATE_HIRE
FROM EMPLOYEE_TBL,
    EMPLOYEE_PAY_TBL
WHERE EMPLOYEE_TBL.EMP_ID = EMPLOYEE_PAY_TBL.EMP_ID;

This SQL statement returns the employee identification and the employee's date of hire. The employee identification is selected from the EMPLOYEE_TBL (although it exists in both tables, you must specify one table), whereas the hire date is selected from the EMPLOYEE_PAY_TBL. Because the employee identification exists in both tables, both columns must be justified with the table name. By justifying the columns with the table names, you tell the database server where to get the data.

Data in the following example is selected from tables EMPLOYEE_TBL and EMPLOYEE_PAY_TBL tables because desired data resides in each of the two tables. An equality join is used.

SELECT EMPLOYEE_TBL.EMP_ID, EMPLOYEE_TBL.LAST_NAME,
    EMPLOYEE_PAY_TBL.POSITION
FROM EMPLOYEE_TBL, EMPLOYEE_PAY_TBL
WHERE EMPLOYEE_TBL.EMP_ID = EMPLOYEE_PAY_TBL.EMP_ID;
EMP_ID    LAST_NAM POSITION
--------- -------- -------------
311549902 STEPHENS MARKETING
442346889 PLEW     TEAM LEADER
213764555 GLASS    SALES MANAGER
313782439 GLASS    SALESMAN
220984332 WALLACE  SHIPPER
443679012 SPURGEON SHIPPER

6 rows selected.

Notice that each column in the SELECT clause is preceded by the associated table name in order to identify each column. This is called qualifying columns in a query. Qualifying columns is only necessary for columns that exist in more than one table referenced by a query. You usually qualify all columns for consistency and to avoid any questions when debugging or modifying SQL code.


To read the entire article from which this tip is excerpted, click over to InformIT. You have to register there, but the registration is free.


This was first published in January 2003

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.