join - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

join

Author

Topic

khurram7x
Starting Member

5 Posts

Posted - 2014-11-14 : 00:35:26

Apology in advance for a long question, but doing this just for the sake of learning:
i'm new to SQL and researching on JOIN for now. I'm getting two different behaviors when using INNER and OUTER JOIN. What I know is, INNER JOIN gives an intersection kind of result while returning only common rows among tables, and (LEFT/RIGHT) OUTER JOIN is outputting what is common and remaining rows in LEFT or RIGHT tables, depending upon LEFT/RIGHT clause respectively.

While working with MS Training Kit and trying to solve this practice: "Practice 2: In this practice, you identify rows that appear in one table but have no matches in another. You are given a task to return the IDs of employees from the HR.Employees table who did not handle orders (in the Sales.Orders table) on February 12, 2008. Write three different solutions using the following: joins, subqueries, and set
operators. To verify the validity of your solution, you are supposed to return employeeIDs: 1, 2, 3, 5, 7, and 9."

I'm successful doing this with subqueries and set operators but with JOIN is returning something not expected. I've written the following query:

USE TSQL2012;
SELECT
E.empid
FROM
HR.Employees AS H
JOIN Sales.Orders AS O
ON H.empid = O.empid
AND O.orderdate = '20080212'
JOIN HR.Employees AS E
ON E.empid <> H.empid

ORDER BY
E.empid
;

I'm expecting results as: 1, 2, 3, 5, 7, and 9 (6 rows)
But what i'm getting is: 1,1,1,2,2,2,3,3,3,4,4,5,5,5,6,6,7,7,7,8,8,9,9,9 (24 rows)

I tried some videos but could not understand this side of INNER/OUTER JOIN. I'll be grateful if someone could help this side of JOIN, why is it so and what should I try to understand while working with JOIN.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-11-14 : 01:18:27

quote:
return the IDs of employees from the HR.Employees table who did not handle orders (in the Sales.Orders table)

you can use NOT EXISTS


SELECT *
FROM   tablea as a
WHERE  NOT EXISTS
       (
           SELECT *
           FROM   tableb as b
           WEHRE  b.some_col = a.some_col
       )

KH
[spoiler]Time is always against us[/spoiler]

khurram7x
Starting Member

5 Posts

Posted - 2014-11-14 : 01:42:15

Thanks for reply. Yes, i can and it worked well. But you missed my point, I'm trying to learn JOIN's and accordingly need to solve this query with JOIN.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-11-14 : 09:23:01

how bout this:


SELECT h.empid
  FROM hr.Employees AS H
       LEFT JOIN sales.Orders o
           ON h.empid = o.empid AND o.orderdate = '20080212'
  WHERE o.orderdate IS NULL;

Uses the fact that when there are no matches on a left join, nulls are returned

khurram7x
Starting Member

5 Posts

Posted - 2014-11-14 : 13:39:37

Ya, solved my problem. Many thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources