JOINs

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”

id username first last
1 ohBillNotAgain Hillary Clinton
2 internetCreator Al Gore
3 dontMessWithTexas George Bush

Table: “Purchases”

id username item quantity
1 ohBillNotAgain Pickles 95
2 internetCreator Meerkat Treats 11

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
Result
first item
Al Meerkat Treats
George  
Hillary Pickles

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.

DATE and DATETIME

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:

id username first last member_since
1 ohBillNotAgain Hillary Clinton 2011-12-25
2 internetCreator Al Gore 2011-12-8
3 dontMessWithTexas George Bush 2011-7-9

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

Example
SELECT *
FROM Users
WHERE member_since < '2011-12-25'
Result
id username first last member_since
2 internetCreator Al Gore 2011-12-8
3 dontMessWithTexas George Bush 2011-7-9

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:

id username first last last_login
1 ohBillNotAgain Hillary Clinton 2011-12-24 12:59:15
2 internetCreator Al Gore 2011-12-24 11:53:25
3 dontMessWithTexas George Bush 2011-12-24 16:24:17

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

Example
SELECT * 
FROM Users
WHERE last_login < '2011-12-24 13:00:00'
Result
id username first last last_login
1 ohBillNotAgain Hillary Clinton 2011-12-24 12:59:15
2 internetCreator Al Gore 2011-12-24 11:53:25

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.

Functions

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:

id name views
1 Hillary 100
2 Al 150
3 George 300
Example
SELECT COUNT(*) 
FROM Users
Result

3

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.

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

250

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.

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

225

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.

Example
SELECT MAX(views)
FROM Users
Result

300

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

Example
SELECT MIN(views)
FROM Users
Result

100

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

Comparing Dates

Warning: Comparing dates can make you rip your hair out. The difference of two dates provides a difficult task because the people who made our time structure obviously did not care to consider the future programmers that have to deal with it every day. Using MySQL, the task is much easier if you know a few MySQL Functions like “DATEDIFF” and “TIMESTAMPDIFF”. The idea is that the MySQL functions do all of the hard work and you just input the particular values into the function. In this tutorial, we will compare 2 dates and output the dates in the difference of days and the difference of seconds.

MySQL DATEDIFF Function

Example
SELECT DATEDIFF(‘2013-01-14 10:59:10′,’2013-01-17 11:50:34’)*24*60*60;
#259200

The DATEDIFF function is not as robust as it appears. We provided the trailing “*24*60*60” to convert the returned value into seconds. In the next example you will see why I have done this and why the TIMESTAMPDIFF is much more accurate. The DATEDIFF can be deceiving when I put in full date times, but I do not want to confuse you. The DATEDIFF could not care less about the time string (ex: “10:59:10”), in fact you should remove them to remind yourself of the difference. MySQL looks at our 2 dates and when it compares them it sees that we have the same year and month. However, the days are different so it subtracts the second date’s day from the first date’s day (17-14), which gives us 3. Now, 3*24*60*60 = 259200 like any good mathematician would tell you. That number is the difference of days in seconds, but not the difference of seconds between the two datetimes. The TIMESTAMPDIFF on the other hand can do a datetime second comparison:

MySQL TIMESTAMPDIFF Function

Example
SELECT TIMESTAMPDIFF(SECOND,’2013-01-14 10:59:10′, ‘2013-01-17 11:50:34’);
#262284

The result, 262284, is the real seconds between the two dates. Of course, raw seconds makes it more difficult to compute days, weeks, or even years, but you will have a more accurate answer if you put in the work. The extra work is the very reason why you see many DATEDIFFs being used when TIMESTAMPDIFF should be used.

You should definitely see the difference between the arguments of DATEDIFF vs TIMESTAMPDIFF as they are opposite. I know I was a little sneaky flipping them without telling you, but the comparison is reversed between the functions. I have no idea why they are reversed, but the creators of MySQL must have felt it was important. Personally, I like the order of the DATEDIFF’s arguments as difference is the first number subtracted from the second, but who I am to judge?

I hope this helps clear up how to compare dates accurately in MySQL.

Rounding Numbers

MySQL gives us four functions to round numbers: CEILING, FLOOR, ROUND, and TRUNCATE. These four functions all have different purposes, but together, they can round a number any way you would like. You might already know the definition of these words from your math classes, but you most likely have no idea what it means to truncate. I will discuss them all in detail so that you can take these concepts with you into other languages, as each language has similar functions. We will not need any tables or additional work as MySQL allows us to input integers into our functions.

The creators long ago thought of rounding numbers in a metaphorical way. The ceiling would be the highest of the room and the floor would be the lowest of the room. Why they did not use up and down is beyond my knowledge, but just know that the ceiling is up and the floor is down like you would expect.

Rounding numbers with MySQL

SQL CEILING Function

Example
SELECT CEILING( 1 ); /* returns 1 */
SELECT CEILING( 1.6 ); /* returns 2 */
SELECT CEILING( 1.4 ); /* returns 2 */

The CEILING function rounds the decimal up to the next integer. The decimal is irrelevant in this function as it is always eliminated when the number rounds up to an integer. Of course, this means that you will lose your decimal places.

SQL FLOOR Function

Example
SELECT FLOOR( 1 ); /* returns 1 */
SELECT FLOOR( 2.1 ); /* returns 2 */
SELECT FLOOR( 2.9 ); /* returns 2 */

If CEILING is up, then the FLOOR function is the same but only the number is rounded down. Again, you will lose the decimal place and an integer will be displayed. The decimals are essentially truncated from the number. Truncated simply means to be cut off.

SQL ROUND Function

Example
SELECT ROUND( 1 ); /* returns 1 */
SELECT ROUND( 1.5 ); /* returns 2 */
SELECT ROUND( 1.4635, 1 ); /* returns 1.5 */

The ROUND function you probably already understand from your math classes. You might have known CEILING and FLOOR, but ROUND is by far the most common. Rounding just means to round up from 5 or down from anything less. ROUND is unique because you can tell SQL which position you would like rounded. In our third statement, we have SQL round to the first decimal by placing 1 as our second argument to the ROUND function.

SQL TRUNCATE Function

Example
SELECT TRUNCATE( 1, 0 ); /* returns 1 */
SELECT TRUNCATE( 1.699, 1 ); /* returns 1.6 */
SELECT TRUNCATE( 185, -1 ); /* returns 180 */

The TRUNCATE function is not very common to use. Basically, it strips characters from your number. Why would you want to lose information about your number? There are a few reasons, but you will find those later in your programming career, but we should talk about why it works. You provide the value in the first argument and the position you want to truncate to in the second argument. You can see in the example that our third SQL statement has a negative one. It returns 180 because we truncated the 5, but we put the placeholder 0 in there so the numbers in the integer keep their position.