SQL joins are used to query data from two or more tables and or views and are based on a relationship between certain fields. Tables in a database are often related to each other with keys.
A primary key is one or more columns with a unique value for each row. Each primary key value must be unique within the table. The purpose of the join is to bind data together without repeating
all of the data in every table. This is part of the overall goal of normalizing data.
Employees Table
| employeeID | employeeName |
|---|
| 1000 | John Smith |
| 1001 | Fred White |
| 1002 | Jane Scott |
| 1003 | Samuel Williams |
Departments Table
| deptName | employeeID |
|---|
| Sales | 1000 |
| HR | 1002 |
| Accounting | 1003 |
| Operations | 1004 |
In this example, employeeID is a primary key in the table called "Employees". You'll notice that employeeID also exists in the "Departments" table, but as a foreign key.
Using an SQL Join, we can combine these two tables into one when presenting the results of a query. The following list are the most common types of Joins. There are some other variations such as the equi-join, natural join, and cross join that will be covered seperately.
Types of Joins
| INNER JOIN: | Returns rows when there is at least one match in both tables |
| LEFT JOIN: | Returns all rows from the left table, even if there are no matches in the right table |
| RIGHT JOIN: | Returns all rows from the right table, even if there are no matches in the left table |
| FULL JOIN: | Returns rows when there is a match in one of the tables |
Design Example
In this example, using an inner join, we can build a query to display the department name and employee name even though neither table has this information on its own. The two tables are joined by the 'employeeID' field.
We can use the INNER JOIN to bind this data together.
Did you find the page informational and useful? Share it using one of your favorite social sites.
Recommended Books & Training Resources
