SQL RIGHT JOIN or RIGHT OUTER JOIN |
SQL Sample Code - SQL RIGHT JOIN or RIGHT OUTER JOIN |
SQL RIGHT JOIN is exactly the same as RIGHT OUTER JOIN. Certain databases prefer one than the other.
RIGHT JOIN
| SELECT column_name(s) FROM table_name1 RIGHT JOIN table_name2 ON table_name1.column_name=table_name2.column_name….; |
RIGHT OUTER JOIN
| SELECT column_name(s) FROM table_name1 RIGHT OUTER JOIN table_name2 ON table_name1.column_name=table_name2.column_name….; |
See the SQL samples section below for an explanation of RIGHT JOIN.
What does SQL RIGHT 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 RIGHT JOIN will join two tables but will include all rows from the table specified in the RIGHT JOIN element of the Select statement (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 RIGHT 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 tblCompany RIGHT 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 | SQL Code Land |
| Ben | SQL | Sample Code World |
| Frederica | Reference | SQL Sample |
| 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:
|
|