Very easy once you get it. The INNER JOIN allows you to pull whatever columns you want from multiple tables that have at least one common value.
Let’s take two tables to get the idea:
- ACCOUNT table will hold basic customer info
- SALES table will hold sales info. (let’s say we sell software and some customers could buy more, and some don’t register them all for some reason)
Let’s talk about scenarios now:
INNER JOIN allows you to take any column from more than one table and output into one spreadsheet of data.
SCENARIO: I want to see who could buy more licenses?
SELECT ac.first_name, ac.last_name, ac.customer_id, sa.licensed, sa.potential, ac.did_home, ac.did_cell
FROM account ac
INNER JOIN sales sa
ON ac.customer_id = sa.customer_id
WHERE sa.potential > sa.licensed;
|Let’s break down what we did here:|
|SELECT: In this line you list all the columns from the two tables you want in your output. You’ll notice in front of the first_name is “ac.” which is another name for the table you’re pulling from. You can use account.first_name or in give it a new tag name to shorten it up – in this case I use “ac”. How is it identified?|
|FROM: Here we name the table to look in – but we also give it a tag name. The tag can be nearly anything you want – but stay away from variable names like “ON” “IN” etc… So in this case I make account = ac.|
|INNER JOIN: This is the call to another table and again give it a tag to speed up typing.|
|ON: This clause now bridges the connection between the two tables. What is the common column? In the above case “customer_id”. Using the “=” between the two.|
|WHERE: Here you can do whatever you want, using all the known variants “> < = like etc…”|
In my example above I want to sell more licenses. So who are my potentials?
If I want to find out who didn’t register all their licenses – I can change the WHERE to be:
WHERE sa.licensed < sa.registered;
The nice thing about INNER JOIN is you can add in more tables by repeating the INNER JOIN / ON