Board index » delphi » Inner join and left join

Inner join and left join

What are the differences between these two joins ?
 

Re:Inner join and left join


Quote
"Alan" <alanpl...@yahoo.com.au> wrote in message

news:3daf69e6$1@newsgroups.borland.com...

Quote
> What are the differences between these two joins ?

Standard join syntax looks like this:

... FROM TableA JOIN TableB ON TableA.ColC = TableB.ColD ...

A join is an inner join by default, and it includes every row pair that
matches the join condition. The join result includes all the columns from
TableA and all the columns from TableB, and it has every combination of a
row from TableA with a row from TableB that matches the join condition.

An outer join includes all the rows that would be in an inner join, plus the
remaining rows from TableA and/or TableB even though they don't meet the
join condition. A left outer join, called a left join for short, includes
all the rows from the table on the LEFT side of the JOIN keyword, TableA in
our example. If a row from TableA cannot meet the join condition (because no
row in TableB has a ColD value that matches the TableA row's value in ColC),
then the left join still includes a row with TableA's values, and the
columns that would be provided by TableB in that row of the join result are
all NULL.

A RIGHT (outer) join would include all rows from TableB, with all their
matches from TableA.

A FULL outer join would include all row combinations that meet the join
condition (same as an inner join) plus all non-matching rows from TableA
(with NULLs filling the row) and all non-matching rows from TableB (with
NULLs filling the row).

  Richard

Re:Inner join and left join


Hi Alan,

Quote
> What are the differences between these two joins ?

This should cover the most common types.   Run each query and look at the
result sets:

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 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

--
Jim

Other Threads