Joining Tables and Views

Friday, November 18, 2011

tags SQL

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

1000John Smith
1001Fred White
1002Jane Scott
1003Samuel Williams

Departments Table


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.

sql join

Did you find the page informational and useful? Share it using one of your favorite social sites.

Recommended Books & Training Resources

Head First SQL: Your Brain on SQL A Learners Guide SQL Cookbook