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.
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:
- Outer Joins
- 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
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
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
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
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
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)
Hope somebody does really find this article helpful . And do comment on this!