Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Joins Clarified

By Sanket Naik,

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!









 

Total article views: 15392 | Views in the last 30 days: 16
 
Related Articles
FORUM

using outer query column name in subquery while doing order by

using outer query column name in subquery while doing order by

FORUM

order by to return null values

order by to return null values

FORUM

ORDER BY

Return last 5 Orders

FORUM

Alter column order

Alter column order

FORUM

Need help with outer joins

Outer join / Group By

Tags
miscellaneous    
t-sql    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones