Presents your 
SQL SERVER E-NEWSLETTER for February 4, 2003

<------------------------------------------->

MERGE TABLES WITH INNER JOINS

An inner left join compares one set against another set that has
matching values. The values from the set left of the join keyword will be
returned before the values from the set right of the JOIN keyword. So if you
use an outer left join, SQL Server will return everything that equals the
two sets, plus everything in the set left of the join keyword.

Here is a code example:

SELECT * FROM TABLE_A a LEFT OUTER JOIN TABLE_B b ON a.pkey1 = b.pkey1

The results of this code would be everything that has matching values
between TABLE_A and TABLE_B, along with the remaining values in TABLE_A. If
this were a right outer join, the results would be vice versa. A full
outer join would return all the values that match between the two sets,
along with all the values from each set.

Joins aren't prevented because there is no foreign key referential
integrity. You can take two dissimilar sets and compare them, which means you
should know what data you're comparing before you attempt joins.

If you have inherited a legacy database and you're researching the data,
you may find data that have different column names, but the values
within the columns look the same. These two columns would be probable
candidates in joining. This type of scenario involves data analysis, 
and joins are key to understanding the data within the database.

The following is a script to demonstrate the differences between the
different types of joins:

SET NOCOUNT ON
GO
CREATE TABLE T1
(PKEY1 INT IDENTITY ( 10, 10 ) NOT NULL
  CONSTRAINT PK_T1 PRIMARY KEY,
COL1 CHAR NULL)
GO
CREATE TABLE T2
(PKEY1 INT IDENTITY NOT NULL
  CONSTRAINT PK_T2 PRIMARY KEY,
COL1 INT NULL)
GO

INSERT T1 (COL1) VALUES ( '1' )
GO
INSERT T1 (COL1) VALUES ( '2' )
GO
INSERT T1 (COL1) VALUES ( '3' )
GO


INSERT T2 (COL1) VALUES ( 392 )
GO
INSERT T2 (COL1) VALUES ( 74 )
GO
INSERT T2 (COL1) VALUES ( 74 )
GO
INSERT T2 (COL1) VALUES ( 74 )
GO
DELETE T2 WHERE PKEY1 = 3
GO

PRINT REPLICATE(CHAR(10),3) + REPLICATE ('#', 10) +
     ' LEFT INNER JOIN ' + CHAR(10)
GO
SELECT *
FROM T1
JOIN T2 ON CONVERT(INT,T1.COL1) = T2.PKEY1
GO

PRINT REPLICATE(CHAR(10),3) + REPLICATE ('#', 10) +
     ' LEFT OUTER JOIN ' + CHAR(10)
GO
SELECT *
FROM T1 a
LEFT OUTER JOIN T2 b ON CONVERT(INT, a.COL1) = b.PKEY1
GO

PRINT REPLICATE(CHAR(10),3) + REPLICATE ('#', 10) +
     ' RIGHT OUTER JOIN ' + CHAR(10)
GO
SELECT *
FROM T1 a
RIGHT OUTER JOIN T2 b ON CONVERT(INT, a.COL1) = b.PKEY1
GO

PRINT REPLICATE(CHAR(10),3) + REPLICATE ('#', 10) +
     ' FULL OUTER JOIN ' + CHAR(10)
GO
SELECT *
FROM T1 a
FULL OUTER JOIN T2 b ON CONVERT(INT, a.COL1) = b.PKEY1
GO

DROP TABLE T1,T2
GO

----------------------------------------