explain different types of jions with examples briefly?

Question Posted / guest

1 Answers
5687 Views
Zensar, I also Faced
E-Mail Answers

explain different types of jions with examples briefly?..

Answer / akash

Inner join
All of the joins that you have seen so far have used the
natural join syntax—for example, to produce a list of
customers and dates on which they placed orders. Remember
that if this syntax is available, it will automatically
pick the join attributes as those with the same name in
both tables (intersection of the schemes). It will also
produce only one copy of those attributes in the result
table.

SELECT cFirstName, cLastName, orderDate
FROM customers NATURAL JOIN orders;• The join does
not consider the pk and fk attributes you have specified.
If there are any non-pk/fk attributes that have the same
names in the tables to be joined, they will also be
included in the intersection of the schemes, and used as
join attributes in the natural join. The results will
certainly not be correct! This problem might be especially
difficult to detect in cases where many natural joins are
performed in the same query. Fortunately, you can always
specify the join attributes yourself, as we describe next.

• Another keyword that produces the same results (without
the potential attribute name problem) is the inner join.
With this syntax, you may specify the join attributes in a
USING clause. (Multiple join attributes in the USING clause
are separated by commas.) This also produces only one copy
of the join attributes in the result table. Like the
NATURAL JOIN syntax, the USING clause is not supported by
all systems.

SELECT cFirstName, cLastName, orderDate
FROM customers INNER JOIN orders
USING (custID);• The most widely-used (and most
portable) syntax for the inner join substitutes an ON
clause for the USING clause. This requires you to
explicitly specify not only the join attribute names, but
the join condition (normally equality). It also requires
you to preface (qualify) the join attribute names with
their table name, since both columns will be included in
the result table. This is the only syntax that will let you
use join attributes that have different names in the tables
to be joined. Unfortunately, it also allows you to join
tables on attributes other than the pk/fk pairs, which was
a pre-1992 way to answer queries that can be written in
better ways today.

SELECT cFirstName, cLastName, orderDate
FROM customers INNER JOIN orders
ON customers.custID = orders.custID;• You can
save a bit of typing by specifying an alias for each table
name (such as c and o in this example), then using the
alias instead of the full name when you refer to the
attributes. This is the only syntax that will let you join
a table to itself, as we will see when we discuss recursive
relationships.

SELECT cFirstName, cLastName, orderDate
FROM customers c INNER JOIN orders o
ON c.custID = o.custID;• All of the join
statements above are specified as part of the 1992 SQL
standard, which was not widely supported for several years
after that. In earlier systems, joins were done with the
1986 standard SQL syntax. Although you shouldn’t use this
unless you absolutely have to, you just might get stuck
working on an older database. If so, you should recognize
that the join condition is placed confusingly in the WHERE
clause, along with all of the tests to pick the right rows:

SELECT cFirstName, cLastName, orderDate
FROM customers c, orders o
WHERE c.custID = o.custID;Outer join
One important effect of all natural and inner joins is that
any unmatched PK value simply drops out of the result. In
our example, this means that any customer who didn’t place
an order isn’t shown. Suppose that we want a list of all
customers, along with order date(s) for those who did place
orders. To include the customers who did not place orders,
we will use an outer join, which may take either the USING
or the ON clause syntax.

SELECT cFirstName, cLastName, orderDate
FROM customers c LEFT OUTER JOIN orders o
ON c.custID = o.custID;All customers and order
dates cfirstname clastname orderdate
Tom Jewett
Alvaro Monge 2003-07-14
Alvaro Monge 2003-07-18
Alvaro Monge 2003-07-20
Wayne Dick 2003-07-14

• Notice that for customers who placed no orders, any
attributes from the Orders table are simply filled with
NULL values.

• The word “left” refers to the order of the tables in the
FROM clause (customers on the left, orders on the right).
The left table here is the one that might have unmatched
join attributes—the one from which we want all rows. We
could have gotten exactly the same results if the table
names and outer join direction were reversed:

SELECT cFirstName, cLastName, orderDate
FROM orders o RIGHT OUTER JOIN customers c
ON o.custID = c.custID;• An outer join makes
sense only if one side of the relationship has a minimum
cardinality of zero (as Orders does in this example).
Otherwise, the outer join will produce exactly the same
result as an inner join (for example, between Orders and
OrderLines).

• The SQL standard also allows a FULL outer join, in which
unmatched join attributes from either side are paired with
null values on the other side. You will probably not have
to use this with most well-designed databases.

Evaluation order
Multiple joins in a query are evaluated left-to-right in
the order that you write them, unless you use parentheses
to force a different evaluation order. (Some database
systems require parentheses in any case.) The schemes of
the joins are also cumulative in the order that they are
evaluated; in RA, this means that

r1 r2 r3 = (r1 r2) r3.

• It is especially important to remember this rule when
outer joins are mixed with other joins in a query. For
example, if you write:

SELECT cFirstName, cLastName, orderDate, UPC,
quantity
FROM customers LEFT OUTER JOIN orders
USING (custID)
NATURAL JOIN orderlines;you will lose the
customers who haven’t placed orders. They will be retained
if you force the second join to be executed first:

SELECT cFirstName, cLastName, orderDate, UPC,
quantity
FROM customers LEFT OUTER JOIN
(orders NATURAL JOIN orderlines)
USING (custID);Other join types
For sake of completeness, you should also know that if you
try to join two tables with no join condition, the result
will be that every row from one side is paired with every
row from the other side. Mathematically, this is a
Cartesian product of the two tables, as you have seen
before. It is almost never what you want. In pre-1992
syntax, it is easy to do this accidently, by forgetting to
put the join condition in the WHERE clause:

SELECT cFirstName, cLastName, orderDate
FROM customers, orders;• If your system is backward-
compatible (most are), you might actually try this just to
prove to yourself that the result is pure nonsense.
However, if you ever have an occasion to really need a
Cartesian product of two tables, use the new cross join
syntax to prove that you really mean it. Notice that this
example still produces nonsense.

SELECT cFirstName, cLastName, orderDate
FROM customers CROSS JOIN orders;• It is possible,
but confusing, to specify a join condition other than
equality of two attributes; this is called a non-equi-join.
If you see such a thing in older code, it probably
represents a WHERE clause or subquery in disguise.

• You may also hear the term self join, which is nothing
but an inner or outer join between two attributes in the
same table. We’ll look at these when we discuss recursive
relationships.

Is This Answer Correct ?

2 Yes

2 No

Post New Answer