Please visit our sponsors !

SQL Join

Joins and Keys

Sometimes we have to select data from two tables to make our result complete. We have to perform a join.

Tables in a database can be related to each other with keys. A primary key is a column with a unique value for each row. The purpose is to bind data together, across tables, without repeating all of the data in every table.

In the "Employees" table below, the "Employee_ID" column is the primary key, meaning that no two rows can have the same Employee_ID. The Employee_ID distinguishes two persons even if they have the same name.

When you look at the example tables below, notice that: 

• The "Employee_ID" column is the primary key of the "Employees" table
• The "Prod_ID" column is the primary key of the "Orders" table
• The "Employee_ID" column in the "Orders" table is used to refer to the persons in the "Employees" table without using their names

Employees:

Orders:

Referring to Two Tables

We can select data from two tables by referring to two tables, like this:

Example

Who has ordered a product, and what did they order?

SELECT Employees.Name, Orders.Product
FROM Employees, Orders
WHERE Employees.Employee_ID=Orders.Employee_ID

Result

Example

Who ordered a printer?

SELECT Employees.Name
FROM Employees, Orders
WHERE Employees.Employee_ID=Orders.Employee_ID
AND Orders.Product='Printer'

Result

Using Joins

OR we can select data from two tables with the JOIN keyword, like this:

Example INNER JOIN

Syntax

SELECT field1, field2, field3
FROM first_table
INNER JOIN second_table
ON first_table.keyfield = second_table.foreign_keyfield

Who has ordered a product, and what did they order?

SELECT Employees.Name, Orders.Product
FROM Employees
INNER JOIN Orders
ON Employees.Employee_ID=Orders.Employee_ID

The INNER JOIN returns all rows from both tables where there is a match. If there are rows in Employees that do not have matches in Orders, those rows will not be listed.

Result

Example LEFT JOIN

Syntax

SELECT field1, field2, field3
FROM first_table
LEFT JOIN second_table
ON first_table.keyfield = second_table.foreign_keyfield

List all employees, and their orders - if any.

SELECT Employees.Name, Orders.Product
FROM Employees
LEFT JOIN Orders
ON Employees.Employee_ID=Orders.Employee_ID

The LEFT JOIN returns all the rows from the first table (Employees), even if there are no matches in the second table (Orders). If there are rows in Employees that do not have matches in Orders, those rows also will be listed.

Result

Example RIGHT JOIN

Syntax

SELECT field1, field2, field3
FROM first_table
RIGHT JOIN second_table
ON first_table.keyfield = second_table.foreign_keyfield

List all orders, and who has ordered - if any.

SELECT Employees.Name, Orders.Product
FROM Employees
RIGHT JOIN Orders
ON Employees.Employee_ID=Orders.Employee_ID

The RIGHT JOIN returns all the rows from the second table (Orders), even if there are no matches in the first table (Employee). If there had been any rows in Orders that did not have matches in Employees, those rows also would have been listed.

Result

Example

Who ordered a printer?

SELECT Employees.Name
FROM Employees
INNER JOIN Orders
ON Employees.Employee_ID=Orders.Employee_ID
WHERE Orders.Product = 'Printer'

Result

Jump to: Top of Page or HOME or Printer friendly page

Search W3Schools:

What Others Say About Us

Does the world know about us? Check out these places:

Dogpile Alta Vista MSN Google Excite Lycos Yahoo Ask Jeeves

We Help You For Free. You Can Help Us!

• Tell your newsgroup or mailing list
• Link to us from your pages
• Help us correct errors and broken links
• Help us with spelling and grammar
• Validate the XHTML code of this page

W3Schools is for training only. We do not warrant its correctness or its fitness for use. The risk of using it remains entirely with the user. While using this site, you agree to have read and accepted our terms of use and privacy policy.

Copyright 1999-2002 by Refsnes Data. All Rights Reserved

How we converted to XHTML