INNER JOIN SQL Query

A JOIN combines data from two different tables. The most common type is the INNER JOIN. In the INNER JOIN rows from two tables are combined when the data queried is found in the specified rows of both tables. This will result in a fairly narrow search. The syntax for an SQL INNER JOIN is:

SELECT column_name(or names)
FROM table1
INNER JOIN table2
ON table1.specific_column=table2.specific_column

In a Monopoly game, we have a table that lists all the property, their prices, and their group. This table we call "property". Here we will show only the first couple entries:

prop_idnamepricegroup
1Baltic Ave60purple
2Mediterranean Ave60purple
3Reading Railroad200rr
4Oriental Ave100light blue
5Vermont Ave100light blue
6Connecticut Ave120light blue

We might also have a table that keeps track of transactions. This would include transaction number, the owner_name, the prop_id, and the turn number on which the property was acquired.

trans_idowner_nameprop_idturn
1Mary61
2Mary96
3Tom58
4Joe214
5Joe137

Now say we want a list of the property owned and we are particularly interested in their prices and when they were acquired. We could make the following query:

SELECT property.name, property.price, transactions.turn
FROM property
INNER JOIN transactions
ON property.prop_id = transactions.prop_id
ORDER BY transactions.turn
namepriceturn
Connecticut Ave1201
Vermont Ave1008
Mediterranean Ave6014
Baltic Ave6037

Note that Mary's transaction on turn six was not recorded because prop_id number 9 could not be found in the property table. Also Reading Railroad is not included from the property table because there has not yet been a transaction involving it. If there were no matches, the query would return an empty table, but it would not return false.

< JOIN Statements in SQL | LEFT JOIN SQL >


Send notes in disappearing ink!

Interesting Pages