SQL LEFT JOIN or LEFT OUTER JOIN |
SQL Sample Code - SQL LEFT JOIN or LEFT OUTER JOIN |
SQL LEFT JOIN is exactly the same as LEFT OUTER JOIN. Certain databases prefer one than the other.
LEFT JOIN
| SELECT column_name(s) FROM table_name1 LEFT JOIN table_name2 ON table_name1.column_name=table_name2.column_name….; |
LEFT OUTER JOIN
| SELECT column_name(s) FROM table_name1 LEFT OUTER JOIN table_name2 ON table_name1.column_name=table_name2.column_name….; |
See the SQL samples section below for an explanation of LEFT JOIN.
What does SQL LEFT 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 LEFT JOIN will join two tables but will include all rows from the table specified by the FROM element of the Select statement (table_name1), regardless of whether there is a match in the other tables 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 LEFT 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 |
| 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 retrieve all the Staff in the tblStaff table and also the Company for which they are 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 tblStaff LEFT JOIN tblCompany ON tblStaff.Company_ID=tblCompany.Company_ID; |
The table of results will look like this:
| Staff_Forename | Staff_Surname | Company_Name |
| Joe | Sample | Burger Town |
| Fred | Code | Sandwich Land |
| Ben | SQL | Curry World |
| Frederica | Reference | Burger Town |
| Barbara | Sample |
Note that Barbara Sample has appeared in the results even though a company does not exist with a Primary Key (Company_ID) value of ‘5’.
Related SQL Sample Code:
|
|