SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Vivek Johari / SQL Joins / Latest Posts

RE: SQL Joins

ta.bu.shi.da.yu — Mon, 17 Aug 2009 08:13:34 GMT

Yow! But why would you want to?

RE: SQL Joins

GilaMonster — Mon, 17 Aug 2009 07:40:15 GMT

[quote][b]ta.bu.shi.da.yu (8/16/2009)[/b][hr]You can't do an outer join from the where clause...[/quote]You can't in a DB with compatibility set 90 or higher (ie SQL 2005 or 2008). It was possible on SQL 2000, but not recommended.

RE: SQL Joins

ta.bu.shi.da.yu — Sun, 16 Aug 2009 16:31:04 GMT

You can't do an outer join from the where clause...

RE: SQL Joins

arthur.locke — Sun, 16 Aug 2009 16:26:28 GMT

I would like to see the article expanded to explain the difference between specifying joins in the FROM and WHERE clauses. In my experience, failure to understand the difference when using outer joins causes more problems than understanding the basic join types.

RE: SQL Joins

c_atulbajaj — Sat, 15 Aug 2009 11:45:26 GMT

This article has given me a gud understanding of Joins in SQL.The main thing about this article is that is its simplicity with which it describes Joins.Thanx a lot.

RE: SQL Joins

SQL Master-1143748 — Fri, 14 Aug 2009 23:03:40 GMT

Hey Vivek, that's a really good article nicely written, expecting a few more tutorials/articles from you, keep up the good work:-)

RE: SQL Joins

tmeyer1969 — Fri, 14 Aug 2009 17:41:19 GMT

nice article, but I'm puzzled by the statement that you "feel that people are afraid of Joins in SQL Server".I mean, joins (inner/equi, at least) are one of the most basic things that someone has to know about T-SQL, learned in the same breath as 'select','update','delete','where', etc ...Now if you said that outer, self and cross-apply joins are what people are afraid (or more likely unaware) of, I'd buy it. Otherwise, I'd say that the person didn't know jack about SQL if they don't know what a simple innner join is.

RE: SQL Joins

mishaluba — Fri, 14 Aug 2009 17:12:39 GMT

Nice refresher! Thank you.

RE: SQL Joins

Thordog — Fri, 14 Aug 2009 12:49:47 GMT

I thought it was full of hot air... :cool:

RE: SQL Joins

kinglion1109 — Fri, 14 Aug 2009 12:45:24 GMT

Very good article.

RE: SQL Joins

ankur_johari — Fri, 14 Aug 2009 12:38:09 GMT

This article contains all the basic knowledge of joins,with the relevant and proper examples and explanations....

RE: SQL Joins

rtodd-997233 — Fri, 14 Aug 2009 08:48:29 GMT

I agree with you. A self join is not a 'type' of join because it only means that your two tables in your join are the same. It is no different from any other join. What if you join a table to a VIEW of itself? Is THAT a self join? Not even close, because the view might a have a where clause that eliminates some records....The biggest indicator that SELF join is not a type of join, is that you cannot use the word 'SELF' in the syntax of your SQL....all the other types of joins are valid SQL statements

RE: SQL Joins

Thordog — Fri, 14 Aug 2009 08:19:30 GMT

I'd like to work for a company where I am my boss's boss. :cool: (self join exampl)

RE: SQL Joins

ta.bu.shi.da.yu — Fri, 14 Aug 2009 04:47:30 GMT

Very interesting - I've never thought of it that way before. But absolutely, that makes total sense.You'll have to forgive the newbie questions, but I am the eternal learner :)

RE: SQL Joins

MVDBA — Fri, 14 Aug 2009 04:16:30 GMT

nopethe use of left or right determines which side of the join returns all resultsmy example is clearly a left outer joinif this were right outer then the results would be differenti would be asking for "show me all employeesand the name of the person they [b]manage[/b] - include all people that do no manage anyone".egselect e1.*,e2.name from employee e1 left outer join employee e2 on e1.managerid=e2.employeeidassume "maggie os" has no manager and "paul smith" manages no-onereturns (example data)Name id active managerjohn smith 1 1 fred jonesabe lincoln 2 1 fred jonesmaggie os 3 1 NULLpaul smith 4 1 fred jones.......select e1.*,e2.name from employee e1 RIGHT outer join employee e2 on e1.managerid=e2.employeeidreturns (example data)Name id active managerjohn smith 1 1 fred jonesabe lincoln 2 1 fred jonesNULL NULL NULL Paul SmithPaul Smith 4 1 fred jones

