Board index » delphi » Definitions of Join types

Definitions of Join types

Greetings,
Anybody have a link/pointer/description of what
Left Outer Joins, Right Outer Joins, Left Inner Joins,
Right Inner Joins and Natural Joins?  Hasn't become
crystal clear for me yet.

thanks,
Darryl

 

Re:Definitions of Join types


Extracted from SQL books online:

CROSS JOIN Specifies that the cross product of two tables be returned.
This returns the same rows as if no WHERE clause was specified in an
old-style join. This type of join is called a Cartesian join in Oracle.
INNER Specifies that all inner rows be returned. Any unmatched rows are
discarded. This is identical to a standard Oracle table join.
LEFT
[OUTER] Specifies that all of the left table outer rows be returned,
even if no column matches are found. This operates just like an Oracle
outer join (+).
RIGHT
[OUTER] Specifies that all of the right table outer rows be returned,
even if no column matches are found. This operates just like an Oracle
outer join (+).
FULL
[OUTER] Specifies the row be included in the result set and its output
columns that correspond to the other table be set to NULL if a row from
either table does not match the selection criteria. This would be the
same as placing the Oracle outer join operator on both sides of the
equal sign (for example, col1(+) = col2(+)), which is not allowed.

Quote
Darryl wrote:
> Greetings,
> Anybody have a link/pointer/description of what
> Left Outer Joins, Right Outer Joins, Left Inner Joins,
> Right Inner Joins and Natural Joins?  Hasn't become
> crystal clear for me yet.

> thanks,
> Darryl

--
Guillermo Casta?o Acevedo
Gerente de Sistemas - Grupo Millennium Ltda
Guiller...@GrupoMillennium.com
http://www.GrupoMillennium.com

Re:Definitions of Join types


Quote
"Darryl" <dar...@osborne-ind.com> wrote in message news:3cfd2676_1@dnews...
> Greetings,
> Anybody have a link/pointer/description of what
> Left Outer Joins, Right Outer Joins, Left Inner Joins,
> Right Inner Joins and Natural Joins?  Hasn't become
> crystal clear for me yet.

There's really just 4 possible join types. The secret is to remember that
the keywords INNER and OUTER are actually redundant and can be omitted.

[inner] join
left [outer] join
right [outer] join
full [outer] join

So all of left, right, and full are outer joins. There is only one inner
join and thus if you see a query that only has the word "join" you know it
is an inner join. You will never see left, right, or full combined with
inner.

An inner join will only select rows where there is a match between the two
named tables.

Left and Right refer to the relative positions of the tables named in the
query, e.g:

... from customers c left join order o on o.custno = c.custno

Customers is the left table and orders is the right table. Left means to
select all records from the left table whether or not there is a match in
the right table. Right means the other way around of course, and Full means
select all from both tables regardless of matches. So in the above example
all customers will be selected (subject to any Where clause) whether orders
exist or not and orders will be returned as part of each row where they do
exist.

--
Wayne Niddery (Logic Fundamentals, Inc.)
RADBooks: http://www.logicfundamentals.com/RADBooks/delphibooks.html
Those who disdain wealth as a worthy goal for an individual or a society
seem not to realize that wealth is the only thing that can prevent
poverty. - Thomas Sowell

Re:Definitions of Join types


Darryl,

here is a nice little example:

CREATE TABLE English_Numbers(
num    int,
descr  varchar(20)
PRIMARY KEY(num))
go

CREATE TABLE Spanish_Numbers(
num    int,
descr  varchar(20)
PRIMARY KEY(num))
go

INSERT INTO English_Numbers VALUES(1, 'One')
INSERT INTO English_Numbers VALUES(2, 'Two')
INSERT INTO English_Numbers VALUES(3, 'Three')
go
INSERT INTO Spanish_Numbers VALUES(2, 'Dos')
INSERT INTO Spanish_Numbers VALUES(3, 'Tres')
INSERT INTO Spanish_Numbers VALUES(4, 'Cuatro')
go

--LEFT JOIN
SELECT
 E.descr,
 COALESCE(E.num, S.num) AS num,
 S.descr
FROM English_Numbers E
LEFT JOIN Spanish_Numbers S
  ON S.num = E.num
go

