A SQL JOIN clause combines rows from tables, based on a field shared between them (often a joining or ID field). There are four types of JOINs:
- INNER JOIN: Show rows when there’s a match in BOTH tables
- LEFT JOIN: Show rows in the left table with the rows that match up from the right table
- RIGHT JOIN: Show rows in the right table with rows that match up in the left table
- FULL JOIN: Show rows with a match in at least one table
In this article, we’ll use the same “Customers” table from our first articles:
ID Site Contact Address City Zip Country
1 Krypted Charles Edge my house Minneapolis 55418 US
2 Apple Tim Cook spaceship Cupertino 95014 US
3 Microsoft Satya Nadella campus Redmond 98053 US
4 Facebook Mark Zuckerberg foodhall Menlo Park 94025 US
5 JAMF Dean Hager Grain Exchange Minneapolis 55418 US
Let’s look at a selection from the “IPs” table:
IPID SiteID IP
101 1 202.76.2.111
102 2 172.67.33.90
103 3 44.75.32.12
104 4 44.75.32.13
105 5 44.75.32.13
In the above two tables, each has their own ID field, with ID being the Customers table ID and IPID being the ID of an IP address. We have a series of IPs for SiteIDs 3-5, with 104 and 105 being the same IP address. Note that the IPs are fake. I made ’em up. Sorry about not using production data, but it didn’t make that much sense…
Here, we’ll use a SELECT and identify the fields, defining the table followed by a dot and then the field name as the data to load into memory. We’ll pull that from our initial table and use an INNER JOIN with the second, using an ON to define which field is used to map out results:
SELECT IPs.IPID, Customers.ID, IPs.IP
FROM IPs
INNER JOIN Customers
ON IPs.IPID=Customers.ID;
We’ll then get something like this, with the three fields that we defined in the SELECT as the columns to display in the output:
IPID ID IP
101 1 202.76.2.111
102 2 172.67.33.90
103 3 44.75.32.12
104 4 44.75.32.13
105 5 44.75.32.13
This is a pretty basic JOIN. But shows the power we have. We can then use a LEFT JOIN alongside an ORDER BY:
SELECT Customers.Site, IPs.IP
FROM Customers
LEFT JOIN IPs
ON Customers.ID=IPs.IPID
ORDER BY Customers.Site;
The output would then appear as follows:
Site IP
Apple 172.67.33.90
Facebook 44.75.32.13
JAMF 44.75.32.13
Krypted 202.76.2.111
Microsoft 44.75.32.12
The different types of JOINs give you the ability to string together some pretty awesome logic, to locate assets in multiple tables and display them. For example, let’s say I also had a Registrar table. I could then also use that ID attribute from our original database as a key to access data connected to our initial table from other tables. Overall, simple and straight forward means of querying data and displaying those results, or leveraging them into a variable or file in order to make use of them elsewhere (e.g. with a script).