Mysql and Inner joins – Simple Guide

When writing code in MySQL, you don’t need to write inner joins.

Well you do, but you don’t need to say “INNER JOIN”

MySQL uses a dot syntax which makes our code so much easier.

Our example uses 3 tables with several columns each. A table of users, a table of DVD’s and a table showing who has borrowed what and when.

Users: id,Name
DVD: id, Title, genre
loans: id,owner,Title,Date

So we have entries in all of these tables. And we want to retrieve a list of all of the loans.
We use the name of the table a DOT and the name of the column.
eg Users.Name will give us the Name value from the Users table.

The full query is

Select table.column from tables where table.column = table.column

Select (all the fields we want) from ( list the tables here) where (list what equals what)

Select Users.Name, DVD.Title, loans.Date from Users,DVD,Loans where loans.owner=Users.id, loans.Title=DVD.id

The great part about this is, we only “select” the fields we want and can specify the criteria any other way we like. As long as you include all the tables involved, it will just work.
These can get LONG and detailed, but without having to worry about “Inner Join THIS on THAT” we can use it easily. Here’s a real world example for a business who tracks internal device loans for quality testing.
4 tables and 10 columns.
You should be able to piece together their original table structure without too much trouble.

SELECT PSTT_trans.notes as out_notes, PSTT_users.name as username, PSTT_users.id as staff_number, PSTT_equip.id as equipment, PSTT_equip.name, PSTT_trans.id as transid, PSTT_trans.trackid as trackid, PSTT_trans.timestamp as out_time, PSTT_trans.owner_id as the_owner, PSTT_track.open as opentime
from
PSTT_trans,PSTT_track,PSTT_equip,PSTT_users
WHERE
PSTT_trans.equip_id = PSTT_equip.id AND PSTT_trans.owner_id = PSTT_users.id AND PSTT_trans.trackid = PSTT_track.id and PSTT_track.close is null and PSTT_trans.returned is null and PSTT_trans.direction = ‘out’