Topic: SQL

One of the most useful SQL keywords JOIN allows you to compare data across tables. There comes a time in every young developer’s life where they have created a ton of tables, but they need to match information from one to another table. For example, you may have a list of users, but you need to match them with a list of orders. JOIN makes this very possible and quite simple once you get the hang of it. However, this might be one of the most difficult tutorials for someone who is just learning SQL.

Before we get into using a JOIN, I should tell you there are actually 4 types of JOINs.

• JOIN – Returns values when values match in both tables
• LEFT JOIN – Returns all specified “left” values including the matches on the “right”
• RIGHT JOIN – Returns all specified “right” values including the matches on the “left”
• FULL JOIN – Returns all specified “left” values and “right” values

“Left” and “Right” Tables

When the creators made SQL, the envisioned sheets of paper and apparently when creating JOIN, they used what they saw. In the most primitive JOIN usage, your “left” table is the table selected first (usually after SELECT). Your “right” table is the one directly following the JOIN keyword(s). I know it is pretty complicated just bear with me until the example where it will all make sense. JOIN obviously requires two tables and they are:

Table: “Users”

Table: “Purchases”

All political jokes aside, let’s try to get all of our users including the their purchases. Example, please!

SELECT Users.first, Purchases.item 
FROM Users
LEFT JOIN Purchases
ON Users.username=Purchases.username
ORDER BY Users.first

Beautiful! You might be confused, and I most definitely was when I first learned about JOINs. For example, Users.first? What in the world is that? Remember we are working with 2 tables and SQL will get confused very quickly if we are telling it about columns, when it doesn’t know which table that contains that column. The Users is our first table, and first is a column in the “Users” table. Now, SQL is like “Oh! That column.” We repeat that idea to get our item column in our “Purchases” table. We pick FROM Users because we want “Users” to be our first (LEFT) table. This means that we want every single first column in our “Users” table now matter what is specified in the following JOIN.

Now, we get tricky. When then declare our 2nd (RIGHT) table with LEFT JOIN Purchases. But wait, we need to tell it why we are including a 2nd table. We use the SQL keyword ON to say we want to find matching values in our username column in the Users table with our other username column in our Purchases table. Then we wrap up with saying, “Hey, sort it by their first, which is in Users, really quick. Ultimately, if you know how to use a LEFT JOIN, you can use all of the rest. Go ahead and test out the others you little querying mastermind.

Dates and times are extremely frustrating data types and SQL doesn’t make them any easier to work with than other languages. While date and time are actually completely different data types, they are often merged into a datetime data type. SQL dates and times alone are pretty simple even though they require a special formatting. However, merging the two can be one of the most painful tasks you will face in your early years. A good rule of thumb is to only use a time and date together when you absolutely need to or will need to in the future. I’ll explain why dates and times complicates things in just a minute. First, let’s look at how easy life when you simply work with dates.

Working With SQL Dates

Our “Users” Table:

Let’s try to get all of our members before December 25, 2011.

SELECT *
FROM Users
WHERE member_since < '2011-12-25'

Alright, we said BEFORE December 25, 2011, which means poor Hillary isn’t included because she is on that day. Al barely made the cut, but old George was way before our cut off date. I suppose we should actually talk about what happened. Up to WHERE member_since < ‘2011-12-25’, we know what were doing. We could probably guess what is going on here, but I’ll beat the dead horse. We check our member_since column to see if it is less than, <, our date, which was written in single quotes as ‘2011-12-25’. Often when googling for help with dates, you see this syntax referred to as ‘YYYY-MM-DD’, where Y is for year, M is for month, and D is for day, obviously. Each repetition is a symbol of a blank character space for that element. So, you only want 2 digits for month because that makes sense, and maybe you don’t care about the first 2 characters of year. Instead you just write, YY to get 11. Easy enough, onto the wonderful DATETIMEs

Using DATETIME in SQL

Our New “Users” Table:

Now, we attempt to get all of our members that logged in before before 1:00PM December 24, 2011.

SELECT * 
FROM Users
WHERE last_login < '2011-12-24 13:00:00'

Poor George… Alright, exactly the same syntax as a last one with except we replaced the column name and used a DATETIME string to compare against. The DATETIME data type contains all of the same elements as the DATE data type plus a time string. Commonly referenced as ‘YYYY-MM-DD HH:MM:SS’ Again, in single quotations and with a space separating the date string from the time string, we create our DATE string for comparison. Hillary and Al both make the cut because they logged in before the ‘2011-12-24 13:00:00’ cut off DATETIME. It’s much easier here than it looks in practice. That’s because we put in the DATETIME string by hand. SQL has a few DATE functions you can play around with that you can replace our string with:

Important SQL DATE Functions

Note: SQL DATE functions differ greatly across SQL versions. These are MySQL.

• CURDATE() – gets the current date
• CURTIME() – gets the current time
• NOW() – gets both the date and time (in DATETIME format)

If you are not using MySQL, consider switching. Just kidding, kind of, but no worries, a simple query of Google will help you find the right SQL functions for your SQL database.

And so the DATETIMEs came to pass, and the light shined through the best part of SQL, functions. There are quite a bit of functions, and you might actually use the majority of them. I will just cover a basic overview of how to use a few functions, so you can easily use the rest when your tinkering around in SQL. Count, sum, and average are possibly the most common and in that order. However, sometimes you will use super helpful functions like max and min. Let’s discover what they look like, then we’ll use them.

Common SQL Functions

• count() – counts the number of records returned from the query
• sum() – adds the values in records returned from the query together
• avg() – averages values in records returned from the query
• max() – finds the highest value in all of the records returned from the query
• min() – finds the lowest value in all of the records returned from the query

Our “Users” Table:

SELECT COUNT(*) 
FROM Users

As you can see, all COUNT() does is count the values in the column specified inside the parentheses. However, we used the * wildcard, so it just counted all records.

SELECT SUM(views) 
FROM Users
WHERE id <= 2 

Now, we can use the SQL Functions on columns. We said give us the sum, sum(), of our views column, but only where the column id is less than or equal to 2. The 250 is the sum of Hillary and Al’s views because their id is less than or equal to 2. Note: math functions in SQL won’t do so well with strings.

SELECT AVG(views) 
FROM Users
WHERE id >= 2 

The SQL Function average, avg(), tells SQL to crunch our numbers in the views column and give us the average, but only where the id is greater than or equal to 2. This only averages George and Al’s views, which gives us 225.

SELECT MAX(views)
FROM Users

So, the SQL Function max, obviously short for maximum, returns 300 because it is the highest number in our views column.

SELECT MIN(views)
FROM Users

Exactly on the opposite spectrum of is the MIN(), short for minimum, SQL Function. This function grabs the lowest value of our views column.

And that’s all folks. Feel free to play around with the other SQL functions