SQL: Relating Data using Inner Join
Goal: Find records which has associated data in both Table A (Names) and Table B (Phone Numbers)
Prerequisites: 2 Tables with relational data, Ability to run SQL Queries
Many database designs incorporate relationships between tables. One of the most useful things with the SQL syntax is the ability to link items found in both tables using an INNER JOIN query. Below are my sample tables:
ID Name -- ---- 0 Joe 1 Chris 2 Mason
ID Extension -- --------- 0 x0001 0 x0101 2 x3121
Now in this example "Joe" (user ID 0) has 2 phone extensions, Chris has no extensions, and Mason has 1 extension. So lets say we want to get all of that in a table so that we can use it later to display it on our webpage. The following query will pull all users who have an extension and their extensions, and leave off any user who does not.
SELECT * FROM Names INNER JOIN Phones ON Names.ID=Phones.ID
And the result of that query would look something like this:
ID Name Extension -- ----- -------- 0 Joe x0001 0 Joe x0101 2 Mason x3121
As you can see it left chris out of this altogether because he did not have any matching criteria in both tables for what we did our INNER JOIN on. If we wanted to list chris in this we would simply need to use an outer join instead of an inner join and then in the Phone Extension it would just be null.