The SQL JOIN Clause
Make Like a Server and Join Those Tables
SQL is an essential language to know. In my last blog I discussed some basic SELECT queries in order to illustrate how straightforward it can be to get started. The syntax for SQL can be slightly different depending on the program being used but overall it isn’t too hard to see what the (basic) statement is doing. The first SQL statement to make me stop and really think was the JOIN statement. Let’s take a look at why they are used and how they work.
What is a JOIN?
A JOIN clause is used to combine rows from two or more tables based on a column they share. In this example we will look at a Colonial Marines Table and Guns Table:
Notice both tables have the column MarineID. In the Guns Table, each gun has its own ID and also the ID of the marine it belongs to. These two tables are joined together by this column.
Say we want to write a statement that will return values from both tables.
SELECT Guns.GunsID, Marines.MarineName, Guns.GunName
FROM Guns
INNER JOIN Marines ON Guns.MarineID=Marines.MarineID;Let’s break this down line by line
Line1: Select GunsID, MarineName, and GunName data from their respective columns (I picked these values randomly, try switching field names around to see what happens).
Line2: From the Guns Table
Line3: (Inner) Join Marines to Guns on the MarineID field for each table.
Here is what this statement returns:
Notice that we used the clause INNER JOIN on Line3. What does that mean?
Types of Joins
In the previous example, we used an INNER JOIN to return records associated with both tables. There are other types of join we can use.
Inner Join — Creates a new table by combining rows that have matching values in two or more tables. This is the most common type of JOIN. If no JOIN is specified, INNER JOIN is the default.
Outer Join — Or Full Outer Join is used to return all rows when there is a match in the left or right record. Beware this can lead to massive return results.
Left Join — Returns all records from the left table and the matched records from the right table.
Right Join — Returns all records from the right table and the matched records from the left table.
— NOTE — When using left, right, and outer joins all records will be returned even if they have no corresponding match. Will return as NULL value.
If You Can’t Beat Em
The JOIN statement is excellent practice for someone new to SQL. It will give you experience with how tables are related and how the desired data can be returned in an efficient manner. I highly recommend playing around with these concepts to get comfortable with how database queries can be quite elegant. A great program to start with is SQLFiddle http://sqlfiddle.com/. You can start typing SQL immediately and learning.
Happy Coding!
