Data Correlation---All Join Types Explanation
ragav...@gmail.com
*******************
Data Correlation
*******************
In this section you will look at implementing joins to retrieve data
from two or more tables. The results will appear as a single table with
columns from all the tables specified in the SELECT column_list and
meeting the search criteria. You will look at how to implement joins
using both ANSI and SQL Server syntax, and then at the different types
of joins: inner joins, cross joins, outer joins, and self joins.
Implementing Joins
**********************
To join tables, you must make a comparison of one or more columns from
a table to one or more columns in one or more tables. The result of the
comparison produces new rows by combining the columns in the SELECT
column_list from the joined tables that meet the join conditions. When
you join tables you can use either ANSI or SQL Server join syntax. The
syntax for both ANSI and SQL Server syntax are as follows.
ANSI Join Syntax
*******************
SELECT table_name.column_name, [...]
FROM { table_name [join_type] JOIN table_name
ON search_criteria} , [...]
WHERE search_criteria
The join statements for the ANSI syntax show up in the FROM clause of
the SELECT statement. The WHERE clause selects rows from the joined
rows to be returned. There are three types of ANSI join statements you
can choose: INNER JOIN, OUTER JOIN, and CROSS JOIN.
SQL Server Join Syntax
****************************
SELECT table_name.column_name, [...]
FROM table_list
WHERE table_name.column_name
join_operator table_name.column_name, [...]
In SQL Server syntax, the FROM clause lists the tables involved in the
join. The WHERE clause includes the columns to be joined and can
include additional search criteria that determine the rows to be
returned. The join operators for SQL Server syntax are the following:
=, >, <, >=, <=, <>, !>, and !<.
Inner Joins
************
Joins connect two tables based on a join condition producing results as
a new table, with the rows that satisfy the join condition. Inner joins
produce information when matching information is found in both tables.
The most common types of inner joins are equijoins and natural joins.
In an equijoin, column values are compared for equality, and redundant
columns are displayed as columns in the result set; whereas in a
natural join, the redundant columns are not displayed twice.
Take a look at the following example to clarify what was just said.
SQL Server Syntax
**********************
SELECT *
FROM publishers, pub_info
WHERE publishers.pub_id = pub_info.pub_id
ANSI Syntax
SELECT *
FROM publishers
INNER JOIN pub_info ON publishers.pub_id = pub_info.pub_id
pub_id pub_name city state country pub_id logo
pr_info
---- -------- -------- ---- ---- ---- --------
736 New Moon Books Boston MA USA 736 NEWMOON.BMP New
Moon Books...
877 Binnet & Hardley Washington DC USA 877 BINNET.BMP
Binnet & Hardley...
1389 Algodata Infosystems Berkeley CA USA 1389
ALGODATA.BMP Algodata Infosystem
1622 Five Lakes Publishing Chicago IL USA 1622 5LAKES.BMP
Five Lakes Publishing
1756 Ramona Publishers Dallas TX USA 1756 RAMONA.BMP
Ramona Publishers
9901 GGG&G Mnchen NULL GER 9901 GGGG.BMP GGG&G...
9952 Scootney Books New York NY USA 9952 SCOOTNEY.BMP
Scootney Books...
9999 Lucerne Publishing Paris NULL FRA 9999 LUCERNE.BMP
Lucerne Publishing...
(8 row(s) affected)
In this example, the SELECT statement selects all columns from both the
publishers and pub_info tables when the pub_id columns for the joined
tables are equal. Note the redundant pub_id column.
Natural Joins
***************
In a natural join, column values are compared for equality, but
redundant columns are eliminated from the columns in the result set.
Here is an example of a natural join.
SQL Server Syntax
**********************
SELECT publishers.*, pub_info.logo, pub_info.pr_info
FROM publishers, pub_info
WHERE publishers.pub_id = pub_info.pub_id
ANSI Syntax
SELECT publishers.*, pub_info.logo, pub_info.pr_info
FROM publishers
INNER JOIN pub_info ON publishers.pub_id = pub_info.pub_id
pub_id pub_name city state country ...
---- -------- -------- ---- ---- ...
736 New Moon Books Boston MA USA ...
877 Binnet & Hardley Washington DC USA ...
1389 Algodata Infosystems Berkeley CA USA ...
1622 Five Lakes Publishing Chicago IL USA ...
1756 Ramona Publishers Dallas TX USA ...
9901 GGG&G Mnchen NULL GER ...
9952 Scootney Books New York NY USA ...
9999 Lucerne Publishing Paris NULL FRA ...
(8 row(s) affected)
In this example, the SELECT statement selects all columns from the
publishers table and all columns except pub_id from the pub_info table.
Cross or Unrestricted Joins
********************************
Cross or unrestricted joins return a combination of all rows of all
tables in the join as the result set. A cross or unrestricted join is
created not by using the WHERE clause in the SQL Server join of two or
more tables, but by using the CROSS JOIN keyword for the ANSI join.
Combining all rows from all tables involved in the join yields what is
known as a Cartesian product. In most cases, this type of result set is
unusable unless your intention is to find every possible combination,
such as some type of statistical or mathematical analysis. To put it
another way, if you look at each table as a matrix and then you
multiply the matrices, you get a new matrix with all combinations, as
shown in Figure 11.1. Each row from Table1 is added to each row in
Table2. If you add the number of columns from both tables, you get the
resulting number of columns. If you multiply the number of rows in
Table1 by the number of rows in Table2, you get the total number of
rows returned by your query.
Figure 11.1 Creating a Cartesian product.
Note
Tables cannot be joined on text or image columns. You can, however,
compare the lengths of text columns from two tables with a WHERE
clause, but you cannot compare actual data.
Here is an example of creating a Cartesian product using the CROSS JOIN
or unrestricted join. Say you want to list all book titles and their
authors' IDs, and you submit the following query:
SQL Server Syntax
***********************
SELECT titles.title, titleauthor.au_id
FROM titles, titleauthor
ANSI Syntax
SELECT titles.title, titleauthor.au_id
FROM titles CROSS JOIN titleauthor
title au_id
--------------- -----------
The Busy Executive's Database Guide 172-32-1176
The Busy Executive's Database Guide 213-46-8915
. .
. .
. .
Sushi, Anyone? 998-72-3567
Sushi, Anyone? 998-72-3567
(450 row(s) affected)
The results of the query you submitted yielded 450 rows. There are 18
rows in the titles table and 25 rows in the titleauthor table. Because
an unrestricted or cross join returns all possible combinations, you
get 18x25 = 450 rows-not quite the desired result, right?
To avoid submitting an unrestricted join, you should take the number of
tables you are joining and subtract 1. N - 1 will be the number of join
clauses needed, where N is the number of tables involved in the join
(that is, three tables, 3 - 1 = 2, two join clauses). The number of
join clauses might be more if you are joining based on a composite key.
Outer Joins
*************
You can restrict rows from one table while allowing all rows from
another table as your result set by using outer joins. One of the most
common uses for this type of join is to search for orphan records. The
outer join operators and keywords are as follows:
Caution
*********
In prior releases of SQL Server, you could use the following SQL Server
syntax:
*= includes all rows from the first table and only the matching rows in
the second table (left outer join).
=* includes all rows from the second table and only the matching rows
in the first table (right outer join).
Unfortunately, these operators are not always guaranteed to produce the
correct results. Problems can occur when NULL values are present.
Therefore, when using OUTER joins, you should always use the ANSI outer
join syntax.
ANSI Syntax:
LEFT OUTER JOIN Includes all rows from the first table and only the
matching rows in the second table
RIGHT OUTER JOIN Includes all rows from the second table and only the
matching rows in the first table
FULL OUTER JOIN Includes all nonmatching rows from both tables
You have a table of customers and a table with orders. These two tables
are related by a CustomerID field. With an equijoin or a natural join,
you only return records when the CustomerID field has a match in both
tables. Outer joins can be handy to get a customer list, and if a
customer happens to have an order, that order information will also
show up. If the customer does not have an order, the information from
the orders table will show up as (null).
If you do a left outer join on these tables and specify the customers
table first, the desired results will be returned. If you specify a
right outer join, your results show all orders; if an order happens to
have a CustomerID that does not match a CustomerID in the customers
table, the customer information will be (null). (If you follow the
rules of referential integrity, you should never have an order without
a valid CustomerID. If this is the case, then your right outer join
will have the same results as an equijoin or a natural join-all
orders and customers when there is a match on CustomerID.)
The left and right outer joins can return the same results depending on
the table order. For example, these two joins return the same
information:
Customers.CustomerID *= Orders.CustomerID
and
Orders.CustomerID =* Customers.CustomerID.
If you want to find all the titles, whether they happen to have sold
any copies, and the number of copies sold, you can submit the following
query.
SQL Server Syntax
SELECT titles.title_id, titles.title, sales.qty
FROM titles LEFT OUTER JOIN sales
ON titles.title_id = sales.title_id
title_id title qty
-------- ---------------------------------------
BU1032 The Busy Executive's D... 5
BU1032 The Busy Executive's D... 10
BU1111 Cooking with Computers... 25
BU2075 You Can Combat Compute... 35
BU7832 Straight Talk About Co... 15
MC2222 Silicon Valley Gastron... 10
MC3021 The Gourmet Microwave 25
MC3021 The Gourmet Microwave 15
MC3026 The Psychology of Comp... (null)
PC1035 But Is It User Friendl... 30
PC8888 Secrets of Silicon Val... 50
PC9999 Net Etiquette (null)
PS1372 Computer Phobic AND No... 20
PS2091 Is Anger the Enemy? 3
PS2091 Is Anger the Enemy? 75
PS2091 Is Anger the Enemy? 10
PS2091 Is Anger the Enemy? 20
PS2106 Life Without Fear 25
PS3333 Prolonged Data Deprivat... 15
PS7777 Emotional Security: A ... 25
TC3218 Onions, Leeks, and Gar... 40
TC4203 Fifty Years in Bucking... 20
TC7777 Sushi, Anyone? 20
(23 row(s) affected)
Self Joins
As the name suggests, a self join correlates rows of a table with other
rows in the same table. Comparison queries for the same information are
used the most for self joins. For example, if you want to list all
authors who live in the same city and zip code, you will compare city
and zip by executing the following query.
SQL Server Syntax
SELECT au1.au_fname, au1.au_lname,
au2.au_fname, au2.au_lname,
au1.city, au1.zip
FROM authors au1, authors au2
WHERE au1.city = au2.city
AND au1.zip = au2.zip
AND au1.au_id < au2.au_id
ORDER BY au1.city, au1.zip
ANSI Syntax
SELECT au1.au_fname, au1.au_lname,
au2.au_fname, au2.au_lname,
au1.city, au1.zip
FROM authors au1
INNER JOIN authors au2 ON au1.city = au2.city
AND au1.zip = au2.zip
WHERE au1.au_id < au2.au_id
ORDER BY au1.city, au1.zip
au_fname au_lname au_fname au_lname city zip
-------- -------- -------- -------- ------------ ---
Cheryl Carson Abraham Bennet Berkeley 94705
Dean Straight Dirk Stringer Oakland 94609
Dean Straight Livia Karsen Oakland 94609
Dirk Stringer Livia Karsen Oakland 94609
Ann Dull Sheryl Hunter Palo Alto 94301
Anne Ringer Albert Ringer Salt Lake City 84152
(6 row(s) affected)
Notice that when you perform a self join on a table, you create an
alias for the table name. You do this so that one table is treated
logically as two tables.
Tip
A table alias is useful any time you do a multitable join operation. It
allows you to create a more readable and shorter query statement
because you reference the table alias instead of the table name.
Regards,
V.R.Ragavan
Chennai