SQL INNER JOIN |
SQL Sample Code - SQL INNER JOIN or JOIN. |
SQL JOIN is exactly the same as INNER JOIN.
JOIN
| SELECT column_name(s) FROM table_names JOIN table_name ON table_name1.column_name=table_name2.column_name….; |
INNER JOIN
| SELECT column_name(s) FROM table_names INNER JOIN table_name ON table_name1.column_name=table_name2.column_name….; |
See the SQL samples section below for an explanation of INNER JOIN.
What does SQL INNER JOIN do? |
Very often we will need to draw together information from different database tables using SQL. Joins in SQL join two related database tables together using related columns as the link or ‘Relationship’. With an INNER JOIN both tables should have a common column from which INNER JOIN will match a common value for which the join can be attached. Joining the tables together will create one temporary table.
These common columns can have a ‘Relationship’ usually joined using a tables ‘Primary Key’ or ‘Foreign Key’. There are a number of different types of Joins; Inner Join, Left Join, Right Join, and Full Join.
Sample – SQL INNER JOIN |
In this SQL sample we will use the database table ‘tblCompany’. and 'tblStaff'
| Company_ID | CompanyName | Address | Town |
| 1 | SQL Sample | 1 Sample St | Hamburg |
| 2 | SQL Code Land | 2 Code Rd | Hamburg |
| 3 | Sample Code World | 66 SQL St | Curry |
| 4 | SQL Reference Ltd | 34 Reference St | Pisa |
| Staff_ID | Staff_Forename | Staff_Surname | Company_ID | Staff_Age | StaffGender |
| 1 | Joe | Sample | 1 | 21 | M |
| 2 | Fred | Code | 2 | 32 | M |
| 3 | Ben | SQL | 3 | 35 | F |
| 4 | Frederica | Reference | 1 | 21 | F |
| 5 | Barbara | Sample | 5 | 32 | F |
In this SQL sample we want to know which Company each of the staff members is an employee. We want to see the column Company Name column from the tblCompany table and the Forename and Surname from the tblStaff table.
The Company_ID column is the common column between the two tables which can be used for the Join. The Company_ID in the tblCompany table is the Primary Key and the Company_ID in the tblStaff table is the Foreign Key. This is where we will form a Join so that we can draw information from both tables
Use the SELECT statement below:
| SELECT tblStaff.Staff_Forename, tblStaff.Staff_Surname, tblCompany.Company_Name FROM tblCompany JOIN tblStaff ON tblCompany.Company_ID=tblStaff.Company_ID; |
Use the SELECT statement below:
The table of results will look like this:
| Staff_Forename | Staff_Surname | Company_Name |
| Joe | Sample | SQL Sample |
| Fred | Code | SQL Code Land |
| Ben | SQL | Sample Code World |
| Frederica | Reference | SQL Sample |
Note that Barbara Sample has not appeared in the results. This is because there is not a Company_ID that is equal to ‘5’ in the table 'tblCompany'.
Related SQL Sample Code:
|
|