Help me understand ON vs WHERE clause in SQL join queries please

I thought an ON was the same as a WHERE but I'm super confused by this query. I was expecting something like this:What I expected to happen whether I use ON or WHERE to produce the same results when I used a where clause.

but what happens is this and I'm super confused by it:I'm clearly missing the point of the ON clause or something
I am trying to determine the name of all employees who were hired after Davies.  But the question I have has NOTHING to do with the query. I need help understanding what the ON is doing to this query because I am having trouble figuring out what is happening.

Isn't the ON just an alternative to using a WHERE clause? What is the ON clause in this particular query returning me exactly? I'm so confused. Basically, in my mind I figured the on (b.last_name = 'Davies') was going to give me back all matches for people who had a last name of Davies but that isn't the case. Thanks
Mark_CoAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sdstuberCommented:
ON vs WHERE is not important there, they are equivalent conditions in your example

The important thing is in the first query you access the table once.
In the second query you access the query twice and are effectively multiplying the results.

Every row of A  for each row of B, your filter restricts B, but you're still pulling all of A
0
sdstuberCommented:
In an inner join (as you have above)  ON and WHERE are the functionally the same

In an outer join,  ON applies first as a condition of the join
WHERE applies afterward to the results of the join


In an inner join, you could say the same thing, but functionally it makes no difference and internally, the optimizers of each database may combine the operations in whichever order is determined to be most efficient.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mccarlIT Business Systems Analyst / Software DeveloperCommented:
I agree with what sdstuber has said, my contribution here is to help you visualise what is going on. So try this...

Reduce the number of rows in your employee table (or create a new table) so that you are working with maybe 4 rows in total, otherwise the point gets lost. And then, working from that second query, add the b.last_name and b.hire_date to your select columns and run that query. Now play around with the condition in the ON clause to see the effects, ie. try removing it totally, try setting it to (a.last_name = b.last_name) and also try setting it to (a.hire_date > b.hire_date). Hopefully, those experiments will help you understand exactly what is going on (and that is: that every row of A gets joined with every row of B and then ON or WHERE clauses get applied)

Also, just as an aside, to complete your goal, ie. all employees hired after Davies, just take your second query "as it is" and add " WHERE (a.hire_date > b.hire_date)" to the end.
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Mark_CoAuthor Commented:
Thanks
0
PortletPaulfreelancerCommented:
>>Isn't the ON just an alternative to using a WHERE clause?
absolutely not I'm afraid


>>What is the ON clause in this particular query returning me exactly?
you have accidentally caused a "weird one" :)

you have "attached" the data associated with last_name="Davies" to every record in the employees table. This is best illustrated by an example. Below I created a tiny employees table, (Smith/Jones/Ng/Davies) i.e. just one of the records has the last_name 'Davies', but when I display all the columns created by that query this is what happens:
    | LAST_NAME |         HIRE_DATE | B_NAME |       B_HIRE_DATE |
    |-----------|-------------------|--------|-------------------|
    |     Smith | January, 01 1997  | Davies | January, 04 1995  |
    |      Joes | January, 02 1997  | Davies | January, 04 1995  |
    |        Ng | January, 03 1997  | Davies | January, 04 1995  |
    |    Davies | January, 04 1995  | Davies | January, 04 1995  |

Open in new window

If you display some of the fields from the joined employees table aliases as 'b' you will see this for yourself

PLEASE TRY THIS QUERY at your end
select
  a.last_name
, a.hire_date
, b.last_name as b_name
, b.hire_date as b_hire_date
from employees a
join employees b       on b.last_name = 'Davies'
where a.last_name <> 'Davies'
;

Open in new window

The results of this simply aren't very useful I suspect, but notice that it uses both ON & WHERE.

----------------------------------
ON         is for "joining" tables together (or "attaching" together if that terms helps)

WHERE   is for choosing which records are relevant to you

It is very common for queries to need both ON & WHERE
----------------------------------


Full details of how I got my result:
    
    
    CREATE TABLE EMPLOYEES
    	("LAST_NAME" varchar2(6), "HIRE_DATE" timestamp)
    ;
    
    INSERT ALL 
    	INTO EMPLOYEES ("LAST_NAME", "HIRE_DATE")
    		 VALUES ('Smith', '01-Jan-1997 12:00:00 AM')
    	INTO EMPLOYEES ("LAST_NAME", "HIRE_DATE")
    		 VALUES ('Joes', '02-Jan-1997 12:00:00 AM')
    	INTO EMPLOYEES ("LAST_NAME", "HIRE_DATE")
    		 VALUES ('Ng', '03-Jan-1997 12:00:00 AM')
    	INTO EMPLOYEES ("LAST_NAME", "HIRE_DATE")
    		 VALUES ('Davies', '04-Jan-1995 12:00:00 AM')
    SELECT * FROM dual
    ;

**Query 1**:

    select
    *
    from employees
    where last_name = 'Davies'
    

**[Results][2]**:
    
    | LAST_NAME |                      HIRE_DATE |
    |-----------|--------------------------------|
    |    Davies | January, 04 1995 00:00:00+0000 |


**Query 2**:

    select
      a.*
    , b.last_name as b_name
    , b.hire_date as b_hire_date
    from employees a
    join employees b     on b.last_name = 'Davies'
    

**[Results][3]**:
    
    | LAST_NAME |                      HIRE_DATE | B_NAME |                    B_HIRE_DATE |
    |-----------|--------------------------------|--------|--------------------------------|
    |     Smith | January, 01 1997 00:00:00+0000 | Davies | January, 04 1995 00:00:00+0000 |
    |      Joes | January, 02 1997 00:00:00+0000 | Davies | January, 04 1995 00:00:00+0000 |
    |        Ng | January, 03 1997 00:00:00+0000 | Davies | January, 04 1995 00:00:00+0000 |
    |    Davies | January, 04 1995 00:00:00+0000 | Davies | January, 04 1995 00:00:00+0000 |



  [1]: http://sqlfiddle.com/#!4/c46c6/4

Open in new window

0
PortletPaulfreelancerCommented:
mmmmm, too much time spent on preparation, oh well.

do please try the query I suggested above - it will help you to see the results I think.

Cheers, Paul.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.