Did you find this useful?
Socialize it today.


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


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

Departments Table

deptNameemployeeID
Sales1000
HR1002
Accounting1003
Operations1004


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

Please help us spread the word by socializing it today!

email contact us

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

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