Jump to content

Join or Union?

Recommended Posts

I've been reading so many websites about LEFT JOIN, and now UNION, so before I go off on a major rabbit trail please help me figure out which is best by telling you what I want to accomplish.

I'm going attach two images of my tables:


The first table is called Prospects, and the 2nd table is simply called Deals.

I'm going to try to write what I want in a non working version of code, but I hope it will translate what I want better:

Under Table Deals, the field Status must be equal to "Sold"
Under Tables Deals and Prospects, the ProspectID must match
Under Table Deals, the PIGLead must be equal to 2

I want the table to show the Prospect information (name, address, phone, etc), then under the Prospect information, I want to see the deals sold to them where Status = "Sold".  I want to see the Product and Price.

Best method?

Link to post
Share on other sites

Ok, so I was on the right track.  I wrote this code:

ON Prospects.ProspectID = Deals.ProspectID

So this works.  But how can I narrow it down to just like WHERE PIGLead = 2 ?

I now see why Union does not work, there would not be an equal amount of rows returned (if I'm understanding this correctly).

Link to post
Share on other sites
ON Prospects.ProspectID = Deals.ProspectID AND Deals.PIGLead = 2 
WHERE Deals.Status = 'Sold'

So... the ON line works sort of, but it lists clients that nothing was sold to.  So I tried the WHERE above and now nothing comes up. LOL.

Edited by TapeGun007
Link to post
Share on other sites

The LEFT JOIN should work with the tables as they are, but the conditions on the deal table need to be in the JOIN ON and not in the WHERE.


LEFT JOIN Deals ON Prospects.ProspectID = Deals.ProspectID 
                   AND Deals.PIGLead = 2 
                   AND Deals.Status = 'Won'


Link to post
Share on other sites

Is this a less correct way to write it then?  This gave me the result I was looking for, but being a bit OCD, I like to ensure I'm writing solid code.

INNER JOIN Prospects
ON Prospects.ProspectID = Deals.ProspectID AND Deals.Status = 'Won' AND Deals.PIGLead = ?

Yeah, I just put the * in there to shorten it.

Link to post
Share on other sites

The point I was making is that if you use "... LEFT JOIN Deals" then any conditions on deals selection must go in the JOIN ON clause. If you put them in the WHERE clause, the LEFT JOIN does not function correctly.

Use A LEFT JOIN B when you want to show all selected record values from A and values from B only if a matching record/s exists.

Use A (INNER) JOIN B when you only want to show records where there is a match in both tables.

Only use LEFT JOIN when necessary - they are much slower than INNER JOINS.

Link to post
Share on other sites


Thanks as always.  I did discover through my trials and errors that the conditions in the WHERE clause were causing some issues.  After much reading I had put them in the JOIN ON section, but you confirmed what I wasn't 100% sure of.

So your definition confirms also that I was supposed to use the INNER JOIN because I did only want to show records if the match was in both tables.

I did not know that LEFT JOINS were much slower however, that part I had not yet discovered.

Link to post
Share on other sites
This thread is more than a year old.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.