Whats a union?

[Randell D.]

Before anyone cracks a quick joke... my question does not refer to
same-sex marriage...

I know how to create a join - correct me if I am wrong, but its
something like the following:

SELECT contacts.firstname,contacts.lastname,address.line_ 1
FROM contacts,address
WHERE contacts.address_hash='$myhashkey'
AND address.hash='$myhashkey';

I have seen mixed references in my books about unions and joins that
make me think they are in some way related. My Core MySQL book, which I
think is great, is unfortunately not clear enough for my head to grasp.

I'd appreciate if someone would could give me a real and an imaginery
world example (thus sample code and perhaps compare apples and oranges
in a shopping list or whatever)...

and... ahem... if I'm wrong about my understanding for a 'join' then
perhaps you could clarify that too for me...

I've got about a years MySQL behind me but very little of it is
commercial/work related so I appreciate all the help/direction you can
give... Please reply to the newsgroup for others to learn from...

Cheers
Randell D.

[J West]

To correct your misconception
Joins will join one table to another and what you have provided is an
example that produces a reult set similar to that of a join but not strictly
a join (There are perfomance and other issues with your SQL syntax.

O.K.
Lesson 1.
Inner and outer joins and know your lefts and rights!

SELECT e.EmployeeName, c.CarModel from Employee e, EmployeeCars c
JOIN c.EmployeeID = e.EmployeeID
WHERE e.EmployeeSalary > 20000

This is an implied inner join that will return all employees regardless of
whether or not they have a company car. that earn over £20,000 a year! The
CarModel field will contain nulls if no car is attached to the employee
table it could also contain nulls if there is an entry for an employee in
the car table but the field has not been set! (Unless NULLS are not allowed
for this field) Confused? You will be!

Replace the word JOIN with LEFT OUTER JOIN and you will only get employees
who have an employee
entry in the car table that earn over 20k! - very useful!

It's late, I'm tired and I don't fancy writing an SQL manual right now so
I'll leave others to explain unions! Also consider what would happen if an
employee had more than one company car and you wanted a list of all
employees who had Mondeos! How would you stop the employee appearing twice!

There are loads of issues with joins and you really should read up on them!

Check out
http://www.theregister.co.uk/2004/05...r_a_new_skill/
and http://www.w3schools.com/sql/default.asp

Regards

James West
"Randell D." <re******************************@fiprojects.moc > wrote in
message news:d7bRd.416494$8l.121589@pd7tw1no...

Before anyone cracks a quick joke... my question does not refer to
same-sex marriage...

I know how to create a join - correct me if I am wrong, but its something
like the following:

SELECT contacts.firstname,contacts.lastname,address.line_ 1
FROM contacts,address
WHERE contacts.address_hash='$myhashkey'
AND address.hash='$myhashkey';

I have seen mixed references in my books about unions and joins that make
me think they are in some way related. My Core MySQL book, which I think
is great, is unfortunately not clear enough for my head to grasp.

I'd appreciate if someone would could give me a real and an imaginery
world example (thus sample code and perhaps compare apples and oranges in
a shopping list or whatever)...

and... ahem... if I'm wrong about my understanding for a 'join' then
perhaps you could clarify that too for me...

I've got about a years MySQL behind me but very little of it is
commercial/work related so I appreciate all the help/direction you can
give... Please reply to the newsgroup for others to learn from...

Cheers
Randell D.

[Randell D.]

J West wrote:

To correct your misconception
Joins will join one table to another and what you have provided is an
example that produces a reult set similar to that of a join but not strictly
a join (There are perfomance and other issues with your SQL syntax.

O.K.
Lesson 1.
Inner and outer joins and know your lefts and rights!

SELECT e.EmployeeName, c.CarModel from Employee e, EmployeeCars c
JOIN c.EmployeeID = e.EmployeeID
WHERE e.EmployeeSalary > 20000

This is an implied inner join that will return all employees regardless of
whether or not they have a company car. that earn over £20,000 a year! The
CarModel field will contain nulls if no car is attached to the employee
table it could also contain nulls if there is an entry for an employee in
the car table but the field has not been set! (Unless NULLS are not allowed
for this field) Confused? You will be! Okay... I can understand the above...

Replace the word JOIN with LEFT OUTER JOIN and you will only get employees
who have an employee
entry in the car table that earn over 20k! - very useful!

It's late, I'm tired and I don't fancy writing an SQL manual right now so
I'll leave others to explain unions! Also consider what would happen if an
employee had more than one company car and you wanted a list of all
employees who had Mondeos! How would you stop the employee appearing twice!
But I'll have to play with my LEFT OUTER JOINs to fully
understand/apprceciate them...

There are loads of issues with joins and you really should read up on them!

Check out
http://www.theregister.co.uk/2004/05...r_a_new_skill/
and http://www.w3schools.com/sql/default.asp

Regards

James West

Thanks for the help and refered links and sleep well ;-)

randelld