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.
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.
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.