Our First Join
In the above example I'm selecting the fields logid, log.userid, [action], logDate, ip, username, firstName and lastName. This is being selected from the table and then I'm joining the table users to log based on the users.userid column being equal to the log.userid column. The log.userid and users.userid refers to the specific columns... by having log.userid it means I'm referring to the userid column that's in the log table. Conversely, users.userid means I'm referring to the userid column in the users table. Since there are two identically named columns in the two table, if I don't specify which column I'm referring to I'll get an error.
Our First Join
What's happening here is that I'm doing a select from the log table. I'm saying also that the users table is directly related to the log table. The relation is based off of the userid field on the two tables. This then lets me select fields across both tables. This idea can be extended to include more tables or do even more complicated things. The query can also be rewritten some.
Our First Join
Here I've done two things. First, I've aliased log and users. Notice the a and b behind them in the from clause? This is an alias. Imagine if the table were a really long name, like userLogFilesDailyInterval and currentUserTable. Rather than typing a rather long field name like userLogFilesDailyInterval.userid, by aliasing we can shorten it to a.userid. The alias can be anything you want. I prefer to use letters because it's fast, but in some cases it makes more sense to alias it as something meaningful, perhaps usertable.userid. The tables in our database aren't especially long, but I'll continue to use aliasing because I so desire.
The second thing we did was get rid of the JOIN and ON words. Instead I'm just selecting from the two tables. In the WHERE clause I then create our table relationships. This is the same thing as explicitly using JOIN. Feel free to use whichever method you find the easiest to read and use.
Now that we've covered some basic joins, let's look at subqueries.
