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.firstn ame,contacts.la stname,address. line_1
FROM contacts,addres s
WHERE contacts.addres s_hash='$myhash key'
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]

Re: Whats a union?

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.EmployeeSalar y > 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/04/10_minutes_for_a_new_skill/

and http://www.w3schools.com/sql/default.asp

Regards

James West
"Randell D." <reply.via.news .group.only.tha nks@fiprojects. moc> wrote in
message news:d7bRd.4164 94$8l.121589@pd 7tw1no...[color=blue]
>
> 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.firstn ame,contacts.la stname,address. line_1
> FROM contacts,addres s
> WHERE contacts.addres s_hash='$myhash key'
> 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.[/color]

[Randell D.]

Re: Whats a union?

J West wrote:[color=blue]
> 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.EmployeeSalar y > 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![/color]
Okay... I can understand the above...
[color=blue]
>
> 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![/color]

But I'll have to play with my LEFT OUTER JOINs to fully
understand/apprceciate them...
[color=blue]
>
> 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[/color]

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

randelld