RE: SQL Joins

GilaMonster — Fri, 14 Aug 2009 04:14:50 GMT

[quote][b]ta.bu.shi.da.yu (8/14/2009)[/b][hr]But I think that calling it a left outer join is a bit of a misnomer. Could be a right outer join to, right?[/quote]If it was specified as FROM Table1 t1 RIGHT OUTER JOIN Table1 t2 ON .... yes. Self-join is not a technical term. All it indicates is that a table is joined to itself. The term 'self join' doesn't define what the join type is.

RE: SQL Joins

ta.bu.shi.da.yu — Fri, 14 Aug 2009 04:04:11 GMT

[quote][b]GilaMonster (8/14/2009)[/b][hr][quote][b]ta.bu.shi.da.yu (8/14/2009)[/b][hr][quote]One point I would like to make though, is that it's definitely a special type of join where the left and right tables are the same table. Therefore there is no point calling it a left or right outer join. It is what it says it is - it's a self join. To be precise, a self inner join I guess, but nobody calls it that :)[/quote]There's nothing at all special about a 'self' join. It's a standard inner or outer join that just happens to have the same table on both sides. Nothing more.[/quote]Ah, OK. But I think that calling it a left outer join is a bit of a misnomer. Could be a right outer join to, right?

RE: SQL Joins

ta.bu.shi.da.yu — Fri, 14 Aug 2009 04:02:07 GMT

I stand corrected :-)

RE: SQL Joins

MVDBA — Fri, 14 Aug 2009 03:57:10 GMT

left outer self join - easyshow me all employees and their managers[b]include all employees that do not have a manager[/b]select e1.*,e2.name from employee e1 left outer join employee e2 on e1.managerid=e2.employeeid

RE: SQL Joins

GilaMonster — Fri, 14 Aug 2009 03:57:07 GMT

[quote][b]ta.bu.shi.da.yu (8/14/2009)[/b][hr]Why would you ever want to do a left outer self join? [/quote][code]CREATE TABLE #SomeTable (Col1 int)Insert into #SomeTable Values (1)Insert into #SomeTable Values (2)Insert into #SomeTable Values (3)Insert into #SomeTable Values (4)GOSELECT t1.Col1, t2.Col1 FROM#SomeTable t1 LEFT OUTER JOIN #SomeTable t2 ON t1.Col1 = t2.Col1-1[/code][quote]A left outer self join means that you want to find every match and you want to find all rows that don't match. Sounds like a cross join to me. [/quote]It's not a cross join. Cross join returns all possible combinations. Left outer join means return all rows in the left table, where the join condition matches rows from the right table return them, where they do not make those columns null.With the table above, a cross join to itself would return 16 rows. The left outer join to itself will return 4.[quote]One point I would like to make though, is that it's definitely a special type of join where the left and right tables are the same table. Therefore there is no point calling it a left or right outer join. It is what it says it is - it's a self join. To be precise, a self inner join I guess, but nobody calls it that :)[/quote]There's nothing at all special about a 'self' join. It's a standard inner or outer join that just happens to have the same table on both sides. Nothing more.

RE: SQL Joins

ta.bu.shi.da.yu — Fri, 14 Aug 2009 03:51:48 GMT