--RIGHT JOIN
SELECT
 E.descr,
 COALESCE(E.num, S.num) AS num,
 S.descr
FROM English_Numbers E
RIGHT JOIN Spanish_Numbers S
  ON S.num = E.num
go

--INNER JOIN
SELECT
 E.descr,
 COALESCE(E.num, S.num) AS num,
 S.descr
FROM English_Numbers E
INNER JOIN Spanish_Numbers S
  ON S.num = E.num
go

--OUTER JOIN (FULL OUTER JOIN)
SELECT
 E.descr,
 COALESCE(E.num, S.num) AS num,
 S.descr
FROM English_Numbers E
FULL OUTER JOIN Spanish_Numbers S
  ON S.num = E.num
goCREATE TABLE English_Numbers(
num    int,
descr  varchar(20)
PRIMARY KEY(num))
go

CREATE TABLE Spanish_Numbers(
num    int,
descr  varchar(20)
PRIMARY KEY(num))
go

INSERT INTO English_Numbers VALUES(1, 'One')
INSERT INTO English_Numbers VALUES(2, 'Two')
INSERT INTO English_Numbers VALUES(3, 'Three')
go
INSERT INTO Spanish_Numbers VALUES(2, 'Dos')
INSERT INTO Spanish_Numbers VALUES(3, 'Tres')
INSERT INTO Spanish_Numbers VALUES(4, 'Cuatro')
go

--LEFT JOIN
SELECT
 E.descr,
 COALESCE(E.num, S.num) AS num,
 S.descr
FROM English_Numbers E
LEFT JOIN Spanish_Numbers S
  ON S.num = E.num
go

--RIGHT JOIN
SELECT
 E.descr,
 COALESCE(E.num, S.num) AS num,
 S.descr
FROM English_Numbers E
RIGHT JOIN Spanish_Numbers S
  ON S.num = E.num
go

--INNER JOIN
SELECT
 E.descr,
 COALESCE(E.num, S.num) AS num,
 S.descr
FROM English_Numbers E
INNER JOIN Spanish_Numbers S
  ON S.num = E.num
go

--OUTER JOIN (FULL JOIN, FULL OUTER JOIN)
SELECT
 E.descr,
 COALESCE(E.num, S.num) AS num,
 S.descr
FROM English_Numbers E
FULL OUTER JOIN Spanish_Numbers S
  ON S.num = E.num
go

HTH,
--
Jim

Re:Definitions of Join types


Darryl,

here is a nice little example -- run each query and look at the result sets:

CREATE TABLE Spanish_Numbers(
num    int,
descr  varchar(20)
PRIMARY KEY(num))
go

INSERT INTO English_Numbers VALUES(1, 'One')
INSERT INTO English_Numbers VALUES(2, 'Two')
INSERT INTO English_Numbers VALUES(3, 'Three')
go
INSERT INTO Spanish_Numbers VALUES(2, 'Dos')
INSERT INTO Spanish_Numbers VALUES(3, 'Tres')
INSERT INTO Spanish_Numbers VALUES(4, 'Cuatro')
go

--LEFT JOIN
SELECT
 E.descr,
 COALESCE(E.num, S.num) AS num,
 S.descr
FROM English_Numbers E
LEFT JOIN Spanish_Numbers S
  ON S.num = E.num
go

--RIGHT JOIN
SELECT
 E.descr,
 COALESCE(E.num, S.num) AS num,
 S.descr
FROM English_Numbers E
RIGHT JOIN Spanish_Numbers S
  ON S.num = E.num
go

--INNER JOIN
SELECT
 E.descr,
 COALESCE(E.num, S.num) AS num,
 S.descr
FROM English_Numbers E
INNER JOIN Spanish_Numbers S
  ON S.num = E.num
go

--OUTER JOIN (FULL JOIN, FULL OUTER JOIN)
SELECT
 E.descr,
 COALESCE(E.num, S.num) AS num,
 S.descr
FROM English_Numbers E
FULL OUTER JOIN Spanish_Numbers S
  ON S.num = E.num
go

HTH,
--
Jim

Re:Definitions of Join types


Forgot:

CREATE TABLE English_Numbers(
num    int,
descr  varchar(20)
PRIMARY KEY(num))
go

Other Threads