Online Test Banks
Score higher
See Online Test Banks
eLearning
Learning anything is easy
Browse Online Courses
Mobile Apps
Learning on the go
Explore Mobile Apps
Dummies Store
Shop for books and more
Start Shopping

How to Use Column-Name Join and Inner Join in SQL

SQL offers several different types of joins, including the column-name join and inner join, to help you accomplish your specific task. Here are some examples to help guide you on your SQL journey.

Column-name join

The column-name join is like a natural join, but it’s more flexible. In a natural join, all the source table columns that have the same name are compared with each other for equality. With the column-name join, you select which same-name columns to compare. You can choose them all if you want, making the column-name join (effectively) a natural join.

Or you may choose fewer than all same-name columns. In this way, you have a great degree of control over which cross-product rows qualify to be placed into your result table.

Suppose you’re a chess-set manufacturer and have one inventory table that keeps track of your stock of white pieces and another that keeps track of black pieces. The tables contain data as follows:

WHITE      BLACK
-----      -----
Piece Quant Wood   Piece Quant Wood
----- ----- ----   ----- ----- ----
King 502 Oak   King  502 Ebony
Queen 398 Oak   Queen 397 Ebony
Rook 1020 Oak   Rook 1020 Ebony
Bishop 985 Oak   Bishop 985 Ebony
Knight 950 Oak   Knight 950 Ebony
Pawn 431 Oak   Pawn  453 Ebony

For each piece type, the number of white pieces should match the number of black pieces. If they don’t match, some chessmen are being lost or stolen, and you need to tighten security measures.

A natural join compares all columns with the same name for equality. In this case, a result table with no rows is produced because no rows in the WOOD column in the WHITE table match any rows in the WOOD column in the BLACK table.

This result table doesn’t help you determine whether any merchandise is missing. Instead, do a column-name join that excludes the WOOD column from consideration. It can take the following form:

SELECT *
 FROM WHITE JOIN BLACK
 USING (Piece, Quant) ;

The result table shows only the rows for which the number of white pieces in stock equals the number of black pieces:

Piece Quant Wood Piece Quant Wood
----- ----- ---- ----- ----- ----
King 502 Oak King 502 Ebony
Rook 1020 Oak Rook 1020 Ebony
Bishop 985 Oak Bishop 985 Ebony
Knight 950 Oak Knight 950 Ebony

The shrewd person can deduce that Queen and Pawn are missing from the list, indicating a shortage somewhere for those piece types.

Inner join

By now, you’re probably getting the idea that joins are pretty esoteric and that it takes an uncommon level of spiritual discernment to deal with them adequately. You may have even heard of the mysterious inner join and speculated that it probably represents the core or essence of relational operations.

Well, ha! The joke’s on you: There’s nothing mysterious about inner joins. The column-name join could have been formulated in the last example as an inner join by using the following syntax:

SELECT *
 FROM WHITE INNER JOIN BLACK
 USING (Piece, Quant) ;

The result is the same.

The inner join is so named to distinguish it from the outer join. An inner join discards all rows from the result table that don’t have corresponding rows in both source tables. An outer join preserves unmatched rows. That’s the difference. Nothing metaphysical about it.

  • Add a Comment
  • Print
  • Share
blog comments powered by Disqus
Advertisement

Inside Dummies.com

Dummies.com Sweepstakes

Win $500. Easy.