SQL FULL JOIN |
SQL Sample Code - SQL FULL JOIN |
| SELECT column_name(s) FROM table_name1 FULL JOIN table_name2 ON table_name1.column_name=table_name2.column_name….; |
See the SQL samples section below for an explanation of FULL JOIN.
What does SQL FULL 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’.
A FULL JOIN will join two tables but will include all rows from both tables specified in the FROM and FULL JOIN element of the Select statement (table_name1 and table_name2), regardless of whether there is a match in the other table’s column.
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 FULL JOIN |
In this SQL sample we will use the database tables ‘tblCompany’ and 'tblStaff'.
| Company_ID | Company_Name | 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 |
| 5 | MySQL Code Geeks | 5 Sample Ave | Las Vegas |
| 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 | 32 | F |
In this example we want to retrieve all the Staff in the tblStaff table all the Company Names. 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 FULL JOIN tblStaff ON tblCompany.Company_ID=tblStaff.Company_ID; |
The table of results will look like this:
| Staff_Forename | Staff_Surname | Company_Name |
| Joe | Sample | SQL Sample |
| Fred | Code | Sandwich Land |
| Ben | SQL | SQL Code Land |
| Frederica | Reference | Sample Code World |
| Barbara | Sample | |
| MySQL Code Geeks |
Note: Barbara Sample has appeared in the results even though a company does not exist with a Primary Key (Company_ID) value of ‘5’. The Veggie Vegas company record has also appeared even though no Staff in the tblStaff table are related to this particular company.
Related SQL Sample Code:
|
|