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.