Normalization is an art; it is the best thing that can happen to databases

.But it modularizes the data which means that we are not having all the data in

the same table. This indirectly means that, suppose I want a receipt and for

constructing that receipt I need data that is scattered across many a tables.

Then what do I do?

The answer to this is Joins. We will be briefly discussing the joins .Hope

somebody finds this article helpful. This article is specific to T-SQL.

Joins

A join consolidates the data in two tables into a single result set. The

tables aren't actually merged; they just appear to be in the rows returned by

the query. You can even join more than table at a time.

How do you join?

Well we perform join by merging two tables on the basis of one particular or

may be many columns having the same data respectively across the tables. It is

really mandatory that for joins there must be a common column with the same data

type otherwise join is not possible.

SELECT c.CustomerNumber, o.Amount FROM customers c, orders o WHERE c.CustomerNumber=o.CustomerNumber

The italicized part is the join condition or join criterion. When the join is

successful, data in the second table is combined with the first to form a

composite result set—a set of rows containing data from both tables. In short,

the two tables have a child, but one bigger than them itself.

There are basically two types of Joins:

1. Outer Joins
2. Inner Joins

The key difference between them is that outer joins include rows in the

result set even when the join condition isn't met, while an inner join doesn't.

When the join criteria in an outer join aren't met, columns in the first table

are returned normally, but columns from the second table are returned with no

value—as NULLs.

SELECT c.CustomerNumber, o.Amount FROM customers c JOIN orders o ON (c.CustomerNumber=o.CustomerNumber)

This is the other way of writing the join, a multilevel T-SQL join statement:

SELECT c.CustomerNumber, o.Amount, i.Description FROM customers c, orders o, items i WHERE c.CustomerNumber=o.CustomerNumber AND o.ItemNumber=i.ItemNumber

This query joins the composite of the customers table and the orders table

with the items table. Note that the exact ordering of the WHERE clause is

unimportant.

Note of Optimization

In order to allow servers to fully optimize queries, SQL requires that the

ordering of the predicates (the join statements or conditions or the joins ) in

a WHERE clause must not affect the result set. They must be associative—the

query must return the same result regardless of the order in which they're

processed.

But actually the order of the terms in the WHERE clause is significant when

constructing multilevel joins using the where clause syntax. That is why the

SQL-92 standard moved join construction to the FROM clause.

A multilevel syntax in the form of the “from clause”:

SELECT c.CustomerNumber, o.Amount, i.Description FROM customers c LEFT OUTER JOIN orders o ON
(c.CustomerNumber=o.CustomerNumber) LEFT OUTER JOIN items i ON (o.ItemNumber=i.ItemNumber)

Now just don’t worry about the LEFT OUTER JOIN . The explanation follows.

In a LEFT OUTER JOIN the resultset contains the data as the following that

columns in the first table are returned normally, but columns from the second

table are returned with no value—as NULLs for the values corresponding to the

ones returned in the columns of the first table.

The other types of Outer Joins are RIGHT OUTER Joins, CROSS Joins, and FULL

OUTER Joins.

RIGHT OUTER Joins:

It isn't really that different from a LEFT OUTER JOIN. In fact, it's really

just a LEFT OUTER JOIN with the tables reversed

Cross Joins:

A CROSS JOIN, by contrast, is an intentional Cartesian product. The size of a

Cartesian product is the

number of rows in one table multiplied by those in the other.

SELECT c.CustomerNumber, o.Amount FROM orders o CROSS JOIN customers c

Full Outer Joins :

A FULL OUTER JOIN returns rows from both tables regardless of whether the

join condition succeeds. When

a join column in the first table fails to find a match in the second, the values

from the second table are

returned as NULL, just as they are with a LEFT OUTER JOIN. When the join column

in the second table fails

to find a matching value in the first table, columns in the first table are

returned as NULL, as they are in a

RIGHT OUTER JOIN.

SELECT c.CustomerNumber, o.Amount FROM customers c FULL OUTER JOIN orders o ON
(c.CustomerNumber=o.CustomerNumber)

Conclusion

Hope somebody does really find this article helpful . And do comment on this!