[quote][b]michael vessey (8/14/2009)[/b][hr]I have one question -I posed a question on our technical tests for new DBA/Developers and I asked"Name All Types of join you are aware of"The question was deliberatly loosely worded as I was looking to see how deep the person's understanding of joins wasI was looking for Logical = Inner, Left/Right/Full outer , Cross ApplyPhysical = Hash, Merge, Loopthis would demonstrate they understood not only the function of the join, but how the SQL engine behaves and physically performs these joins.Now when we recruit in india I always get SELF JOIN added to the answers (but never in the UK for some reason)- I immediatly scratch this answer off as incorrect - but should i???In the example SELF join actually used the INNER JOIN syntax - it does not matter which tables you join together it is still an INNER JOIN...how about if the "self join" used a left OUTER??? is it still a self join? nope - it's a left outer (In my opinion)Any thoughts??? In My opinion "SELF JOIN" is a nickname , but it does not functionally describe the join itself (ie you don't know if it's left , right, inner or outer"Mike Vessey[/quote]Why would you ever want to do a left outer self join? A left outer self join means that you want to find every match and you want to find all rows that don't match. Sounds like a cross join to me. Others might see the flaw in my reasoning... is there something I'm missing?One point I would like to make though, is that it's definitely a special type of join where the left and right tables are the same table. Therefore there is no point calling it a left or right outer join. It is what it says it is - it's a self join. To be precise, a self inner join I guess, but nobody calls it that :)

RE: SQL Joins

GilaMonster — Fri, 14 Aug 2009 03:48:15 GMT

[quote][b]cs (8/14/2009)[/b][hr]If someone could explain how cross apply works, I'd be a very happy man :-)[/quote]I'll give it a try.[code]CREATE TABLE #SomeTable (Col1 int)Insert into #SomeTable Values (1)Insert into #SomeTable Values (2)Insert into #SomeTable Values (3)Insert into #SomeTable Values (4)GOCREATE Function dbo.SomeFunction (@Input int)RETURNS TABLE ASRETURN (SELECT @Input-1 AS Result union all SELECT @Input AS Result)GOSELECT Col1, func.resultFROM #SomeTable st CROSS APPLY dbo.someFunction(st.Col1) AS func [/code]For each row in SomeTable the CROSS APPLY will run the table-valued function dbo.SomeTable and pass it the value of Col1 from that row of SomeTable. Based on the table and function created above, the results would be1,01,12,12,23,23,34,34,4Make sense so far?Cross apply is like inner join, if the function doesn't return a row for a particular parameter, that parameter won't appear in the resultset. If you want outer join behaviour, use OUTER APPLYCross apply with a subquery is pretty much the same as with a function, it's the subquery that's run once for each row. The equivalent of that function above with a subquery would be this:[code]SELECT Col1, func.resultFROM #SomeTable st CROSS APPLY (SELECT st.Col1 UNION ALL SELECT st.Col1-1 ) AS func[/code]

RE: SQL Joins

MVDBA — Fri, 14 Aug 2009 02:38:47 GMT

I have one question -I posed a question on our technical tests for new DBA/Developers and I asked"Name All Types of join you are aware of"The question was deliberatly loosely worded as I was looking to see how deep the person's understanding of joins wasI was looking for Logical = Inner, Left/Right/Full outer , Cross ApplyPhysical = Hash, Merge, Loopthis would demonstrate they understood not only the function of the join, but how the SQL engine behaves and physically performs these joins.Now when we recruit in india I always get SELF JOIN added to the answers (but never in the UK for some reason)- I immediatly scratch this answer off as incorrect - but should i???In the example SELF join actually used the INNER JOIN syntax - it does not matter which tables you join together it is still an INNER JOIN...how about if the "self join" used a left OUTER??? is it still a self join? nope - it's a left outer (In my opinion)Any thoughts??? In My opinion "SELF JOIN" is a nickname , but it does not functionally describe the join itself (ie you don't know if it's left , right, inner or outer"Mike Vessey

RE: SQL Joins

ta.bu.shi.da.yu — Fri, 14 Aug 2009 02:15:06 GMT

That's odd... this logged me in under a different account. Ah well.

RE: SQL Joins

cs-892458 — Fri, 14 Aug 2009 02:13:27 GMT

This is a very good article. To be complete it only needs to explain semi-joins, anti-semi-joins and cross apply. If someone could explain how cross apply works, I'd be a very happy man :-)

SQL Joins

Vivek Johari — Tue, 11 Aug 2009 16:56:12 GMT

Comments posted to this topic are about the item [B]SQL Joins[/B]