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.
Please help us spread the word by socializing it today!
Did you find something wrong with the information on this page? Please take a moment to report it to us
so that we can continue to improve the quality of the information on this site. Click here to
report an issue with this page.
Recommended Books & Training Resources
