Tutorial:
How do we join tables in SQL?
Introduction
In this tutorial I will explain the basic of how to join tables in a query so that data can be retrieved from multiple tables.
Selecting
data from multiple tables in one advantage that we can have.
Without this advantage or capability the entire relational database concept
would not be possible. A join combines
two or more tables to retrieve data from multiple tables
The primary keys are an
important factor to join tables, these tables have a relationship of the
primary keys, this help to relate and select the desired data.
Types of Joins
EQUIJOINS
NATURAL JOINS
NON-EQUIJOINS
OUTER JOINS
SELF JOINS
Join Conditions
The SELECT and
FROM
clauses are both required SQL statement elements; the WHERE clause
is a required element of a SQL statement when joining tables. The tables
being joined are listed in the FROM clause. The join is performed in the WHERE
clause. Several operators can be used to join tables, such as =, <, >, <>, <=, >=, !=, BETWEEN, LIKE,
and NOT;
they can all be used to join tables. However, the most common operator
is the equal symbol (=).
SELECT
FROM
WHERE
AND
BETWEEN
LIKE
NOT
=
!=
<
<=
>
>=
<>
EQUIJOINS
The most important and used
join is the EQUIJOINS or JOIN OF EQUITY; it is also called INNER JOIN. The EQUIJOIN
joins two tables with a common column in which each is usually the primary key.
The syntax for an EQUIJOIN is:
SELECT TABLE_NAME1.COLUMN1, TABLE_NAME2.COLUMN2...
FROM TABLE_NAME1, TABLE_NAME2, TABLE_NAME3
WHERE TABLE_NAME1.COLUMN_NAME = TABLE_NAME2.COLUMN_NAME
AND TABLE_NAME1.COLUMN_NAME = TABLE_NAME3.COLUMN_NAME
________________________________________________________ Example SELECT EMPLOYEE_TBL.EMP_ID,
EMPLOYEE_PAY_TBL.DATE_HIRE
FROM EMPLOYEE_TBL,
EMPLOYEE_PAY_TBL,
WHERE EMPLOYEE_TBL.EMP_ID = EMPLOYEE_PAY_TBL.EMP_ID;
Notice that each column in
the SELECT
clause is preceded by the associated table name in order to identify each
column. This is called qualifying columns in a query.
Qualifying columns is only necessary for columns that exist in more than one
table referenced by a query. You usually qualify all columns for consistency
and to avoid any questions when debugging or modifying SQL code.
NATURAL JOINS
Natural Joins are almost
the same as the Equijoin but the natural joins differs from the equijoin by
eliminating or remove duplication of columns in the joining columns. The join
condition is the same but the columns selected are different.
The syntax for a NATURAL
JOIN is:
SELECT TABLE_NAME1.*, TABLE_NAME2.COLUMN_NAME
TABLE_NAME3.COLUMN_NAME
FROM TABLE_NAME1, TABLE_NAME2, TABLE_NAME3
WHERE TABLE_NAME1.COLUMN_NAME = TABLE_NAME2.COLUMN_NAME
AND TABLE_NAME1.COLUMN_NAME = TABLE_NAME3.COLUMN
Asterisk (*) represents all columns of a table
______________________________________________________________
Example:
SELECT EMPLOYEE_TBL.*, EMPLOYEE_PAY_TBL.SALARY
FROM EMPLOYEE_TBL,
EMPLOYEE_PAY_TBL
WHERE EMPLOYEE_TBL.EMP_ID = EMPLOYEE_PAY_TBL.EMP_ID;
This SQL statement returns
all columns from EMPLOYEE_TBL and SALARY from the EMPLOYEE_PAY_TBL.
The EMP_ID is
in both tables, but is retrieved only from the EMPLOYEE_TBL
because both contain the same information and do not need to be selected.
NON-EQUIJOINS
Joins two or more tables
based on a specified column value not equaling a specified column value in
another table, also called NON-EQUITY JOINS.
Syntax of NON_EQUIJOINS:
FROM TABLE_NAME1, TABLE_NAME2
WHERE TABLE_NAME1.COLUMN_NAME != TABLE_NAME2.COLUMN_NAME
AND TABLE_NAME1.COLUMN_NAME != TABLE_NAME2.COLUMN_NAME
_________________________________________________________________
Example:
SELECT EMPLOYEE_TBL.EMP_ID, EMPLOYEE_PAY_TBL.DATE_HIRE
FROM EMPLOYEE_TBL,
EMPLOYEE_PAY_TBL
WHERE EMPLOYEE_TBL.EMP_ID != EMPLOYEE_PAY_TBL.EMP_ID;
SELECT E.EMP_ID, E.LAST_NAME, P.POSITION
FROM EMPLOYEE_TBL E,
EMPLOYEE_PAY_TBL P
WHERE E.EMP_ID <> P.EMP_ID;
When using NON-EQUIJOINs,
you may receive several rows of data that are of no use to you. Check your
results carefully.
OUTER JOINS
An OUTER JOIN is
used to return all rows that exist in one table, even though corresponding rows
do not exist in the joined table. The (+) symbol is used to denote an OUTER JOIN in
a query. The (+) is
placed at the end of the table name in the WHERE clause. The table with the
(+)
should be the table that does not have matching rows. In many implementations,
the OUTER JOIN is
broken down into joins called LEFT OUTER JOIN, RIGHT OUTER JOIN,
and FULL OUTER JOIN.
The OUTER JOIN in
these implementations is normally optional.
NOTE
You must check your particular
implementation for exact usage and syntax of the OUTER JOIN.
The (+)
symbol is used by some major implementations, but is non-standard.
The general syntax is
FROM TABLE_NAME1{RIGHT | LEFT | FULL} [OUTER] JOINON TABLE2
________________________________________________________________
The Oracle syntax is
FROM TABLE_NAME1, TABLE_NAME2, TABLE_NAME3
WHERE TABLE_NAME1.COLUMN_NAME[(+)] = TABLE_NAME2.COLUMN_NAME[(+)]
AND TABLE_NAME1.COLUMN_NAME[(+)] = TABLE_NAME3.COLUMN_NAME[(+)]
NOTE
The OUTER JOIN can only be used on one
side of a join condition; however, you can use an OUTER JOIN on
more than one column of the same table in the join condition.
The concept of the OUTER JOIN is
explained in the next two examples. In the first example, the product
description and the quantity ordered are selected; both values are extracted
from two separate tables. One important factor to keep in mind is that there
may not be a corresponding record in the ORDERS_TBL table for every product. A
regular join of equality is performed:
SELECT P.PROD_DESC, O.QTY
FROM PRODUCTS_TBL P,
ORDERS_TBL O
WHERE P.PROD_ID = O.PROD_ID;
PROD_DESC QTY-------------------------------- ---WITCHES COSTUME 1PLASTIC PUMPKIN 18 INCH 25PLASTIC PUMPKIN 18 INCH 2LIGHTED LANTERNS 10FALSE PARAFFIN TEETH 20KEY CHAIN 1 6 rows selected.
Only six rows were
selected, but there are 10 distinct products. You want to display all products,
whether the products have been placed on order or not.
The next example
accomplishes the desired output through the use of an OUTER JOIN.
Oracle's syntax is used for the OUTER JOIN.
SELECT P.PROD_DESC, O.QTY
FROM PRODUCTS_TBL P,
ORDERS_TBL O
WHERE P.PROD_ID = O.PROD_ID(+);
PROD_DESC QTY-------------------------------- ---WITCHES COSTUME 1ASSORTED MASKSFALSE PARAFFIN TEETH 20ASSORTED COSTUMESPLASTIC PUMPKIN 18 INCH 25PLASTIC PUMPKIN 18 INCH 2PUMPKIN CANDYPLASTIC SPIDERSCANDY CORNLIGHTED LANTERNS 10KEY CHAIN 1OAK BOOKSHELF 12 rows selected.
All products were returned
by the query, even though they may not have had a quantity ordered. The outer
join is inclusive of all rows of data in the PRODUCTS_TBL table, whether a
corresponding row exists in the ORDERS_TBL table or not.
SELF JOINS
The SELF JOIN is
used to join a table to itself, as if the table were two tables, temporarily
renaming at least one table in the SQL statement. The syntax is as follows:
SELECT A.COLUMN_NAME, B.COLUMN_NAME, C.COLUMN_NAME
FROM TABLE1 A, TABLE2 B, TABLE3 C
WHERE A.COLUMN_NAME = B.COLUMN_NAME
AND A.COLUMN_NAME = C.COLUMN_NAME
_________________________________________________________________
The following is an
example:
SELECT A.LAST_NAME, B.LAST_NAME, A.FIRST_NAME
FROM EMPLOYEE_TBL A,
EMPLOYEE_TBL B
WHERE A.LAST_NAME = B.LAST_NAME;
The preceding SQL statement
returns the employees' first name for all the employees with the same last name
from the EMPLOYEE_TBL.
Self joins are useful when all of the data you want to retrieve resides in one
table, but you must somehow compare records in the table to other records in
the table.
REFERENCES:
On-line:
1.- INFORMIT- web address http://www.informit.com/content/index.asp?product_id={0DC36111-55AC-4058-A401-3ADA0BB75725}
2.- SQL Tutorial- web address http://www.baycongroup.com/tocsql.htm
3.- SQL Join – web address http://www.w3schools.com/sql/sql_join.asp